Dim rst As New ADODB.Recordset rst.ActiveConnection = Cn rst.CursorType = adOpenKeyset rst.CursorLocation = adUseClient rst.LockType = adLockReadOnly rst.Source = "SELECT txtfile FROM TABLE" rst.Open Dim mStream As ADODB.Stream Set mStream = New ADODB.Stream mStream.Type = adTypeBinary mStream.Open If Not IsNull(rst.Fields("txtfile")) Then mStream.Write rstImg.Fields("txtfile") mStream.SaveToFile "c:\temp.txt" end if
'将任何文件从数据库中下载到本地: Public Function LoadFile(ByVal col As ADODB.Field, ByVal filename As String) As Boolean '获得binary数据 On Error GoTo myerr: Dim arrBytes() As Byte Dim FreeFileNumber As Integer lngsize = col.ActualSize arrBytes = col.GetChunk(lngsize) FreeFileNumber = FreeFile Open filename For Binary Access Write As #FreeFileNumber Put #FreeFileNumber, , arrBytes Close #FreeFileNumber LoadFile = True myerr: If Err.Number <> 0 Then LoadFile = False Err.Clear End If End Function'将文件从本地上传到数据库中 Public Function UpLoadFile(ByVal filename, ByVal col As ADODB.Field) As Boolean On Error GoTo myerr: Dim arrBytes() As Byte Dim FreeFileNumber As Integer FreeFileNumber = FreeFile Open filename For Binary As #FreeFileNumber n = LOF(FreeFileNumber) ReDim arrBytes(1 To n) As Byte Get #FreeFileNumber, , arrBytes Close #FreeFileNumber col.AppendChunk (arrBytes) UpLoadFile = True myerr: If Err.Number <> 0 Then UpLoadFile = False Err.Clear End If End Function
这是sqlserver的,oracle也应该差不多(只是字段类型改一下即可) '假设数据库中对应的表为tb1, '结构如:create tabel tb1(id int identity(1,1),WordValue image) '那么我就可以将word文档存入此表的WordValue字段Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim stm As ADODB.StreamPrivate Sub SaveDocToDB(cn As ADODB.Connection) On Error GoTo EH Set stm = New ADODB.Stream rs.Open "select WordValue from tbl", cn, adOpenKeyset, adLockOptimistic CommonDialog1.ShowOpen Text1.Text = CommonDialog1.FileName
With stm .Type = adTypeBinary .Open .LoadFromFile CommonDialog1.FileName End With With rs .AddNew .Fields("wordValue") = stm.Read .Update End With rs.Close Set rs = Nothing Exit Sub EH: MsgBox Err.Description, vbInformation, "Error" End Sub Private Sub LoadDcFromDB(cn As ADODB.Connection) '读出 On Error GoTo EH Dim strTemp As String Set stm = New ADODB.Stream strTemp = "c:\temp.doc" '临时文件,用来保存读出的文档 rs.Open "select WordValue from tbl where id=1", cn, , , adCmdText With stm .Type = adTypeBinary .Open .Write rs("WordValue") .SaveToFile strTemp, adSaveCreateOverWrite .Close End With Set stm = Nothing rs.Close Set rs = Nothing Exit Sub EH: MsgBox Err.Description, vbInformation, "Error" End Sub
rst.ActiveConnection = Cn
rst.CursorType = adOpenKeyset
rst.CursorLocation = adUseClient
rst.LockType = adLockReadOnly
rst.Source = "SELECT txtfile FROM TABLE"
rst.Open
Dim mStream As ADODB.Stream
Set mStream = New ADODB.Stream
mStream.Type = adTypeBinary
mStream.Open
If Not IsNull(rst.Fields("txtfile")) Then
mStream.Write rstImg.Fields("txtfile")
mStream.SaveToFile "c:\temp.txt"
end if
Public Function LoadFile(ByVal col As ADODB.Field, ByVal filename As String) As Boolean '获得binary数据
On Error GoTo myerr:
Dim arrBytes() As Byte
Dim FreeFileNumber As Integer
lngsize = col.ActualSize
arrBytes = col.GetChunk(lngsize)
FreeFileNumber = FreeFile
Open filename For Binary Access Write As #FreeFileNumber
Put #FreeFileNumber, , arrBytes
Close #FreeFileNumber
LoadFile = True
myerr:
If Err.Number <> 0 Then
LoadFile = False
Err.Clear
End If
End Function'将文件从本地上传到数据库中
Public Function UpLoadFile(ByVal filename, ByVal col As ADODB.Field) As Boolean
On Error GoTo myerr:
Dim arrBytes() As Byte
Dim FreeFileNumber As Integer
FreeFileNumber = FreeFile
Open filename For Binary As #FreeFileNumber
n = LOF(FreeFileNumber)
ReDim arrBytes(1 To n) As Byte
Get #FreeFileNumber, , arrBytes
Close #FreeFileNumber
col.AppendChunk (arrBytes)
UpLoadFile = True
myerr:
If Err.Number <> 0 Then
UpLoadFile = False
Err.Clear
End If
End Function
'假设数据库中对应的表为tb1,
'结构如:create tabel tb1(id int identity(1,1),WordValue image)
'那么我就可以将word文档存入此表的WordValue字段Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim stm As ADODB.StreamPrivate Sub SaveDocToDB(cn As ADODB.Connection)
On Error GoTo EH
Set stm = New ADODB.Stream
rs.Open "select WordValue from tbl", cn, adOpenKeyset, adLockOptimistic
CommonDialog1.ShowOpen
Text1.Text = CommonDialog1.FileName
With stm
.Type = adTypeBinary
.Open
.LoadFromFile CommonDialog1.FileName
End With
With rs
.AddNew
.Fields("wordValue") = stm.Read
.Update
End With
rs.Close
Set rs = Nothing
Exit Sub
EH: MsgBox Err.Description, vbInformation, "Error"
End Sub
Private Sub LoadDcFromDB(cn As ADODB.Connection)
'读出
On Error GoTo EH
Dim strTemp As String
Set stm = New ADODB.Stream
strTemp = "c:\temp.doc" '临时文件,用来保存读出的文档
rs.Open "select WordValue from tbl where id=1", cn, , , adCmdText
With stm
.Type = adTypeBinary
.Open
.Write rs("WordValue")
.SaveToFile strTemp, adSaveCreateOverWrite
.Close
End With
Set stm = Nothing
rs.Close
Set rs = Nothing
Exit Sub
EH: MsgBox Err.Description, vbInformation, "Error"
End Sub
如果是msdora,那么就没有BLOB这个数据类型。