Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 1 (9.0.1) Part Number A88879-01 |
|
External LOBs (BFILEs), 36 of 41
See Also:
"Use Case Model: External LOBs (BFILEs)" for all basic operations of External LOBs (BFILES). |
This procedure describes how to UPDATE a BFILE by initializing a BFILE locator.
You must initialize the BFILE
locator bind variable to a directory alias and filename before issuing the update statement.
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:
Not applicable.
The examples are provided in six programmatic environments:
/* 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;
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); }
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.
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;}
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
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(); } } }
|
![]() Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|