VB6 Oracle Retrieving Clob into an array

Question

I am trying to retrieve an oracle field with datatype clob into a VB array without reformating the retrieval SQL string (simply SELECT * FROM my_table). I am using an ADO recordset.

The clob values are lost when I close the connection or do any other action with the recordset. How can I achieve this.

Answer 1

In one of my projects I tried to manipulate BLOBs and CLOBs using ADO, but without any success, until I switched to Oracle Objects 4 OLE. You need to install and configure Oracle client on your machine, the in project references add "Oracle InProc Server 4.0 Type Library".

Here's a sample code I used for BLOBs:

Dim OraSession  As OracleInProcServer.OraSessionClass
Dim OraDatabase As OracleInProcServer.OraDatabase
Dim OraDynaset  As OracleInProcServer.OraDynaset
Dim srcImg As OraBlob

Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.DbOpenDatabase("myDBName", "login/password", 0&)

Set OraDynaset = OraDatabase.DbCreateDynaset("SELECT src_id, src_image from Sources where src_file_name like '%.png'", 0&)
With OraDynaset
    If .RecordCount > 0 Then
        .MoveFirst
        While (Not .EOF Or .BOF)
            Set srcImg = .Fields("src_image").Value
            srcImg.CopyToFile ("C:\sources\" & srcID & ".png")
            .MoveNext
        Wend

    End If
End With

Set OraDynaset = Nothing
Set OraDatabase = Nothing
Set OraSession = Nothing