下面是一个网上的文章,存取ORACLE的BLOB字段,但运行出错,cmdsave_Click()中rs.open说数据类型不支持,为何,真的VB不支持ORACLE的BLOB吗?
原文Provider=OraOLEDB.Oracle.1;,但在我机上试打不开数据库,改成MSDAORA.1 Option Explicit
Dim rn As ADODB.Connection Public Function CreateDataSource(DataSource As String, UserID As String, Password As String) As Boolean
On Error GoTo DbConErr:
Set rn = New ADODB.Connection
With rn
.ConnectionString = "Provider=MSDAORA.1; password=dasm ;User ID = archives;Data Source=tjdasm;Locale Identifier=2052"
.Open
End With
CreateDataSource = True
Exit Function
DbConErr:
CreateDataSource = False
End FunctionSub BlobToFile(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
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 SubSub 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
If Dir$(filename) = "" Then Err.Raise 53, , "File not found"
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 fnum, , tmp
fld.AppendChunk tmp
bytesleft = bytesleft - bytes
Loop
Close #fnum
End SubPrivate Sub cmdread_Click()
Dim rs As New ADODB.Recordset
rs.ActiveConnection = rn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = "select * from t_demo"
rs.Open
ComDlgDir.DialogTitle = "保存文件"
ComDlgDir.Filter = "*.*"
ComDlgDir.ShowSave
Call BlobToFile(rs.Fields("text"), ComDlgDir.filename)
Set rs = Nothing
Exit Sub
Set rs = NothingEnd SubPrivate Sub cmdsave_Click()
Dim rs As New ADODB.Recordset
rs.ActiveConnection = rn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = "select * from t_demo"
rs.Open '数据类型不被支持
rs.AddNew
ComDlgDir.DialogTitle = "选取文件"
ComDlgDir.ShowOpen
rs.Fields("id").Value = 1
If ComDlgDir.filename <> "" Then
Call FileToBlob(rs.Fields("text"), ComDlgDir.filename)
rs.Update
End If
Set rs = Nothing
Exit Sub
Set rs = NothingEnd SubPrivate Sub Form_Load()
If Not CreateDataSource("sid", "systemp", "manager") Then
MsgBox "Connection failure!"
End IfEnd Sub
原文Provider=OraOLEDB.Oracle.1;,但在我机上试打不开数据库,改成MSDAORA.1 Option Explicit
Dim rn As ADODB.Connection Public Function CreateDataSource(DataSource As String, UserID As String, Password As String) As Boolean
On Error GoTo DbConErr:
Set rn = New ADODB.Connection
With rn
.ConnectionString = "Provider=MSDAORA.1; password=dasm ;User ID = archives;Data Source=tjdasm;Locale Identifier=2052"
.Open
End With
CreateDataSource = True
Exit Function
DbConErr:
CreateDataSource = False
End FunctionSub BlobToFile(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
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 SubSub 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
If Dir$(filename) = "" Then Err.Raise 53, , "File not found"
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 fnum, , tmp
fld.AppendChunk tmp
bytesleft = bytesleft - bytes
Loop
Close #fnum
End SubPrivate Sub cmdread_Click()
Dim rs As New ADODB.Recordset
rs.ActiveConnection = rn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = "select * from t_demo"
rs.Open
ComDlgDir.DialogTitle = "保存文件"
ComDlgDir.Filter = "*.*"
ComDlgDir.ShowSave
Call BlobToFile(rs.Fields("text"), ComDlgDir.filename)
Set rs = Nothing
Exit Sub
Set rs = NothingEnd SubPrivate Sub cmdsave_Click()
Dim rs As New ADODB.Recordset
rs.ActiveConnection = rn
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.Source = "select * from t_demo"
rs.Open '数据类型不被支持
rs.AddNew
ComDlgDir.DialogTitle = "选取文件"
ComDlgDir.ShowOpen
rs.Fields("id").Value = 1
If ComDlgDir.filename <> "" Then
Call FileToBlob(rs.Fields("text"), ComDlgDir.filename)
rs.Update
End If
Set rs = Nothing
Exit Sub
Set rs = NothingEnd SubPrivate Sub Form_Load()
If Not CreateDataSource("sid", "systemp", "manager") Then
MsgBox "Connection failure!"
End IfEnd Sub
解决方案 »
- SQL 中 where 条件中 in 后面 加 CASE WHEN 语句 报错
- suiziguo大侠 还是你来帮忙解决一下
- 求一SQL语句,要求截取一字段的某一部分插入到另一字段中
- .dmp文件导入Oracle里的问题?
- 在ORACLE中有表变量和临时表吗?
- 新学习Oracle,如何创建一个简单的存储过程
- 不用存储过程如何返回新增记录的主键
- Orcale安装不上去,老提示找不到组件?
- 新手入门,请高人指点
- ="'"&A1&"'"
- 用PL/SQL Developer编译存储过程可不可以只编译其中的一个过程的?如我想编译PKG_OUT_PRESC.Get_All_Dict_Medic_Catalog
- 咨询oracle是否有向SqlServer的事件探查器,我主要是想实现跟踪应用程序执行的SQL
需要用oracle提供的driver
_bstr_t bstrConnect="Provider=OraOLEDB.Oracle;Data Source=orcl;User Id=system;Password=oracle;";
在工程---引用中加入oraoledb.dll