Solved

ADO manipulating Oracle CLOBS/BLOBS

Posted on 2001-06-13
10
1,182 Views
Last Modified: 2007-12-19
Can anybody point me at any examples that show how to manipulate Oracle CLOBS and/or BLOBS using ADO and the Oracle OLEDB interface?

0
Comment
Question by:jasonclarke
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 5

Expert Comment

by:AndrewDev
ID: 6185534
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
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6185578
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
 
LVL 5

Expert Comment

by:AndrewDev
ID: 6185603
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
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6185736
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
 
LVL 9

Author Comment

by:jasonclarke
ID: 6185749
Thanks for the information, give me a little while to try out the code, and I will be back...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Accepted Solution

by:
nigelrowe earned 100 total points
ID: 6185750
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
 
LVL 9

Author Comment

by:jasonclarke
ID: 6186853
Thanks, that is all very helpful stuff!
0
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6187016
You're welcome...

Cheers
0
 

Expert Comment

by:farri_no2
ID: 8628842
i cannot find "SQLEncode" function in the normal vb6 IDE ... can somebody guide me ?
0
 

Expert Comment

by:bcsapps
ID: 8642357
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now