Internal Persistent LOBs, 41 of 42

UPDATE by Initializing a LOB Locator Bind Variable

Figure 9-44 Use Case Diagram: UPDATE by Initializing a LOB Locator Bind Variable

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

Purpose

This procedure describes how to UPDATE by initializing a LOB locator bind variable.

Usage Notes

Not applicable.

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:

SQL: Oracle8i SQL Reference , Chapter 7, "SQL Statements" -- UPDATE
C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions.
COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" -- ALLOCATE
C/C++ (Pro*C/C++): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives".
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Oradatabase > METHODS > ExecuteSQL
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 update Sound data by means of a locator bind variable.

Examples

Examples are provided in the following programmatic environments:

SQL: Update by Initializing a LOB Locator Bind Variable

/* Note that the example procedure updateUseBindVariable_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE updateUseBindVariable_proc (Lob_loc BLOB) IS
BEGIN
   UPDATE Multimedia_tab SET Sound = lob_loc WHERE Clip_ID = 2;
END;

DECLARE
   Lob_loc     BLOB;
BEGIN
   /* Select the LOB: */
   SELECT Sound INTO Lob_loc
      FROM Multimedia_tab
         WHERE Clip_ID = 1;
   updateUseBindVariable_proc (Lob_loc);
   COMMIT;
END;

C (OCI): Update by Initializing a LOB Locator Bind Variable

/* Select the locator into a locator variable: */
sb4 select_sound_locator(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text  *sqlstmt = 
     (text *)"SELECT Sound FROM Multimedia_tab WHERE Clip_ID=2";
  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;
}

/* Update the LOB  in the selected row in the table: */
void updateLobUsingBind (envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  text  *updstmt = 
    (text *) "UPDATE Multimedia_tab SET Sound = :1 WHERE Clip_ID = 1";
  OCILobLocator *Lob_loc;
  OCIBind       *bndhp1;

  /* Allocate locator resources: */
  (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&Lob_loc,
                            (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0);

  /* Select the locator: */
  printf(" select a sound locator\n");
  (void)select_sound_locator(Lob_loc, errhp, svchp, stmthp);
  
  /* Prepare the SQL statement: */
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, updstmt, (ub4) 
                                  strlen((char *) updstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT));

  /* Binds the bind positions: */
  printf(" bind locator to bind position\n");

  checkerr (errhp, OCIBindByPos(stmthp, &bndhp1, errhp, (ub4) 1,
                                (dvoid *) &Lob_loc, (sb4)0, SQLT_BLOB,
                                (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                                (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));

  /* Execute the SQL statement: */
  printf ("update LOB column in another row using this locator\n"); 
  checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                  (ub4) OCI_DEFAULT));
  
  return;
}

COBOL (Pro*COBOL): Update by Initializing a LOB Locator Bind Variable

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

       01  BLOB1          SQL-BLOB.
       01  NEW-LEN        PIC S9(9) COMP.
       01  AMT            PIC S9(9) COMP.
       01  OFFSET         PIC S9(9) COMP.

      * Define the source and destination position and location: 
       01  SRC-POS        PIC S9(9) COMP.
       01  DEST-POS       PIC S9(9) COMP.
       01  SRC-LOC        PIC S9(9) COMP.
       01  DEST-LOC       PIC S9(9) COMP.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       UPDATE-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 SOUND INTO :BLOB1
                FROM MULTIMEDIA_TAB 
                WHERE CLIP_ID = 1
           END-EXEC.
 
           EXEC SQL 
                UPDATE MULTIMEDIA_TAB
                SET SOUND = :BLOB1 WHERE CLIP_ID = 2
           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++): Update by Initializing a LOB Locator Bind Variable

#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);
}

void updateUseBindVariable_proc(Lob_loc)
  OCIBlobLocator *Lob_loc;
{
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL UPDATE Multimedia_tab SET Sound = :Lob_loc WHERE Clip_ID = 2;
}

void updateLOB_proc()
{
  OCIBlobLocator *Lob_loc;

  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT Sound INTO :Lob_loc
           FROM Multimedia_tab WHERE Clip_ID = 1;
  updateUseBindVariable_proc(Lob_loc);
  EXEC SQL FREE :Lob_loc;
  EXEC SQL COMMIT WORK;
}

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

Visual Basic (OO4O): Update by Initializing a LOB Locator Bind Variable

Dim OraDyn As OraDynaset, OraSound as OraBlob
 
'Select a column with clip_id  = 1: 
Set OraDyn = OraDb.CreateDynaset("SELECT * FROM Multimedia_tab WHERE 
    clip_id = 1", ORADYN_DEFAULT) 
 
'Get the OraBlob object from the field: 
Set OraSound = OraDyn.Fields("Sound").Value 
 
'Create a parameter for OraBlob object: 
OraDb.Parameters.Add "SOUND",Null,ORAPARM_INPUT,ORATYPE_BLOB 
 
'Set the value of SOUND parameter to OraSound: 
OraDb.Parameters("SOUND").Value = OraSound 
 
'Update the Multimedia_tab with OraSound for clip_id = 2: 
OraDb.ExecuteSQL("Update  Multimedia_tab SET Sound = :SOUND 
   WHERE Clip_id = 2")

Java (JDBC): Update by Initializing a LOB Locator Bind Variable

import java.sql.Connection;
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_163
{
 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
    {
     ResultSet rset = stmt.executeQuery (
        "SELECT sound FROM multimedia_tab WHERE clip_id = 1");
      if (rset.next())
      {
       // retrieve the LOB locator from the ResultSet: 
       BLOB sound_blob = ((OracleResultSet)rset).getBLOB (1);

        OraclePreparedStatement ops = 
          (OraclePreparedStatement) conn.prepareStatement(
             "UPDATE multimedia_tab SET SOUND = ? WHERE clip_id = 2");
          ops.setBlob(1, sound_blob);
          ops.execute();
          rset.close();
          stmt.close();
     conn.commit();
     conn.close();
   }
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}