Example: Dynasets Containing LOBs and Transactions

Schema Description

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