Checking If the BFILE is Open with FILEISOPEN

Figure 12-16 Use Case Diagram: Checking If BFILE is Open Using FILEISOPEN

Text description of the illustration adl12b10.gif

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 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) (Oracle9i 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) (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

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): Checking If the BFILE is Open with FILEISOPEN

• C (OCI): Checking 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): Checking If the BFILE is Open with FILEISOPEN

PL/SQL (DBMS_LOB Package): Checking 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
   File_loc      BFILE;
   RetVal       INTEGER;
BEGIN
   /* Select the LOB, initializing the BFILE locator: */
   SELECT Music INTO File_loc FROM Multimedia_tab
      WHERE Clip_ID = 3;
   RetVal := DBMS_LOB.FILEISOPEN(File_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): Checking 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): Checking 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): Checking If the BFILE is Open with FILEISOPEN").

Java (JDBC): Checking 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_45
{

  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;
        boolean result = false;

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

        result = src_lob.isFileOpen();
        System.out.println(
           "result of fileIsOpen() before opening file : " + result);
        if (!result) 
        src_lob.openFile();

        result = src_lob.isFileOpen();
        System.out.println(
           "result of fileIsOpen() after opening file : " + result);

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