Release 1 (9.0.1)
Part Number A88879-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 1 (9.0.1) Part Number A88879-01 |
|
External LOBs (BFILEs), 13 of 41
|
See Also:
"Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES). |
This procedure describes how to INSERT a row containing a BFILE by initializing a BFILE locator.
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:
In these examples we insert a Photo from an operating system source file (PHOTO_DIR).
Examples in the following programmatic environments are provided:
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;
/* 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); }
This script is also provided in $ORACLE_HOME/rdbms/demo/lobs/cobol/ finsert
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.
This script is also provided in $ORACLE_HOME/rdbms/demo/lobs/proc/finsert
#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;}
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 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(); } } }
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|