VB在Oracle数据库中存取文件

oracle中可用LOB 字段来存储长文本或者二进制数据，LOB分为BLOB合CLOB，其中BLOB可用来存储二进制数据，如exe、dll、视频、音频等；CLOB只能用来存储字符型文本数据。
举个VB存取BLOB字段的简单例子，首先在oracle中建一有BLOB字段的表
SQL:
create table TEST_TABLE(id number(4) not null,fileName varchar2(255),fileContent BLOB)

在VB中写代码
Dim cnn as New ADODB.Connection  '全局数据
Dim rs1 as New ADODB.Recordset     '全局数据
Dim sql as String                                      ’全局数据

Private Sub Form_Load()
    cnn.ConnectionString = "Provider=OraOledb.Oracle.1;" _
                        & "Password=******;" _
                        & "User ID=******;" _
                        & "Data Source=******;" _
                        & "Persist Security Info=True"
    cnn.Open       '打开数据库连接
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

‘－－－－－－－－－－－－－读取一条记录，获取文件数据，并写入文件－－－－－－－－－－－
Private Sub GetFile()
    strSql = "select * from TEST_TABLE where id=1'"
    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