Re: How to Retrieve CLOB with ADODB Command Parameter (StoredProc), NOT by a Select statement

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Paul Clement (UseAdddressAtEndofMessage_at_swspectrum.com)
Date: 10/15/04

Next message: Unforgiven: "Re: ADO Execute method slow. Need help..."

Previous message: Sri: "RE: StoredProcedure running an Update will not return a following sele"
In reply to: Khodr: "Re: How to Retrieve CLOB with ADODB Command Parameter (StoredProc), NOT by a Select statement"
Messages sorted by: [ date ] [ thread ]

Date: Fri, 15 Oct 2004 13:45:40 -0500

On Thu, 7 Oct 2004 10:09:35 -0400, "Khodr" <khodr007@excite.com> wrote:

¤ Hi Val,
¤
¤ Thanks for your answer. I am not using Microsoft ODBC Provider. I am useing
¤ Oracle ODBC Provider {Oracle in OraHome81}.
¤ So whatever the provider is, using GetChunk/AppendChunk or not, does not
¤ matter to me. My question is: What data type I should use in StoredProc to
¤ return the CLOB?
¤
¤ So the priority is now to successfully call the StoredProc. Then I will use
¤ GetChunk/AppendChunk. But the StoredProc is failing now because there is an
¤ Output Parameter that returns a CLOB and I am specifying the data type as
¤ adVarChar. I also tried adLongVarChar as I mentioned below. They all fail.
¤ The question again: What data type should I use in the StoredProc?
¤
¤ Thanks again. Any other suggestionss will be highly appreciated.
¤
¤ A friendly reminder: I am using VB.NET
¤

I don't believe ADO supports CLOB data types with an Oracle stored procedure. You would probably
need to use ADO.NET instead if a stored procedure is required.

Below is an article from Oracle's Metalink site which uses SQL:

Doc ID: Note:126125.1
Subject: ADO Streaming BLOB & CLOB Example Using ODBC and OLEDB in VB (SCR 1388)
Type: SAMPLE CODE
Status: PUBLISHED
Content Type: TEXT/X-HTML
Creation Date: 01-DEC-2000
Last Revision Date: 09-DEC-2002


Abstract
The following Visual Basic code takes a binary file and text file and uses the ADO stream object to
store and retrieve data from an Oracle database using both Oracle's ODBC Driver and OLE DB Provider.

Product Name, Product Version Oracle ODBC Driver, versions 8.1.7, 9.0, and 9.2
Oracle Provider for OLE DB, versions 8.1.7, 9.0, and 9.2

Platform Windows 95, 98, NT, 2000, and XP Professional
Date Created 01-DEC-2000

Instructions
Execution Environment:
     Visual Basic 6.0
     SQL*Plus

Access Privileges:
Requires access to the EMP table in the SCOTT demo schema.

Usage:
Run the sample from within the Visual Basic development environment.

Instructions:
     1. Open a new Standard EXE project in Visual Basic.

     2. Choose Project | References... and check the following:

         * Microsoft ActiveX Data Objects 2.6 (or 2.7) Library
         * OraOLEDB 1.0 Type Library

     3. On an empty VB form add four command buttons with the following properties:

         CommandButton1
             Name = cmdSaveBLOBToDB
             Caption = 'Store BLOB in DB'

         CommandButton2
             Name = cmdSaveBLOBToFile
             Caption = 'Save BLOB to File'

         CommandButton3
             Name = cmdSaveCLOBToDB
             Caption = 'Store CLOB in DB'

         CommandButton4
             Name = cmdSaveCLOBToFile
             Caption = 'Save CLOB to File'

     4. View the code of the VB form and make sure it is blank. You should be in the
         object = (General) and procedure = (Declarations) section of the code window.
         Paste the entire sample code below into VB's code window.

     5. Set the BlobFileName constant to the name of a binary file, such as .jpg, .bmp,
         .tif, etc., and put this file in the 'C:\' folder.

     6. Set the ClobFileName constant to the name of a text file and put this file into
         the 'C:\' folder.

     7. Set the value of the ODBCConnectSTring and the OLEDBConnectString constants to valid
         parameters for connecting to your database.

     8. Uncomment the ConnectivityMethod constant to designate the method for accessing
         the Oracle database: ODBC or OLE DB.

     9. Create a table to hold the LOBs by executing the following SQL*Plus statement:

             create table LOB_TABLE (
               FIELD VARCHAR2(50),
               BLOBFIELD BLOB,
               CLOBFIELD CLOB);

     10. You are now ready to run the sample code. The LOBs will be retrieved and stored
         into the 'C:\Temp' folder on your computer.

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.

Description
Prerequisites:
     * Microsoft Visual Basic 6.0 service pack 5
     * Microsoft Data Access Components (MDAC) version 2.6 or 2.7
     * Oracle client software version 8.1.7, 9.0, or 9.2
     * Oracle ODBC Driver version 8.1.7, 9.0, or 9.2
     * Oracle Provider for OLE DB version 8.1.7, 9.0, or 9.2

References
This sample was taken from Sample Code Repository (SCR) Entry 1388.

Sample Code
'
' ********************************
' *** Streaming LOB Examples ***
' ********************************
' *** Oracle Worldwide Support ***
' *** Created 10 Aug 2000 ***
' ********************************
'
' ADOStreamLOBs
'
' The following code contains examples of inserting and retrieving BLOBs and
' CLOBs with VB using the ADO Object Model with Stream objects.
'

Option Explicit

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mStream As ADODB.Stream
Dim ConnStr As String

' Set up you ODBC and OLE DB connect strings here
Const ODBCConnectString = "DSN=oracleu;UID=scott;PWD=tiger;"
Const OLEDBConnectString = "Data Source=geoff;User ID=scott;Password=tiger;"

' Set up the name and locations of the source and destination paths of the BLOB file
Const BlobFileName = "blobtest.jpg"
Const BlobSourcePath = "C:\"
Const BlobDestPath = "C:\Temp\"

' Set up the name and locations of the source and destination paths of the CLOB file
Const ClobFileName = "clobtest.txt"
Const ClobSourcePath = "C:\"
Const ClobDestPath = "C:\Temp\"

' Uncomment the connectivity method you wish to use
Const ConnectivityMethod = "ODBC"
'Const ConnectivityMethod = "OLEDB"

Private Sub cmdSaveCLOBToFile_Click()

' ******************************************************************
' *** This event will take a CLOB that has already been inserted ***
' *** into the database and save it as a file. ***
' ******************************************************************


    On Error GoTo ErrorHandler

    Screen.MousePointer = vbHourglass

    Set cn = New ADODB.Connection

' Set the character set for you particular type of text data
mStream.Charset = "us-ascii"

' Set it to a text file type
    mStream.Type = adTypeText

' Open it
    mStream.Open

' This writes the text file from the clob field to the buffer
    mStream.WriteText rs.Fields("CLOBFIELD").Value

' This saves the stream to a file on disk
    mStream.SaveToFile ClobDestPath & rs.Fields("FIELD").Value

    mStream.Close
    Set mStream = Nothing

    rs.Close
    Set rs = Nothing

    cn.Close
    Set cn = Nothing

    Screen.MousePointer = vbDefault

    MsgBox "Clob saved to file as " & ClobDestPath & ClobFileName, , "Clob Saved"

Exit Sub

ErrorHandler:

Screen.MousePointer = vbDefault

    Select Case Err.Number
      Case 3004
        MsgBox "Could not write file.", , "File Already Exists"
      Case Else
        MsgBox Err.Number & " - " & Err.Description, , "Error Msg"
    End Select

End Sub

Private Sub cmdSaveCLOBToDB_Click()

' *******************************************************************
' *** This event will insert a CLOB from a file into the database ***
' *******************************************************************


    On Error GoTo ErrorHandler

    Screen.MousePointer = vbHourglass

    Set cn = New ADODB.Connection


    If ConnectivityMethod = "ODBC" Then
' ODBC Connection Setup

      Set cn = New ADODB.Connection
      With cn
         .ConnectionString = ODBCConnectString
         .Open
      End With

    Else
' OLEDB Provider Connection Setup

      cn.Provider = "OraOLEDB.Oracle"
      cn.ConnectionString = OLEDBConnectString
      cn.Open

    End If


    Set rs = New ADODB.Recordset

    rs.Open "Select * from LOB_TABLE", cn, adOpenKeyset, adLockOptimistic

' Create the ADO Stream object
    Set mStream = New ADODB.Stream

' Set the character set for you particular type of text data
    mStream.Charset = "us-ascii"

' Make it a text type
    mStream.Type = adTypeText

' Open the stream
    mStream.Open

' Read the text file into the stream buffer
    mStream.LoadFromFile ClobSourcePath & ClobFileName

' Add the blob to the database
    With rs
        .AddNew
        .Fields("CLOBFIELD").Value = mStream.ReadText
        .Fields("FIELD").Value = ClobFileName
        .Update
    End With

    mStream.Close
    Set mStream = Nothing

    rs.Close
    Set rs = Nothing

    cn.Close
    Set cn = Nothing

    Screen.MousePointer = vbDefault

    MsgBox "Clob inserted into DB from " & ClobSourcePath & ClobFileName, , "Clob Inserted"

Exit Sub

ErrorHandler:

Screen.MousePointer = vbDefault

    Select Case Err.Number
      Case 3002
        MsgBox "Could not read file, check the path.", , "File Not Found"
      Case Else
        MsgBox Err.Number & " - " & Err.Description, , "Error Msg"
    End Select

End Sub

Private Sub cmdSaveBLOBToDB_Click()

' *******************************************************************
' *** This event will insert a BLOB from a file into the database ***
' *******************************************************************


    On Error GoTo ErrorHandler

    Screen.MousePointer = vbHourglass

    Set cn = New ADODB.Connection


    If ConnectivityMethod = "ODBC" Then
' ODBC Connection Setup

      Set cn = New ADODB.Connection
      With cn
         .ConnectionString = ODBCConnectString
         .Open
      End With

    Else
' OLEDB Provider Connection Setup

      cn.Provider = "OraOLEDB.Oracle"
      cn.ConnectionString = OLEDBConnectString
      cn.Open

    End If


    Set rs = New ADODB.Recordset

    rs.Open "Select * from LOB_TABLE", cn, adOpenKeyset, adLockOptimistic

' Create the ADO Stream object
    Set mStream = New ADODB.Stream

' Make it a binary type
    mStream.Type = adTypeBinary

' Open the stream
    mStream.Open

' Read the binary file into the stream buffer
    mStream.LoadFromFile BlobSourcePath & BlobFileName

' Add the blob to the database
    With rs
        .AddNew
        .Fields("BLOBFIELD").Value = mStream.Read
        .Fields("FIELD").Value = BlobFileName
        .Update
    End With

    mStream.Close
    Set mStream = Nothing

    rs.Close
    Set rs = Nothing

    cn.Close
    Set cn = Nothing

    Screen.MousePointer = vbDefault

    MsgBox "Blob inserted into DB from " & BlobSourcePath & BlobFileName, , "Blob Inserted"

Exit Sub

ErrorHandler:

Screen.MousePointer = vbDefault

Private Sub cmdSaveBLOBToFile_Click()

' ******************************************************************
' *** This event will take a BLOB that has already been inserted ***
' *** into the database and save it as a file. ***
' ******************************************************************


    On Error GoTo ErrorHandler

    Screen.MousePointer = vbHourglass

    Set cn = New ADODB.Connection

    If ConnectivityMethod = "ODBC" Then
' ODBC Connection Setup

      Set cn = New ADODB.Connection
      With cn
         .ConnectionString = ODBCConnectString
         .Open
      End With

    Else
' OLEDB Provider Connection Setup

      cn.Provider = "OraOLEDB.Oracle"
      cn.ConnectionString = OLEDBConnectString
      cn.Open

    End If


    Set rs = New ADODB.Recordset

' 'blobtest.txt' is the name of a BINARY file that has been stored in LOB_TABLE in the db
    rs.Open "Select * from LOB_TABLE where FIELD = '" & BlobFileName & "'", cn, _
        adOpenKeyset, adLockOptimistic

' Create ADO stream object
    Set mStream = New ADODB.Stream

' Set it to a binary file type
    mStream.Type = adTypeBinary

' Open it
    mStream.Open

' This writes the image from the blob field to the buffer
    mStream.Write rs.Fields("BLOBFIELD").Value

' This saves the stream to a file on disk
    mStream.SaveToFile BlobDestPath & rs.Fields("FIELD").Value

    mStream.Close
    Set mStream = Nothing

    rs.Close
    Set rs = Nothing

    cn.Close
    Set cn = Nothing

    Screen.MousePointer = vbDefault

    MsgBox "Blob saved to file as " & BlobDestPath & BlobFileName, , "Blob Saved"

Exit Sub

ErrorHandler:

Screen.MousePointer = vbDefault

Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

Next message: Unforgiven: "Re: ADO Execute method slow. Need help..."

Previous message: Sri: "RE: StoredProcedure running an Update will not return a following sele"
In reply to: Khodr: "Re: How to Retrieve CLOB with ADODB Command Parameter (StoredProc), NOT by a Select statement"
Messages sorted by: [ date ] [ thread ]