|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 2 (9.2)
Part Number A96591-01
This chapter contains the following sections:
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 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:
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.
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.
Although most LOB columns in an Oracle database support up to 4 GB of data storage, ADO limits the maximum column size to 2 GB.
To incur less round trips to the database, reads and writes should be carried out in large chunks for better performance.
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.
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.
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.
The following OLE DB rowset methods read and write LOB data:
In OraOLEDB, the following functionality is supported:
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.
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:
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