Solved

ADO manipulating Oracle CLOBS/BLOBS

Posted on 2001-06-13
10
1,224 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month6 days, 9 hours left to enroll

636 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