各位大虾:
请问VB中如何存取ORACLE数据库的CLOB类型的字段?
我把CLOB类型的字段象普通的VARCHAR2字段一样存入数据库,用SQL PLUS WORKSHEET可以查到该字段的值,但是,我用以下SQL语句查询,执行时提示“数据类型不支持”,
请问:如何解决?strSql = "Select tbl,priCatalogID,isvalid,OE_row,OE_col,OE_page,slice,db,"
strSql = strSql & "cube,userid,createtime,nodeInfo,graphtype,"
strSql = strSql & "pagepos,filter,ispublished From " & gstrSchema & "preReport "
strSql = strSql & "Where preRptID="
strSql = strSql & Mid(sKey, 7)说明:nodeInfo、filter字段为CLOB类型。
请问VB中如何存取ORACLE数据库的CLOB类型的字段?
我把CLOB类型的字段象普通的VARCHAR2字段一样存入数据库,用SQL PLUS WORKSHEET可以查到该字段的值,但是,我用以下SQL语句查询,执行时提示“数据类型不支持”,
请问:如何解决?strSql = "Select tbl,priCatalogID,isvalid,OE_row,OE_col,OE_page,slice,db,"
strSql = strSql & "cube,userid,createtime,nodeInfo,graphtype,"
strSql = strSql & "pagepos,filter,ispublished From " & gstrSchema & "preReport "
strSql = strSql & "Where preRptID="
strSql = strSql & Mid(sKey, 7)说明:nodeInfo、filter字段为CLOB类型。
Dim iStm As ADODB.Stream
Dim iRe As ADODB.Recordset
Dim iConcStr As String
'数据库连接字符串
iConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
";Data Source=F:\My Documents\客户资料1.mdb"
'读取文件到内容
Set iStm = New ADODB.Stream
With iStm
.Type = adTypeBinary '二进制模式
.Open
.LoadFromFile "c:\test.doc"
End With
'打开保存文件的表
Set iRe = New ADODB.Recordset
With iRe
.Open "表", iConc, adOpenKeyset, adLockOptimistic
.AddNew '新增一条记录
.Fields("保存文件内容的字段") = iStm.Read
.Update
End With
'完成后关闭对象
iRe.Close
iStm.Close
End SubPrivate Sub Command2_Click()
Dim iStm As ADODB.Stream
Dim iRe As ADODB.Recordset
Dim iConc As String
'数据库连接字符串
iConc = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
";Data Source=\\xz\c$\Inetpub\zj\zj\zj.mdb"
'打开表
Set iRe = New ADODB.Recordset
iRe.Open "tb_img", iConc, adOpenKeyset, adLockReadOnly
iRe.Filter = "id=64"
'保存到文件
Set iStm = New ADODB.Stream
With iStm
.Mode = adModeReadWrite
.Type = adTypeBinary
.Open
.Write iRe("img")
.SaveToFile "c:\test.doc"
End With
'关闭对象
iRe.Close
iStm.Close
End Sub
Public Function WriteToDB(ByRef col As ADODB.Field, ByVal FileName As String) As Boolean
On Error GoTo ErrMsg
Dim mStream As ADODB.Stream
Set mStream = New ADODB.Stream
WriteToDB = False
mStream.Type = adTypeBinary
mStream.Open
mStream.LoadFromFile FileName
col.Value = mStream.Read
mStream.Close
Set mStream = Nothing
WriteToDB = True
Exit Function
ErrMsg:
MsgBox "存储照片到数据库时出现错误." & vbCrLf & Err.Description, vbExclamation + vbOKOnly, "提示"
End Function'设置临时照片文件
Public Function ReadDB(col As ADODB.Field, ByRef imgFile As String) As Boolean
On Error GoTo ErrRead
Dim mStream As New ADODB.Stream
ReadDB = False
If col.ActualSize < 200 Then Exit Function
mStream.Type = adTypeBinary
mStream.Open
mStream.Write col.Value
mStream.SaveToFile imgFile, adSaveCreateOverWrite
ReadDB = True
Exit Function
ErrRead:
MsgBox "设置临时照片文件时出现错误:" & vbCrLf & Err.Description, vbInformation, "提示"
ReadDB = False
End Function
我也遇到和楼主一样的困扰,其实楼主你没发现
如果clob的值长度4000,将不能正常的用sql语句插入
直接在SQL PLUS中运行时是否能取得数据?用过ORACLE数据库,没有出现你描述的这种情况
Dim rstOra As ADODB.Recordset
Dim intI As Integer
On Error GoTo ErrorHandler
Set adoCnn = New ADODB.Connection
Set rstOra = New ADODB.Recordset
adoCnn.ConnectionString = "Provider=OraOLEDB.Oracle;User ID=test;password=test;Data Source=oraDATA;Persist Security Info=False"
adoCnn.CursorLocation = adUseClient
adoCnn.Open
rstOra.CursorLocation = adUseClient rstOra.ActiveConnection = adoCnn
rstOra.Open "select picture from Testrecord"注意:Provider=OraOLEDB.Oracle 处及rstOra.CursorLocation = adUseClient处不能用传统的连接方式:Provider=MSDAORA.1
Dim actualSize As Long
Dim offSize As Long
Dim varReport As Variant
Dim varChunk As VariantactualSize = adoRec("Picture").actualSize
offSize = 0
Do While offSize < actualSize
varChunk = adoRec("Picture").GetChunk(ChunkSize)
varReport = varReport & varChunk
offSize = offSize + ChunkSize
DoEvents
Loop
无论是c/s方式还是通过asp的web调用,都获得了成功
问题的关键在于要安装oracle的ole db provider
安装以后连接串Provider使用Provider=OraOLEDB.Oracle 就可以了
至于CursorLocation 不是关键问题
此外还有许多需要注意的问题,很难一一详述,留下联系方式,我们可以一起讨论