Internal Persistent LOBs, 31 of 42

Write Append to a LOB

Figure 9-34 Use Case Diagram: Write Append to a LOB

See:
"Use Case Model: Internal Persistent LOBs Basic Operations", for all basic operations of Internal Persistent LOBs.

Purpose

This procedure describes how to WRITE APPEND to a LOB.

Usage Notes

Writing Singly or Piecewise

The writeappend operation writes a buffer to the end of a LOB.

For OCI, the buffer can be written to the LOB in a single piece with this call; alternatively, it can be rendered piecewise using callbacks or a standard polling method.

Writing Piecewise: When to Use Callbacks or Polling?

If the value of the piece parameter is OCI_FIRST_PIECE, data must be provided through callbacks or polling.

If a callback function is defined in the cbfp parameter, then this callback function will be invoked to get the next piece after a piece is written to the pipe. Each piece will be written from bufp.
If no callback function is defined, then OCILobWriteAppend() returns the OCI_NEED_DATA error code. The application must call OCILobWriteAppend() again to write more pieces of the LOB. In this mode, the buffer pointer and the length can be different in each call if the pieces are of different sizes and from different locations. A piece value of OCI_LAST_PIECE terminates the piecewise write.

Locking the Row Prior to Updating

Prior to updating a LOB value via the PL/SQL DBMS_LOB package or the OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of a SQL SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs.

For more details on the state of the locator after an update, refer to "Updated LObs Via Updated Locators" in Chapter 5, "Advanced Topics".

Syntax

See Chapter 3, "LOB Programmatic Environments" for a list of available functions in each programmatic environment. Use the following syntax references for each programmatic environment:

PL/SQL (DBM_LOB Package): Oracle8i Supplied PL/SQL Packages Reference Chapter 18, "DBMS_LOB" -- WRITEAPPEND
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCILobWriteAppend
COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Precompiler Directives" -- LOB WRITE APPEND
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB WRITE APPEND
Visual Basic (OO4O): No syntax reference is provided with this release.
Java (JDBC): Oracle8i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle8i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

These examples demonstrate writing to the end of a video frame (Frame).

Examples

Examples are provided in the following programmatic environments:

PL/SQL (DBMS_LOB Package): Write Append to a LOB
C (OCI): Write Append to a LOB
COBOL (Pro*COBOL): Write Append to a LOB
C/C++ (Pro*C/C++): Write Append to a LOB
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): Write Append to a LOB

PL/SQL (DBMS_LOB Package): Write Append to a LOB

/* Note that the example procedure lobWriteAppend_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE lobWriteAppend_proc IS
   Lob_loc    BLOB;
   Buffer     RAW(32767);
   Amount     Binary_integer := 32767;
BEGIN
   SELECT Frame INTO Lob_loc FROM Multimedia_tab where Clip_ID = 1 FOR UPDATE;
   /* Fill the buffer with data... */
   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc, DBMS_LOB.LOB_READWRITE);
   /* Append the data from the buffer to the end of the LOB: */
   DBMS_LOB.WRITEAPPEND(Lob_loc, Amount, Buffer);
   /* Closing the LOB is mandatory if you have opened it: */
   DBMS_LOB.CLOSE(Lob_loc);
END;

C (OCI): Write Append to a LOB

/* Select the locator into a locator variable: */
sb4 select_lock_frame_locator(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text  *sqlstmt = 
    (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=1 FOR UPDATE";
  OCIDefine *defnp1;
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                                  (ub4)strlen((char *)sqlstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
  checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1,
                                  (dvoid *)&Lob_loc, (sb4)0, 
                                  (ub2) SQLT_BLOB,(dvoid *) 0, 
                                  (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT));

  /* Execute the select and fetch one row: */
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                 (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                 (ub4) OCI_DEFAULT));
  
  return (0);
}

#define MAXBUFLEN 32767

void writeAppendLob(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCIBlobLocator *Lob_loc;
  ub4 amt;
  ub4 offset;
  sword retval;
  ub1 bufp[MAXBUFLEN];
  ub4 buflen;
 
  /* Allocate the locator: */
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc,
                         (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0);

  /* Select the BLOB: */
  printf(" select and lock a frame locator\n");
  select_lock_frame_locator(Lob_loc, errhp, svchp, stmthp);

  /* Open the BLOB: */
  checkerr (errhp, (OCILobOpen(svchp, errhp, Lob_loc, OCI_LOB_READWRITE)));

  /* Setting the amt to the buffer length.  Note here that amt is in bytes 
     since we are using a BLOB: */
  amt    = sizeof(bufp); 
  buflen = sizeof(bufp);

  /* Fill bufp with data: */
  /* Write the data from the buffer at the end of the LOB: */
  printf(" write-append data to the frame Lob\n");
  checkerr (errhp, OCILobWriteAppend (svchp, errhp, Lob_loc, &amt, 
                             bufp, buflen,
                             OCI_ONE_PIECE, (dvoid *)0,
                             (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *))0,
                             0, SQLCS_IMPLICIT));

  /* Closing the BLOB is mandatory if you have opened it: */
  checkerr (errhp, OCILobClose(svchp, errhp, Lob_loc));
  /* Free resources held by the locators: */
  (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB);

  return;
}

COBOL (Pro*COBOL): Write Append to a LOB

       IDENTIFICATION DIVISION.
       PROGRAM-ID. WRITE-APPEND-BLOB.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  BLOB1         SQL-BLOB.
       01  AMT           PIC S9(9) COMP.
       01  BUFFER        PIC X(32767) VARYING.
           EXEC SQL VAR BUFFER IS LONG RAW (32767) END-EXEC.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
           EXEC SQL INCLUDE SQLCA END-EXEC.
        
       PROCEDURE DIVISION.
       WRITE-APPEND-BLOB.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.

      * Allocate and initialize the BLOB locators: 
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL SELECT FRAME INTO :BLOB1
                FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 FOR UPDATE END-EXEC.
 
      * Open the target LOB: 
           EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC.

      *    Populate AMT here: 
           MOVE 5 TO AMT.
           MOVE "2424242424" to BUFFER.

      * Append the source LOB to the destination LOB: 
           EXEC SQL LOB WRITE APPEND :AMT FROM :BUFFER INTO :BLOB1 END-EXEC.
           EXEC SQL LOB CLOSE :BLOB1 END-EXEC.

       END-OF-BLOB.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BLOB1 END-EXEC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (Pro*C/C++): Write Append to a LOB

#include <oci.h>
#include <stdio.h>
#include <sqlca.h>

void Sample_Error()
{
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}

#define BufferLength 128

void LobWriteAppend_proc()
{
  OCIBlobLocator *Lob_loc;
  int Amount = BufferLength;
  /* Amount == BufferLength so only a single WRITE is needed: */
  char Buffer[BufferLength];
  /* Datatype equivalencing is mandatory for this datatype: */
  EXEC SQL VAR Buffer IS RAW(BufferLength);
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT Frame INTO :Lob_loc
           FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE;
  /* Opening the LOB is Optional: */
  EXEC SQL LOB OPEN :Lob_loc;
  memset((void *)Buffer, 1, BufferLength);  
  /* Write the data from the buffer at the end of the LOB: */
  EXEC SQL LOB WRITE APPEND :Amount FROM :Buffer INTO :Lob_loc;
  /* Closing the LOB is mandatory if it has been opened: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

void main()
{
  char *samp = "samp/samp";
  EXEC SQL CONNECT :samp;
  LobWriteAppend_proc();
  EXEC SQL ROLLBACK WORK RELEASE;
}

Java (JDBC): Write Append to a LOB

import java.io.OutputStream;

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Types;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

// Oracle Specific JDBC classes: 
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class Ex2_126
{
  static final int MAXBUFSIZE = 32767;
  public static void main (String args [])
       throws Exception
  {
    // Load the Oracle JDBC driver: 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database: 
    Connection conn =
    DriverManager.getConnection ("jdbc:oracle:oci8:@", "samp", "samp");

    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();
    try
    {
     BLOB dest_loc = null;
     byte[] buf = new byte[MAXBUFSIZE];
     long pos = 0;
     ResultSet rset = stmt.executeQuery (
         "SELECT frame FROM multimedia_tab WHERE clip_id = 1 FOR UPDATE");
   if (rset.next())
   {
     dest_loc = ((OracleResultSet)rset).getBLOB (1);
   }

   // Start writing at the end of the LOB.  ie. append: 
   pos = dest_loc.length();
   
   // fill buf with contents to be written: 
   buf = (new String("Hello World")).getBytes();

   // Write the contents of the buffer into position pos of the output LOB: 
   dest_loc.putBytes(pos, buf);

   // Close all streams and handles: 
   stmt.close();
   conn.commit();
   conn.close();

    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}