13
Using OraOLEDB to Manipulate LOBs

This chapter contains the following sections:

• Introducing OLE DB
• Manipulating LOBs Using ADO Recordsets and OLE DB Rowsets
• Manipulating LOBs Using OraOLEDB Commands
• ADO and LOBs Example 1: Inserting LOB Data From a File

Introducing OLE DB

OLE DB is an open specification for accessing various types of data from different stores in a uniform way. It uses a set of COM interfaces for accessing and manipulating different types of data. The interfaces are available from various database providers.

OLE DB introduces the concept of a consumer and a provider. A consumer is a client application that uses or 'consumes' an OLE DB interface. A provider is a component that exposes an OLE DB interface.

A typical provider can retrieve data from a particular data store and expose the data to a consumer in tabular form.

OraOLEDB: OLE DB and Oracle Large Object (LOB) Support

OraOLEDB is an OLE DB provider for Oracle. It offers high performance and efficient access to Oracle data including LOBs. It also allows updates to certain LOB types.

The following LOB types are supported by OraOLEDB:

• For Persistent LOBs. READ/WRITE through the rowset.
• For BFILEs. READ-ONLY through the rowset.
• Temporary LOBs are not supported through the rowset.

Rowset Object

Rowset is an OLE DB object that provides READ/WRITE capability to data obtained by executing an SQL SELECT statement or a stored procedure that returns a REF Cursor.

BFILEs can be part of the rowset but they are read-only.

Manipulating LOBs Using ADO Recordsets and OLE DB Rowsets

LOB data is never retrieved and stored in the provider cache. When a server cursor is used, OraOLEDB provides the LOB data to the consumer only when it is requested.

To incur less round trips to the database, reads and writes should be carried out in large chunks for better performance.

Use Explicit Transactions

When using server cursor in an auto-commit mode, all LOB data modifications are transmitted to the database and committed. This means that even if the recordset is in a deferred update mode, the LOB data modifications and any previous deferred updates, will be permanent. To have flexibility of rolling back LOB data modifications, it is advised that explicit transactions are used when manipulation LOB data.

ADO Recordsets and LOBs

GetChunk()

The GetChunk method of ADO recordset object retrieves LOB data. When subsequent GetChunk() calls are made on the same LOB column, data is retrieved from where it left off. However, if the current row changes or if another LOB column is read from or written to, calling GetChunk() again on the original LOB column will retrieve data from the beginning.

Writing Data to a LOB Column With AppendChunk()

The AppendChunk() method of ADO recordset object writes data to a LOB column. The initial AppendChunk() method will overwrite any existing data. Subsequent AppendChunk() calls will append the data, but the appending will end when the current row changes or when another LOB column data is updated or read from.

OLE DB Rowsets and LOBs

The following OLE DB rowset methods read and write LOB data:

• IRowset::GetData() and ISequentialStream::Read() reads LOB data.
• IRowsetChange::SetData() and ISequentialStream::Write() writes LOB data.

Manipulating LOBs Using OraOLEDB Commands

In OraOLEDB, the following functionality is supported:

• LOB input bind parameters, through commands
• Tables with any type of LOB, including BFiles, can be created through commands

LOB input or output parameters are supported in stored procedure executions using OraOLEDB 8.1.7 or higher. In addition, the database must be Oracle8i Release 8.1 or higher.

ADO and LOBs Example 1: Inserting LOB Data From a File

The following is an ADO sample that demonstrates the insertion of a new row with a LOB column. A file called "c:\myfile.txt" will need to be created on your machine for this sample to work. It can be created using your favorite editor to contain any character data such as "This is only a test". This character data will then be used by the program to populate the CLOB column in the MULTIMEDIA_TAB table.

The program then retrieves the newly inserted data from the database and validates the inserted data. The inserted row is then deleted before the program exits.

The example covers the following ADO methods that can be used for LOBs, namely:

• GetChunk method
• AppendChunk method
• ActualSize property

  Sub Main()
    Dim con As New ADODB.Connection 
    Dim cmd As New ADODB.Command 
  
    Dim rst As New ADODB.Recordset 
  
    Dim LogFileName As String 
    Dim LogFileNum As Integer 
  
    Dim sql As String        ' SELECT statement 
    Dim clob_data As Variant ' data from a text file 
    Dim vardata As Variant   ' data retrieved from clob data in chunks 
    Dim vardata_len As Long  ' length of the data retrieved from the CLOB 
  column 
    Dim done As Boolean      ' done = True if finished retrieving all the data 
    Dim Data As Variant      ' the entire data retrieved from the CLOB column 
  
    On Error GoTo ErrorHandler 
  
    ' open a text file 
    LogFileName = "c:\myfile.txt" 
    LogFileNum = FreeFile 
    Open LogFileName For Input As LogFileNum 
  
    ' load text from file to a local variable 
    clob_data = Input$(LOF(LogFileNum), LogFileNum) 
    Close #LogFileNum 
  
    ' connect as adldemo/adldemo 
    con.CursorLocation = adUseServer 
    con.Open "Provider=OraOLEDB.Oracle;Data Source=db9i;" & _ 
             "User Id=adldemo;Password=adldemo;" 
  
    ' open a recordset 
    sql = "select clip_id, story from MULTIMEDIA_TAB" 
    rst.Open sql, con, adOpenStatic, adLockOptimistic, adCmdText 
  
    ' add a new record 
    rst.AddNew 
    rst!clip_id = 1234 
    rst!story.AppendChunk (clob_data) 
    rst.Update 
  
    ' fetch entire CLOB data 
    Do While (Not (done)) 
      vardata = rst!story.GetChunk(4096) 
      If Not (IsNull(vardata)) Then 
        Data = Data & vardata 
      Else 
        done = True 
      End If 
    Loop 
  

  
  
  
    ' validate fetched data 
    If Data = clob_data And Len(clob_data) = rst!story.ActualSize Then 
      MsgBox "The CLOB data (of " & Len(clob_data) & " bytes) " & _ 
             "was inserted and retrieved properly!" 
    End If 
  
    ' cleanup 
    con.Execute "delete from multimedia_tab where clip_id = 1234" 
    rst.Close 
    con.Close 
  
    Exit Sub 
  ErrorHandler: 
    MsgBox "Error: " & Err.Description 
  End Sub