External LOBs (BFILEs), 36 of 41

Updating a BFILE by Initializing a BFILE Locator

Figure 12-32 Use Case Diagram: Updating a BFILE by Initializing a BFILE Locator

Text description of adl12b33.gif follows

Text description of the illustration adl12b33.gif

See Also:

"Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES).

Purpose

This procedure describes how to UPDATE a BFILE by initializing a BFILE locator.

Usage Notes

You must initialize the BFILE locator bind variable to a directory alias and filename before issuing the update statement.

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): See the (Oracle9i SQL Reference), Chapter 7, "SQL Statements" -- UPDATE
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 15, "Relational Functions" -- LOB Functions, OCILobFileSetName
COBOL (Pro*COBOL) (Pro*COBOL Precompiler Programmer's Guide): Chapter 13, "Large Objects (LOBs)", "LOB Statements" for usage notes. Appendix F, "Embedded SQL and Precompiler Directives" -- ALLOCATE. See also (Oracle9i SQL Reference), Chapter 7, "SQL Statements" -- UPDATE
C/C++ (Pro*C/C++) (Pro*C/C++ Precompiler Programmer's Guide): Chapter 16, "Large Objects (LOBs)", "LOB Statements", usage notes. Appendix F, "Embedded SQL Statements and Directives". See also (Oracle9i SQL Reference), Chapter 7, "SQL Statements" -- UPDATE
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > PROPERTIES > DirectoryFileName, FileName, and > OBJECTS > OraDatabase > METHODS > ExecuteSQL. See also OBJECTS > OraBfile, OraDatabase > Examples
Java (JDBC) Oracle9i JDBC Developer's Guide and Reference: Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Further extensions are available in ( Oracle9i SQLJ Developer's Guide and Reference): Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

Not applicable.

Examples

The examples are provided in six programmatic environments:

PL/SQL: Updating a BFILE by Initializing a BFILE Locator

/* Note that the example procedure updateUseBindVariable_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE updateUseBindVariable_proc (File_loc BFILE) IS
BEGIN
   UPDATE Multimedia_tab SET Photo = File_loc WHERE Clip_ID = 3;
END;

DECLARE
   File_loc  BFILE;
BEGIN
   SELECT Photo INTO File_loc
      FROM Multimedia_tab
         WHERE Clip_ID = 1;
   updateUseBindVariable_proc (File_loc);
   COMMIT;
END;

C (OCI): Updating a BFILE by Initializing a BFILE Locator

void BfileUpdate(envhp, errhp, svchp, stmthp) 
OCIEnv    *envhp; 
OCIError  *errhp; 
OCISvcCtx *svchp; 
OCIStmt   *stmthp; 
{ 
  OCILobLocator *Lob_loc; 
  OCIBind *bndhp; 

  text  *updstmt =  
     (text *) "UPDATE Multimedia_tab SET Photo = :Lob_loc WHERE Clip_ID = 1"; 

  OraText *Dir = (OraText *)"PHOTO_DIR", *Name = (OraText *)"Washington_photo"; 
 
  /* Prepare the SQL statement: */ 
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, updstmt,  (ub4)  
                                  strlen((char *) updstmt), 
                                  (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); 
 
  /* Allocate Locator resources: */ 
  (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &Lob_loc,  
                            (ub4)OCI_DTYPE_FILE, (size_t) 0, (dvoid **) 0); 
 
  checkerr (errhp, OCILobFileSetName(envhp, errhp, &Lob_loc, 
                                     Dir, (ub2)strlen((char *)Dir), 
                                     Name,(ub2)strlen((char *)Name))); 
 
  checkerr (errhp, OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1, 
                                (dvoid *) &Lob_loc, (sb4) 0,  SQLT_BFILE, 
                                (dvoid *) 0, (ub2 *)0, (ub2 *)0, 
                                (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)); 
 
 /* Execute the SQL statement: */ 
  checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                                  (CONST OCISnapshot*) 0, (OCISnapshot*) 0,   
                                  (ub4) OCI_DEFAULT)); 
  
 /* Free LOB resources: */ 
  OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_FILE); 
}

COBOL (Pro*COBOL): Updating a BFILE by Initializing a BFILE Locator

This script is also provided in:

$ORACLE_HOME/rdbms/demo/lobs/cobol/fupdate

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

       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1         SQL-BFILE.
       01  BFILE-IND      PIC S9(4) COMP.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(30) VARYING.
       01  ORASLNRD       PIC 9(4).

           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC ORACLE OPTION (ORACA=YES) END-EXEC.
           EXEC SQL INCLUDE ORACA END-EXEC.
        
       PROCEDURE DIVISION.
       BFILE-UPDATE.

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

      * Allocate and initialize the BFILE locator:
           EXEC SQL ALLOCATE :BFILE1 END-EXEC.

      * Populate the BFILE: 
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC.
           EXEC ORACLE OPTION (SELECT_ERROR=NO) END-EXEC.
           EXEC SQL 
                SELECT PHOTO INTO :BFILE1:BFILE-IND
                FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 END-EXEC.

      * Make photo associated with clip_id=3 same as clip_id=1: 
           EXEC SQL 
                UPDATE MULTIMEDIA_TAB SET PHOTO = :BFILE1:BFILE-IND
                WHERE CLIP_ID = 3 END-EXEC.
        
      * Free the BFILE: 
       END-OF-BFILE.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BFILE1 END-EXEC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           MOVE ORASLNR TO ORASLNRD.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED ON LINE ", ORASLNRD, ":".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (Pro*C/C++): Updating a BFILE by Initializing a BFILE Locator

This script is also provided in:

$ORACLE_HOME/rdbms/demo/lobs/proc/fupdate

#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)
  OCIBFileLocator *Lob_loc;
{
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL UPDATE Multimedia_tab SET Photo = :Lob_loc WHERE Clip_ID = 3;
}

void updateBFILE_proc()
{
  OCIBFileLocator *Lob_loc;

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

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

}

Visual Basic (OO4O): Updating a BFILE by Initializing a BFILE Locator

Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraParameters As OraParameters, OraPhoto As OraBfile

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "scott/tiger", 0&)

OraDb.Connection.BeginTrans

Set OraParameters = OraDb.Parameters

'Define in out parameter of BFILE type: 
OraParameters.Add "MyPhoto", Null, ORAPARM_BOTH, ORATYPE_BFILE

'Define out parameter of BFILE type: 
OraDb.ExecuteSQL (
   "BEGIN SELECT Photo INTO :MyPhoto FROM Multimedia_tab WHERE Clip_ID = 1; 
      END;")
       
'Update the photo BFile for clip_id=1 to clip_id=1001: 
OraDb.ExecuteSQL (
   "UPDATE Multimedia_tab SET Photo = :MyPhoto WHERE Clip_ID = 1001")

'Get Directory alias and filename
'MsgBox " Directory alias is " & OraMusic1.DirectoryName & " Filename is " & 
OraMusic1.filename

OraDb.Connection.CommitTrans

Java (JDBC): Updating a BFILE by Initializing a BFILE Locator

import java.io.InputStream;
import java.io.OutputStream;

// Core JDBC classes: 
import java.sql.DriverManager;
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 Ex4_100
{

  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");

    conn.setAutoCommit (false);

    // Create a Statement: 
    Statement stmt = conn.createStatement ();

    try
    {
        BFILE src_lob = null;
        ResultSet rset = null;
        OraclePreparedStatement pstmt = null;

        rset = stmt.executeQuery (
           "SELECT photo FROM multimedia_tab WHERE clip_id = 3");
        if (rset.next())
        {
           src_lob = ((OracleResultSet)rset).getBFILE (1);
        }

        // Prepare a CallableStatement to OPEN the LOB for READWRITE: 
        pstmt = (OraclePreparedStatement) conn.prepareStatement (
            "UPDATE multimedia_tab SET photo = ? WHERE clip_id = 1");
        pstmt.setBFILE(1, src_lob);
        pstmt.execute();

       //Close the statements and commit the transaction: 
       stmt.close();
       pstmt.close();
       conn.commit();
       conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}