OraBLOB, OraCLOB Objects

Description

The OraBLOB and OraCLOB interfaces in OO4O provide methods for performing operations in a database on the large object data types BLOB, CLOB, and NCLOB. In this developer's guide, BLOB, CLOB, and NCLOB data types are also referred to as LOB data types.

OO4O supports the creation of temporary BLOB or CLOB types that can be manipulated and then bound to SQL statements or PL/SQL blocks, or copied into permanent LOBs.

Remarks

LOB data is accessed using the Read and CopyToFile methods.

LOB data is modified using the Write, Append, Erase, Trim, Copy, CopyFromFile, and CopyFromBFile methods. A row lock must be obtained before modifying the contents of a LOB column in a row. If the LOB column is a field of an OraDynaset object, then the lock is obtained by invoking the Edit method.

None of the LOB operations are allowed on NULL LOBs. To avoid errors, use the IsNull property to detect NULL LOBs. To perform write operations on a LOB that is null, first the LOB column must be initialized with an Empty value.

To insert a new row having a LOB column, first initialize the LOB column with an Empty value by setting the Value property of the OraField or OraParameter object to the keyword Empty and commit the change to the database. The newly updated Empty LOB must be selected again from the database before it can be used. This is done automatically in the case of the OraDynaset object: If a LOB field in an OraDynaset object is set to Empty and the Update method is called, OO4O automatically reselects the Empty LOB into the dynaset making it available for use in subsequent write operations.

There are two modes of operation for read and write operations for LOBs.

Multiple-piece read/write operations

In this mode, the total amount of data to be read or written is more than the size of the buffer for an individual read/write operation. Rather than make a complete round-trip for each operation, the pieces are streamed. To begin the multiple piece operation, the PollingAmount property is first set to the total amount of data to be read or written. The Offset property is set at this time to specify the initial offset for the first piece read/write operation. The offset is automatically incremented after the first read/write operation, and cannot be changed again until the multiple piece operation has completed. The Status property must be checked for the success of each piecewise operation and the operation must continue until all the pieces are read or written (it cannot be aborted). To start another multiple-piece read/write operation on the same LOB, the PollingAmount property has to be reset to the desired amount. See "Example: Multiple-Piece Read of a LOB".
Single-piece read/write operation

In this mode, the reading and writing of data occurs in one operation. This mode is enabled when the PollingAmount property is set to 0. See "Example: Single-Piece Read of a LOB".

The Offset property in both modes of operation is 1-based.

By design, LOBs cannot span transactions started by SELECT .. FOR UPDATE, INSERT, and UPDATE statements. Selecting or modifying LOB values using these SQL statements makes LOBs invalid outside the current transaction. In Oracle Objects for OLE, transactions can be started and ended in the following ways.

Dynaset Edit/Update method

The Edit method executes the SELECT FOR UPDATE statement to lock the row and start the transaction. The Update method ends the transaction. If the LOB column value is modifed between the Edit and Update pair, OO4O reselects the value of LOB column after the Update call. This is transparent to the user. Note that OO4O does not reselect the LOB value if the LOB is an attribute of an Oracle objects instance or element of an Oracle collection. If the transaction is started by the OraSession/OraDatabase or OraServer object and the LOB data is modified between the Edit and Update methods, OO4O does not reselect the LOB value from the database. LOBs are invalid after committing transactions initiated by OraSession/OraDatabase or OraServer objects.

See "Example: Dynasets Containing LOBs and Transactions".
Executing an INSERT or UPDATE statement through the ExecuteSQL or CreateSQL method.

An INSERT or UPDATE statement starts the transaction, and the transaction is implicitly ended by Oracle Objects for OLE (auto-commit). If a statement has a LOB output bind parameter, as in the case of the RETURNING .. INTO clause, then it will become invalid after the ExecuteSQL or CreateSQL method is executed To avoid this, the user must execute these statement between the BeginTrans/CommitTrans pair of OraSession, OraServer or OraDatabase objects.

See "Example: INSERT or UPDATE Statements with LOBs and Transactions".

See Also:

"Using Large Objects (LOBs)" for more information about LOB operations and LOB performance issues
Oracle Database SecureFiles and Large Objects Developer's Guide for a detailed description of Oracle LOBs

Properties

Methods

Examples

See "Schema Objects Used in LOB Data Type Examples" for schema objects that are used in the OraLOB and BFILE examples.

Example: Accessing a LOB Value

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraDynaset As OraDynaset 
Dim PartImage as OraBlob 
Dim buffer As Variant 
 
'Create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'Create the OraDatabase Object by opening a connection to Oracle. 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", _
                           "scott/tiger", 0&) 
 
'execute the select statement 
set OraDynaset = OraDatabase.CreateDynaset ("select * from part",0&) 
 
'retrieve photo field from the dynaset 
set PartImage = OraDynaset.Fields("part_image").Value 
 
'read the entire LOB column in one piece into the buffer 
amount_read = PartImage.Read(buffer, 10) 
'use the buffer for internal processing

Example: Modifying a LOB Value

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraDynaset As OraDynaset 
Dim  PartDesc as OraClob 
Dim buffer As String 
 
'Create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'Create the OraDatabase Object by opening a connection to Oracle. 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb","scott/tiger", 0&) 
 
'execute the select statement 
set OraDynaset = OraDatabase.CreateDynaset ("select * from part",0&) 
set PartDesc = OraDynaset.Fields("part_desc").Value 
 
'To get a free file number 
FNum = FreeFile 
'Open the file for reading 
Open "partdesc.dat" For Binary As #FNum 
 
'Allocate buffer to the size of file FNum and read the entire file 
buffer = String$(LOF(FNum), 32) 
Get #FNum, , buffer 
 
'lock the row for write operation 
OraDynaset.Edit 
amount_written = PartDesc.Write(buffer) 
 
'commit the operation and release the lock 
OraDynaset.Update 
Close FNum

Example: Inserting LOBs Using Dynasets

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim Part As OraDynaset 
Dim PartImage as OraBLOB 
Dim ImageChunk() As Byte 
Dim amount_written As Long 
 
'Create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'Create the OraDatabase Object by opening a connection to Oracle. 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
'Create a Dynaset containing a BLOB and a CLOB column 
 
set part = OraDatabase.CreateDynaset ("select * from part",0) 
set PartImage = part.Fields("part_image").Value 
 
'First insert Empty LOB in the part_image column 
part.AddNew 
    part.Fields("part_id").Value = 1234 
    part.Fields("part_image").Value = Empty 
part.Update 
 
'move to the newly added row 
Part.MoveLast 
 
'To get a free file number 
FNum = FreeFile 
 
'Open the file for reading PartImages 
Open "part_picture.gif" For Binary As #FNum 
 
'Re adjust the buffer size to hold entire file data 
 
Redim ImageChunk(LOF(FNum)) 
 
'read the entire file and put it into buffer 
Get #FNum, , ImageChunk 
 
'call dynaset's Edit method to lock the row 
part.Edit 
amount_written = OraBlob.Write(ImageChunk) 
part.Update 
 
'close the file
Close FNum

Example: Inserting LOBs Using an OraParameter Object

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraBlob As OraBlob 
Dim ImageChunk() As Byte 
Dim amount_written As Long 
 
'Create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'Create the OraDatabase Object by opening a connection to Oracle. 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
Set OraParameters = OraDatabase.Parameters 
OraParameters.Add "PartImage", Empty,ORAPARM_OUTPUT 
OraParameters("PartImage").ServerType = ORATYPE_BLOB 
 
'BeginTrans needs to be called since LOB locators become 
'invalid after the ExecuteSQL call 
OraSession.BeginTrans 
OraDatabase.ExecuteSQL ("insert into part values (1234,'Oracle  Application'," & _
                "EMPTY_BLOB(),NULL,NULL) RETURNING part_image INTO :PartImage") 
set PartImage = OraDatabase.Parameters("PARTIMAGE").Value 
 
FNum = FreeFile 
'Open the file for reading PartImages 
Open "part_picture.gif" For Binary As #FNum 
 
'read the file and put it into buffer 
Redim ImageChunk(LOF(FNum)) 
Get #FNum, , ImageChunk 
 
Set OraBlob = OraDatabase.Parameters("PartImage").Value 
amount_written = OraBlob.Write(ImageChunk, 10, ORALOB_ONE_PIECE) 
 
' commit the transaction and close the file
OraSession.CommitTrans 
Close FNum

Example: Dynasets Containing LOBs and Transactions

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraBlob As OraBlob 
Dim PartImage as OraBLOB 
Dim ImageChunk() As Byte 
Dim amount_written As Long 
 
'Create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'Create the OraDatabase Object by opening a connection to Oracle. 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb",  "scott/tiger", 0&) 
                                     
'Create a Dynaset containing a BLOB and a CLOB column 
set part = OraDatabase.CreateDynaset ("select * from part " & _
                              "where part_id = 1234",0) 
set PartImage = part.Fields("part_image").Value 
 
'To get a free file number 
FNum = FreeFile 
 
'Open the file for reading PartImages 
Open "c:\part_picture.gif" For Binary As #FNum 
Redim ImageChunk(LOF(FNum)) 
 
'read the file and put it into buffer 
Get #FNum, , ImageChunk 
 
'starts the transaction on OraSession 
OraSession.BeginTrans 
 
'call dynaset's Edit method to lock the row 
part.Edit 
Set OraBlob = PartImage 
amount_written = OraBlob.Write(ImageChunk, 10, ORALOB_ONE_PIECE) 
part.Update 
 
'ends the transaction 
OraSession.CommitTrans 
 
'the following lines of code will raise error  
'LOB locator cannot span transaction' 
msgbox Partimage.Size 
Close FNum

Example: INSERT or UPDATE Statements with LOBs and Transactions

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim ImageChunk() As Byte 
Dim amount_written As Long 
 
'Create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'Create the OraDatabase Object by opening a connection to Oracle. 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
Set OraParameters = OraDatabase.Parameters 
OraParameters.Add "PartImage", Empty,ORAPARM_OUTPUT 
OraParameters("PartImage").ServerType = ORATYPE_BLOB 
 
'Create a Dynaset containing a LOB,column 
OraDatabase.ExecuteSQL ("insert into part values (1234,'Oracle Application'," & _ 
                  "EMPTY_BLOB(),NULL,NULL) RETURNING part_image INTO :PartImage") 
set PartImage = OraDatabase.Parameters("PARTIMAGE").Value 
 
'the following lines of code will raise error 
'LOB locator cannot span transaction' 
msgbox Partimage.Size

Example: Using the CopyToFile Method

See "Example:Using the CopyToFile Method".

Example: Using the CopyFromFile Method

See "Example: Using the CopyFromFile Method".

Example: Multiple-Piece Read of a LOB

See "Example: Multiple-Piece Read of a LOB".

Example: Single-Piece Read of a LOB

See "Example: Single-Piece Read of a LOB".

Example: Multiple-Piece Write of a LOB

See "Multiple-Piece Write of a LOB Example".

Example: Single-Piece Write of a LOB

See "Single-Piece Write of a LOB Example".

Example: Passing a Temporary CLOB to a Stored Procedure

See "Example: Passing a Temporary CLOB to a Stored Procedure".