External LOBs (BFILEs), 13 of 41

INSERT Row With BFILE by Initializing BFILE Locator

Figure 11-9 Use Case Diagram: INSERT Row by Initializing BFILE Locator

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

Purpose

This procedure describes how to INSERT a row containing a BFILE by initializing a BFILE locator.

Usage Notes

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

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" -- INSERT: PL/SQL Oracle8i Supplied PL/SQL Packages Reference ....... BFILENAME()
C (OCI) Oracle Call Interface Programmer's Guide: Chapter 7, "LOB and File Operations". Chapter 15, "Relational Functions" -- LOB Functions.
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". See (Oracle8i SQL Reference), Chapter 7 "SQL Statements" -- INSERT
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" -- LOB FILE SET. See also (Oracle8i SQL Reference), Chapter 7 "SQL Statements" -- INSERT
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > DirectoryName, FileName; and > OBJECTS > OraDynaset >METHODS > Update
Java (JDBC) Oracle8i JDBC Developer's Guide and Reference: Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Further extensions are available in Oracle8i SQLJ Developer's Guide and Reference: Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

In these examples we insert a Photo from an operating system source file (PHOTO_DIR).

Examples

Examples in the following programmatic environments are provided:

PL/SQL: Insert a Row Containing a BFILE by Initializing a BFILE Locator

DECLARE
  /* Initialize the BFILE locator: */ 
  Lob_loc  BFILE := BFILENAME('PHOTO_DIR', 'Washington_photo');
BEGIN
    INSERT INTO Multimedia_tab (Clip_ID, Photo) VALUES (3, Lob_loc);
    COMMIT;
END;

C (OCI: Insert a Row Containing a BFILE by Initializing a BFILE Locator

/* Insert a row using BFILE Locator: */
void insertUsingBfileLocator(envhp, svchp, stmthp, errhp)
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIError *errhp;
{
  text  *insstmt = 
     (text *) "INSERT INTO Multimedia_tab (Clip_ID, Photo) \
         VALUES (3, :Lob_loc)";
  OCIBind *bndhp;
  OCILobLocator *Lob_loc;
  OraText *Dir = (OraText *)"PHOTO_DIR", *Name = (OraText *)"Washington_photo";
   
  /* Prepare the SQL statement: */
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, insstmt,  (ub4) 
                                  strlen((char *) insstmt),
                                  (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)Insert a Row Containing a BFILE by Initializing a BFILE Locator

       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-INSERT-INIT.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
    
       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  TEMP-BLOB      SQL-BLOB.
       01  SRC-BFILE      SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  DIR-IND        PIC S9(4) COMP.
       01  FNAME-IND      PIC S9(4) COMP.
       01  AMT            PIC S9(9) COMP.
       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-INSERT-INIT.
           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 :SRC-BFILE END-EXEC.

      * Set up the directory and file information:
           MOVE "PHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "washington_photo" TO FNAME-ARR.
           MOVE 16 TO FNAME-LEN.
 
      * Set the directory alias and filename in locator:
           EXEC SQL
              LOB FILE SET :SRC-BFILE DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME END-EXEC.
           
           EXEC SQL
                INSERT INTO MULTIMEDIA_TAB (CLIP_ID, PHOTO)
                VALUES (6, :SRC-BFILE)END-EXEC.
           EXEC SQL ROLLBACK WORK END-EXEC.
           EXEC SQL FREE :SRC-BFILE 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++): Insert a Row Containing a BFILE by Initializing a BFILE Locator

#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 insertBFILELocator_proc()
{
  OCIBFileLocator *Lob_loc;
  char *Dir = "PHOTO_DIR", *Name = "Washington_photo";
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  /* Allocate the input Locator: */
  EXEC SQL ALLOCATE :Lob_loc;
  /* Set the Directory and Filename in the Allocated (Initialized) Locator: */
  EXEC SQL LOB FILE SET :Lob_loc DIRECTORY = :Dir, FILENAME = :Name;
  EXEC SQL INSERT INTO Multimedia_tab (Clip_ID, Photo) VALUES (4, :Lob_loc);
  /* Release resources held by the Locator: */
  EXEC SQL FREE :Lob_loc;
}

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

}

Visual Basic (OO4O): Insert a Row Containing a BFILE by Initializing a BFILE Locator

Dim OraDyn as OraDynaset, OraPhoto as OraBFile, OraMusic as OraBFile 
Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT) 
Set OraMusic = OraDyn.Fields("Music").Value
Set OraPhoto = OraDyn.Fields("Photo").Value
 
'Edit the first row and initiliaze the "Photo" column: 
OraDyn.Edit 
OraPhoto.DirectoryName = "PHOTO_DIR" 
OraPhoto.Filename = "Washington_photo" 
OraDyn.Update

Java (JDBC): Insert a Row Containing a BFILE by Initializing a BFILE Locator

// Java IO classes: 
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_26
{
  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;
       OracleCallableStatement cstmt = null;
       rset = stmt.executeQuery (
          "SELECT BFILENAME('PHOTO_DIR', 'Washington_photo') FROM DUAL");
       if (rset.next())
       {
          src_lob = ((OracleResultSet)rset).getBFILE (1);
       }

       // Prepare a CallableStatement to OPEN the LOB for READWRITE: 
       cstmt = (OracleCallableStatement) conn.prepareCall (
          "INSERT INTO   multimedia_tab (clip_id, photo) VALUES (3, ?)");
       cstmt.setBFILE(1, src_lob);
       cstmt.execute();

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