See If the BFILE is Open with FILEISOPEN

Figure 11-16 Use Case Diagram: See If BFILE is Open Using FILEISOPEN

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

Purpose

This procedure describes how to see if a BFILE is OPEN with FILEISOPEN.

Usage Notes

While you can continue to use the older FILEISOPEN form, we strongly recommend that you switch to using ISOPEN, because this facilitates future extensibility.

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:

• PL/SQL(DBMS_LOB) (Oracle8i Supplied PL/SQL Packages Reference): Chapter 18, "DBMS_LOB" -- FILEISOPEN

• C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 15, "Relational Functions" -- LOB Functions, OCILobFileIsOpen

• COBOL (Pro*COBOL): A syntax reference is not applicable in this release.

• C/C++ (Pro*C/C++): A syntax reference is not applicable in this release.

• Visual Basic (OO4O): A syntax reference is not applicable in this release.

• 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

These examples query whether a BFILE associated with Music is open. Examples are provided in the following four programmatic environments:

Examples

• PL/SQL (DBMS_LOB Package): See If the BFILE is Open with FILEISOPEN

• C (OCI): See If the BFILE is Open with FILEISOPEN

• C/C++ (Pro*C/C++): No example is provided with this release.

• COBOL (Pro*COBOL): No example is provided with this release.

• Visual Basic (OO4O): No example is provided. See note

• Java (JDBC): See If the BFILE is Open with FILEISOPEN

PL/SQL (DBMS_LOB Package): See If the BFILE is Open with FILEISOPEN

/* Note that the example procedure seeIfOpenBFILE_procOne is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE seeIfOpenBFILE_procOne IS
   Lob_loc      BFILE;
   RetVal       INTEGER;
BEGIN
   /* Select the LOB, initializing the BFILE locator: */
   SELECT Music INTO Lob_loc FROM Multimedia_tab
      WHERE Clip_ID = 3;
   RetVal := DBMS_LOB.FILEISOPEN(Lob_loc);
   IF (RetVal = 1)
      THEN
      DBMS_OUTPUT.PUT_LINE('File is open');
   ELSE
      DBMS_OUTPUT.PUT_LINE('File is not open');
   END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;

C (OCI): See If the BFILE is Open with FILEISOPEN

/* Select the lob/bfile from the Multimedia table */ 
void selectLob(Lob_loc, errhp, svchp, stmthp) 
OCILobLocator *Lob_loc; 
OCIError      *errhp; 
OCISvcCtx     *svchp; 
OCIStmt       *stmthp; 
{ 
     OCIDefine *dfnhp;
     text *selstmt =
             (text *) "SELECT Music FROM Multimedia_tab WHERE Clip_ID=3";

     /* Prepare the SQL select statement */ 
     checkerr (errhp, OCIStmtPrepare(stmthp, errhp, selstmt,  
                                     (ub4) strlen((char *) selstmt), 
                                     (ub4) OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT)); 
 
     /* Call define for the bfile column */ 
     checkerr (errhp, OCIDefineByPos(stmthp, &dfnhp, errhp, 1,  
                                     (dvoid *)&Lob_loc, 0 , SQLT_BFILE,  
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0, 
                                     OCI_DEFAULT)); 
 
     /* Execute the SQL select statement */ 
     checkerr (errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                                     (CONST OCISnapshot*) 0, (OCISnapshot*) 0, 
                                     (ub4) OCI_DEFAULT)); 
} 
void BfileFileIsOpen(envhp, errhp, svchp, stmthp) 
OCIEnv  *envhp; 
OCIError *errhp; 
OCISvcCtx *svchp; 
OCIStmt *stmthp; 
{ 

     OCILobLocator *bfile_loc; 
     boolean flag;
 
     /* Allocate the locator descriptor */ 
     (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc,  
                 (ub4) OCI_DTYPE_FILE,  
                 (size_t) 0, (dvoid **) 0);
 
     /* Select the bfile */ 
     selectLob(bfile_loc, errhp, svchp, stmthp); 

     checkerr(errhp, OCILobFileOpen(svchp, errhp, bfile_loc, 
                                         (ub1)OCI_FILE_READONLY));

     checkerr(errhp, OCILobFileIsOpen(svchp, errhp, bfile_loc, &flag));

     if (flag == TRUE)
     {
       printf("File is open\n");
     }
     else
     {
       printf("File is not open\n");
     }

     checkerr(errhp, OCILobFileClose(svchp, errhp, bfile_loc));

     /* Free the locator descriptor */ 
     OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); 
} 

Visual Basic (OO4O): See If the BFILE is Open with FILEISOPEN

Note: At the present time, OO4O only offers ISOPEN to test whether or not a BFILE is open (see "Visual Basic (OO4O): See If the BFILE is Open with FILEISOPEN").

Java (JDBC): See If the BFILE is Open with FILEISOPEN

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_41
{
  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;

       rset = stmt.executeQuery (
       "SELECT BFILENAME('PHOTO_DIR', 'Lincoln_photo') FROM DUAL");
       if (rset.next())
       {
          src_lob = ((OracleResultSet)rset).getBFILE (1);

          OracleCallableStatement cstmt = (OracleCallableStatement)
          conn.prepareCall ("begin dbms_lob.open (?,dbms_lob.lob_readonly); 
end;");
          cstmt.registerOutParameter(1,OracleTypes.BFILE);
          cstmt.setBFILE (1, src_lob);
          cstmt.execute();
          src_lob = cstmt.getBFILE(1);
          System.out.println ("the file is now open");
       }

       // Close the BFILE,  statement and connection: 
       src_lob.closeFile();
       stmt.close();
       conn.commit();
       conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}