Enabling LOB Buffering

Figure 10-40 Use Case Diagram: Enabling LOB Buffering

Text description of the illustration adl10ps6.gif

See: "Use Case Model: Internal Persistent LOBs Operations", for all Internal Persistent LOB operations.

Purpose

This procedure describes how to enable LOB buffering.

Usage Notes

Enable buffering when performing a small read or write of data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.

Note: You must flush the buffer in order to make your modifications persistent. Do not enable buffering for the stream read and write involved in checkin and checkout.

For more information, refer to "LOB Buffering Subsystem" in Chapter 5, "Large Objects: Advanced Topics".

Syntax

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

• PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this use case.

• C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions, OCIEnableLobBuffering, OCIDisableLobBuffering, OCIFlushBuffer

• C++ (OCCI): Oracle C++ Interface Programmer's Guide

• COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" -- LOB ENABLE BUFFERING

• C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" -- LOB ENABLE BUFFERING

• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > EnableBuffering

• Java (JDBC): There is no applicable syntax reference for this use case.

Scenario

This scenario is part of the management of a buffering example related to Sound that is developed in this and related methods.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): No example is provided with this release.

• C (OCI): Enabling LOB Buffering

• C++ (OCCI): No example is provided with this release.

• COBOL (Pro*COBOL): Enabling LOB Buffering

• C/C++ (Pro*C/C++): Enabling LOB Buffering

• Visual Basic (OO4O): Enabling LOB Buffering

• Java (JDBC): No example is provided with this release.

C (OCI): Enabling LOB Buffering

See: "Disabling LOB Buffering"

COBOL (Pro*COBOL): Enabling LOB Buffering

This script is provided at $ORACLE_HOME/rdbms/demo/lobs/cobol/ibuffer

       IDENTIFICATION DIVISION.
       PROGRAM-ID. LOB-BUFFERING.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
       01  BLOB1          SQL-BLOB.
       01  BUFFER         PIC X(10).
       01  AMT            PIC S9(9) COMP.
           EXEC SQL VAR BUFFER IS RAW(10) END-EXEC.
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       LOB-BUFFERING.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.
 
      * Allocate and initialize the BLOB locator: 
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL 
                SELECT SOUND INTO :BLOB1
                FROM MULTIMEDIA_TAB 
                WHERE CLIP_ID = 1 FOR UPDATE END-EXEC.
 
      * Open the BLOB and enable buffering: 
           EXEC SQL LOB OPEN :BLOB1 READ WRITE END-EXEC.
           EXEC SQL 
                LOB ENABLE BUFFERING :BLOB1 END-EXEC.
        
      * Write some data to the BLOB: 
           MOVE "242424" TO BUFFER.
           MOVE 3 TO AMT.
           EXEC SQL 
                LOB WRITE :AMT FROM :BUFFER INTO :BLOB1 END-EXEC.

           MOVE "212121" TO BUFFER.
           MOVE 3 TO AMT.
           EXEC SQL 
                LOB WRITE :AMT FROM :BUFFER INTO :BLOB1 END-EXEC.

      * Now flush the buffered writes: 
           EXEC SQL LOB FLUSH BUFFER :BLOB1 END-EXEC.
           EXEC SQL LOB DISABLE BUFFERING :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++): Enabling LOB Buffering

You can find this script at $ORACLE_HOME/rdbms/demo/lobs/proc/ibuffer

#include <oci.h>
#include <stdio.h>
#include <string.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 256

void enableBufferingLOB_proc()
{
  OCIBlobLocator *Lob_loc;
  int Amount = BufferLength;
  int multiple, Position = 1;
  /* Datatype equivalencing is mandatory for this datatype: */
  char Buffer[BufferLength];
  EXEC SQL VAR Buffer is RAW(BufferLength);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate and Initialize the LOB: */
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT Sound INTO :Lob_loc
             FROM Multimedia_tab WHERE Clip_ID = 1 FOR UPDATE;

  /* Enable use of the LOB Buffering Subsystem: */
  EXEC SQL LOB ENABLE BUFFERING :Lob_loc;
  memset((void *)Buffer, 0, BufferLength);
  for (multiple = 0; multiple < 8; multiple++)
    {
      /* Write data to the LOB: */
      EXEC SQL LOB WRITE ONE :Amount
                    FROM :Buffer INTO :Lob_loc AT :Position;
      Position += BufferLength;
    }
  /* Flush the contents of the buffers and Free their resources: */
  EXEC SQL LOB FLUSH BUFFER :Lob_loc FREE;
  /* Turn off use of the LOB Buffering Subsystem: */
  EXEC SQL LOB DISABLE BUFFERING :Lob_loc;
  /* Release resources held by the Locator: */
  EXEC SQL FREE :Lob_loc;
}

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

}

Visual Basic (OO4O): Enabling LOB Buffering

You can find this script at $ORACLE_HOME/rdbms/demo/lobs/vbasic/iflush

Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraDyn As OraDynaset, OraSound1 As OraBlob, OraSoundClone As OraBlob

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "samp/samp", 0&)
Set OraDyn = OraDb.CreateDynaset(
   "SELECT * FROM Multimedia_tab ORDER BY clip_id", ORADYN_DEFAULT)
Set OraSound1 = OraDyn.Fields("Sound").Value
'Enable buffering: 
OraSound1.EnableBuffering