ADO manipulating Oracle CLOBS/BLOBS

Can anybody point me at any examples that show how to manipulate Oracle CLOBS and/or BLOBS using ADO and the Oracle OLEDB interface?

LVL 9
jasonclarkeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AndrewDevCommented:
Still searching for example of
Oracle CLOBS and/or BLOBS using ADO. The connection string for ADO & Oracle are below:

Oracle OLEDB interface using ADO DSN-less connection

For the current Oracle ODBC Driver from Microsoft:

oConn.Open "Driver={Microsoft ODBC for Oracle};" & _
                   "Server=OracleServer.world;" & _
                   "Uid=myUsername;" & _
                   "Pwd=myPassword;"

For the older Oracle ODBC Driver from Microsoft:

oConn.Open "Driver={Microsoft ODBC Driver for Oracle};" & _
                   "ConnectString=OracleServer.world;" & _
                   "Uid=myUsername;" & _
                   "Pwd=myPassword;"


This came from http://www.able-consulting.com/ADO_Conn.htm
0
nigelroweCommented:
Heres an example of the two for inserting (I think you'll work out for yourself how to extract, if not let me know)...

Private Sub InsertEntryField(ByVal id_entry As Long, ByVal od_field As Variant, ByVal has_children As Variant, ByVal od_father As Variant, ByVal tag_field As Variant, Optional ByVal txt_field As String, Optional ByRef imgage_array As Variant)
On Error GoTo ErrorHandler
Dim Conn As New ADODB.Connection
Dim Rs As ADODB.Recordset
Dim sqlclause As String

    sqlclause = "SELECT identry, odfield, haschildren, odfather, tagfield, txtfield, imgfield FROM entryfield WHERE 1 <> 1"
    Conn.Open Env.GetConnectionString
    Set Rs = New ADODB.Recordset
    Rs.Open sqlclause, Conn, adOpenKeyset, adLockOptimistic
    Rs.AddNew
    Rs.fields("identry").Value = SQLEncode(id_entry)
    Rs.fields("odfield").Value = SQLEncode(od_field)
    Rs.fields("haschildren").Value = SQLEncode(has_children)
    Rs.fields("odfather").Value = SQLEncode(od_father)
    Rs.fields("tagfield").Value = tag_field
    Rs.Update
    If Not IsMissing(txt_field) Then
        Rs.fields("txtfield").AppendChunk txt_field
        Rs.Update
    End If
    If Not IsMissing(imgage_array) Then
        Rs.fields("imgfield").AppendChunk imgage_array
        Rs.Update
    End If
    Rs.Close
    Set Rs = Nothing
    Conn.Close
    Set Conn = Nothing
    Exit Sub
ErrorHandler:
    Abort "InsertEntryField"
    Err.Raise Err.Number, Err.Source, RTErrDescription(TypeName(Me), "InsertEntryField", Err.Description)
End Sub

The image_array variable is a variant array created from a binary file existing on the hard disk...

    fhandle = FreeFile
    Open fname For Binary Access Read As fhandle
    binary_array = InputB(LOF(fhandle), fhandle)
    Close fhandle
The text_field variable is just a string
0
AndrewDevCommented:
There appears to be a discussion on ADO with BLOBS/CLOBS here. I don't know how useful it will be but it might give you some clues
http://www.orafans.com/bbs/middleware/messages/520.htm

Regards
Andrew
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

nigelroweCommented:
Here's one for retrieving data from a BLOB....

Public Function GetDocument(ByVal id As Long, ByVal od As Integer) As String
On Error GoTo ErrorHandler
    Dim Conn As New ADODB.Connection
    Dim Rs As ADODB.Recordset
    Dim sqlclause As String
    Dim filehandle As Long
    Dim docobject() As Byte, docsize As Variant, docoffset As Long
    CheckState "GetDocument"
    ' get an image from the entryfield table and store it to a temporary file
    Set Conn = OpenNewConnection("publication")
    sqlclause = "SELECT identry, imgfield FROM entryfield" & _
                " WHERE identry = " & SQLEncode(id) & " AND odfield = " & SQLEncode(od)
    Set Rs = Conn.Execute(sqlclause)
    docsize = SQLDecode(Rs.Fields("imgfield").ActualSize)
    If docsize <> "" And docsize <> 0 Then
        temp_filename = doc_directory & "tempentryfield" & id & ".bin"
        If Dir(temp_filename) = "" Then
            ReDim docobject(docsize)
            docobject() = Rs.Fields("imgfield").GetChunk(docsize)
            filehandle = FreeFile
            Open temp_filename For Binary Access Write As filehandle
            Put filehandle, , docobject()
            Close filehandle
        End If
        IsGot = True
    Else
        temp_filename = ""
        IsGot = False
    End If
    Conn.Close
    Set Conn = Nothing
    GetDocument = temp_filename
    Exit Function
ErrorHandler:
    Err.Raise Err.Number, Err.Source, "GetDocument: " & Err.Description
End Function
0
jasonclarkeAuthor Commented:
Thanks for the information, give me a little while to try out the code, and I will be back...
0
nigelroweCommented:
And here's one for retrieving a CLOB...

Public Sub Create(ByVal an_env As String, ByVal id_entry As Long, ByVal od_field As Integer)
On Error GoTo error_handler
Dim Conn As New ADODB.Connection
Dim entryfield_Rs As ADODB.Recordset
Dim sqlclause As String
Dim fieldsize As Long
   
    LoadEnv an_env
    'object_status = OS_INITIALIZED
    sqlclause = "SELECT identry,odfield,haschildren,odfather,tagfield,txtfield FROM " & _
         "entryfield WHERE identry = " & _
        SQLEncode(id_entry) & " AND odfield = " & SQLEncode(od_field)
    Conn.Open Env.GetConnectionString
    Set entryfield_Rs = Conn.Execute(sqlclause)
    If entryfield_Rs.EOF Then
        Conn.Close
        Set Conn = Nothing
        Err.Raise PtolemyFatal, Err.Source, "Cannot find record where identry = " & id_entry & " and odfield = " & od_field
    End If
   
    With entryfield_Rs
        identry = SQLDecode(.Fields("identry").Value)
        odfield = SQLDecode(.Fields("odfield").Value)
        haschildren = SQLDecode(.Fields("haschildren").Value)
        odfather = SQLDecode(.Fields("odfather").Value)
        tagfield = SQLDecode(.Fields("tagfield").Value)
        fieldsize = .Fields("txtfield").ActualSize
        If fieldsize > 0 Then
            txtfield = .Fields("txtfield").GetChunk(fieldsize)
        Else
            txtfield = ""
        End If
    End With
    is_new = False
    Conn.Close
    Set Conn = Nothing
    Exit Sub
error_handler:
    Abort "Create"
    Err.Raise Err.Number, Err.Source, RTErrDescription(TypeName(Me), "Create", Err.Description & " " & sqlclause)
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jasonclarkeAuthor Commented:
Thanks, that is all very helpful stuff!
0
nigelroweCommented:
You're welcome...

Cheers
0
farri_no2Commented:
i cannot find "SQLEncode" function in the normal vb6 IDE ... can somebody guide me ?
0
bcsappsCommented:
Hi Guys,

I have the same issue in hand. But the problem is I'm not able to open a recordset that selects the IMAGE field. It is of BLOB oracle data type. It was working when the data type was a LONG RAW. But after it was changed to BLOB, I get the following error while trying to open the recordset:

Failed to open rsAssess recordset. -2147467259 - Unspecified error
Source: Provider

Does any one has a solution for this?

Thanks,
bcsapps
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.