VB在Oracle數據庫中存取文件

oracle中可用LOB 字段來(lái)存儲長(cháng)文本或者二進(jìn)制數據，LOB分為BLOB合CLOB，其中BLOB可用來(lái)存儲二進(jìn)制數據，如exe、dll、視頻、音頻等；CLOB只能用來(lái)存儲字符型文本數據。
舉個(gè)VB存取BLOB字段的簡(jiǎn)單例子，首先在oracle中建一有BLOB字段的表
SQL:
create table TEST_TABLE(id number(4) not null,fileName varchar2(255),fileContent BLOB)

在VB中寫(xiě)代碼:
Dim cnn As New ADODB.Connection     '全局數據
Dim rs1 As New ADODB.Recordset      '全局數據
Dim SQL As String                   '全局數據

Private Sub Command1_Click()
   SaveFile ("D:\7.JPG")
End Sub

Private Sub Command2_Click()
    GetFile
End Sub

Private Sub Form_Load()
    cnn.ConnectionString = "Provider=OraOledb.Oracle.1;" _
                        & "Password=mygps;" _
                        & "User ID=usermygps;" _
                        & "Data Source=mygps@mygps;" _
                        & "Persist Security Info=True"
    cnn.Open       '打開(kāi)數據庫連接
End Sub

'－－－－－－－－－－－－－插入一條記錄，存儲文件數據－－－－－－－－－－－－－－－－－
Private Sub SaveFile(strFileName As String)
        rs1.LockType = adLockOptimistic
        rs1.CursorLocation = adUseClient
        rs1.Open "TEST_TABLE", cnn, adCmdTable
        rs1.AddNew
        rs1.Fields("id").Value = 1
        rs1.Fields("fileName").Value = strFileName
        Call FileToBlob(rs1.Fields("fileContent"), strFileName)
        rs1.Update
       rs1.Close
       Set rs1 = Nothing
End Sub

'－－－－－－－－－－－－－讀取一條記錄，獲取文件數據，并寫(xiě)入文件－－－－－－－－－－－
Private Sub GetFile()
    strSql = "select * from TEST_TABLE where id='2'"
    Debug.Print strSql
    rs1.LockType = adLockOptimistic
    rs1.CursorLocation = adUseClient
    rs1.Open strSql, cnn, adOpenKeyset
    If rs1.RecordCount > 0 Then
       BlobToFile rs1.Fields("fileContent"), rs1.Fields("fileName").Value, rs1.Fields("fileContent").ActualSize
    End If
    rs1.Close
    Set rs1 = Nothing
End Sub

'－－－－－－－－－－－－－文件數據轉BLOB－－－－－－－－－－－－－－－－－－－－－－
Private Sub FileToBlob(fld As ADODB.field, FileName As String, Optional ChunkSize As Long = 8192)
    Dim fnum As Integer, bytesLeft As Long, bytes As Long
    Dim tmp() As Byte
   
    If (fld.Attributes And adFldLong) = 0 Then
        Err.Raise 1001, , "Field doesn't support the GetChunk method."
    End If
   
    fnum = FreeFile
    Open FileName For Binary As fnum
   
    bytesLeft = LOF(fnum)
    Do While bytesLeft
        bytes = bytesLeft
        If bytes > ChunkSize Then bytes = ChunkSize
       
        ReDim tmp(1 To bytes) As Byte
        Get #1, , tmp
        fld.AppendChunk tmp
        bytesLeft = bytesLeft - bytes
    Loop
    Close #fnum
End Sub

'－－－－－－－－－－－－－BLOB數據轉存文件－－－－－－－－－－－－－－－－－－－－－
Private Sub BlobToFile(fld As ADODB.field, FileName As String, Optional ChunkSize As Long)
    Dim fnum As Integer, bytesLeft As Long, bytes As Long
    Dim tmp() As Byte
   
    If (fld.Attributes And adFldLong) = 0 Then
        Err.Raise 1001, , "Field doesn't support the GetChunk method."
    End If
   
    If Dir$(FileName) <> "" Then Kill FileName
   
    fnum = FreeFile
    Open FileName For Binary As fnum
    bytesLeft = fld.ActualSize
    Do While bytesLeft
        bytes = bytesLeft
        If bytes > ChunkSize Then bytes = ChunkSize
       
        tmp = fld.GetChunk(bytes)
        Put #fnum, , tmp
        bytesLeft = bytesLeft - bytes
    Loop
    Close #fnum
End Sub