Solved

ADO manipulating Oracle CLOBS/BLOBS

Posted on 2001-06-13
10
1,191 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Use closed file on desktop in vba 6 65
Updates not working for MS Windows 7 12 152
Using "ScreenUpdating" 6 55
Visual Studio search word table and return Cell index 8 48
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 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

26 Experts available now in Live!

Get 1:1 Help Now