Write (OraLOB) Method

Description

Writes a buffer into the BLOB or CLOB value of this object and returns the total amount of the data written.

Usage

amount_written = OraBlob.Write buffer, chunksize, piece
amount_written = OraClob.Write buffer, chunksize, piece
 

Arguments

The arguments for the method are:

Arguments Description
in] buffer The character array for an OraCLOB object or byte array for the OraBLOB object from which the piece is written.
[in] [optional] chunksize An Integer specifying the length of the buffer, in characters for an OraCLOB object and bytes for an OraBLOB or OraBFILE object. Default value is the size of the buffer argument.
[in] [optional] piece An Integer specifying which piece of the buffer is being written. Possible values include:
  • ORALOB_ONE_PIECE - Buffer is written in a single piece. This is the default.

  • ORALOB_FIRST_PIECE - Buffer represents the piece of LOB data to be written.

  • ORALOB_NEXT_PIECE - Buffer represents the next piece of LOB data to be written.

  • ORALOB_LAST_PIECE - Buffer represents the last piece of LOB data to be written.

[out] amount_written An Integer representing the amount written, in characters for an OraCLOB object and bytes for an OraBLOB or OraBFILE object.

Remarks

Obtain either a row-level lock or object-level lock before calling the Write method. This method writes the BLOB or CLOB data from the offset specified by the Offset property. For a multiple-piece write operation, the PollingAmount property can be set to the value of the total amount of data to be written, and the Status property must be checked for the success of each piece operation. If the total amount is not known, then the PollingAmount property can be set to 0 and polling still occurs as long as the piece type is not OraLob_piece.

For the last piece, set the piece argument to ORALOB_LAST_PIECE. You must write the polling amount in bytes or characters. It is not possible to terminate the Write operation early if the PollingAmount property is not zero.

When the OraLOB Pollingamount = 0 but the piece type on OraLOB Write is not ORALOB_ONE_PIECE, polling still occurs. Polling completes when ORALOB_LAST_PIECE is sent as an argument to a call to the Write method. This is useful when calling the OraCLOB.Write method in a variable-width character set, when counting the total amount of characters ahead of time may be costly.

Note:

When manipulating LOBs using LOB methods, such as the Write and CopyFromFile, the LOB object is not automatically trimmed if the length of the new data is shorter than the old data. Use the Trim (OraLOB) method to shrink the LOB object to the size of the new data.

Examples

Be sure that you have installed the OraLOB Schema Objects as described in "Schema Objects Used in LOB Data Type Examples" .

Multiple-Piece Write of a LOB Example

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim OraDynaset As OraDynaset 
Dim PartDesc As OraClob 
Dim buffer As String 
Dim chunksize As Long 
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 the OraDynaset Object 
Set OraDynaset = OraDatabase.CreateDynaset("select * from part", 0&) 
Set PartDesc = OraDynaset.Fields("part_desc").Value 
chunksize = 32000 
 
'Re adjust the buffer size 
buffer = String$(chunksize, 32) 
FNum = FreeFile 
 
'Open the file. 
Open "partdesc.dat" For Binary As #FNum 
 
'set the offset and PollingAmount properties for piece wise
'Write operation 
PartDesc.offset = 1 
PartDesc.PollingAmount = LOF(FNum) 
remainder = LOF(FNum) 
 
'Lock the row for write operation 
OraDynaset.Edit 
Get #FNum, , buffer 
 
'Do first write operation 
amount_written = PartDesc.Write(buffer, chunksize, ORALOB_FIRST_PIECE)  
 
While PartDesc.Status = ORALOB_NEED_DATA 
  remainder = remainder - chunksize 
  If remainder < chunksize Then 
    piecetype = ORALOB_LAST_PIECE 
    chunksize = remainder 
   Else 
    piecetype = ORALOB_NEXT_PIECE 
  End If 
  Get #FNum, , buffer 
 
  amount_written = PartDesc.Write(buffer, chunksize, piecetype) 
Wend 
 
Close FNum 
 
'call Update method to commit the transaction 
OraDynaset.Update 

Single-Piece Write of a LOB Example

Dim OraSession As OraSession 
Dim OraDatabase As OraDatabase 
Dim PartImage As OraBlob 
Dim buffer() As Byte 
 
'Create the OraSession Object. 
Set OraSession = CreateObject("OracleInProcServer.XOraSession") 
 
'Create the OraDatabase Object. 
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)
 
'Add PartDesc as an Output parameter and set its initial value. 
OraDatabase.Parameters.Add "PartImage", Null, ORAPARM_OUTPUT 
OraDatabase.Parameters("PartImage").ServerType = ORATYPE_BLOB 
 
'Begin the transaction 
OraSession.BeginTrans 
 
'Execute the statement returning 'PartDesc' 
OraDatabase.ExecuteSQL ("BEGIN select part_image into :PARTIMAGE" & _ 
            "from part where part_id = 1 for update NOWAIT; END;") 
 
'Get 'PartDesc' from Parameters collection 
Set PartImage = OraDatabase.Parameters("PartImage").Value 
 
'Get a free file number 
FNum = FreeFile 
 
'Open the file. 
Open "PartImage.Dat" For Binary As #FNum 
 
'Re adjust the buffer size to hold entire file data 
ReDim buffer(LOF(FNum)) 
Get #FNum, , buffer 
 
'Do one write operation 
amount_written = PartImage.Write(buffer) 
 
Close FNum 
MsgBox "Amount written to the LOB data is " & amount_written 
 
'Ends the transaction 
OraSession.CommitTrans