External LOBs (BFILEs), 27 of 41

Checking If the BFILE Exists

Figure 12-23 Use Case Diagram: Checking If the BFILE Exists

Text description of adl12b18.gif follows

Text description of the illustration adl12b18.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 exists.

Usage Notes

Not applicable.

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" -- FILEEXISTS
C (OCI) Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 15, "Relational Functions" -- LOB Functions, OCILobFileExists
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" -- LOB DESCRIBE ... FILEEXISTS
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 DESCRIBE ...GET FILEEXISTS
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > PROPERTIES > Exists, and > OBJECTS > OraDatabase > PROPERTIES > Parameters. See also OBJECTS > OraBfile, OraDatabase > Examples
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

This example queries whether a BFILE that is associated with Recording.

Examples

The examples are provided in the following six programmatic environments:

PL/SQL (DBMS_LOB Package): Checking If the BFILE Exists

/* Note that the example procedure seeIfExistsBFILE_proc is not part of the 
   DBMS_LOB package:  */
CREATE OR REPLACE PROCEDURE seeIfExistsBFILE_proc IS
   File_loc      BFILE;
BEGIN
   /* Select the LOB: */
   SELECT Intab.Recording INTO File_loc
      FROM THE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab
         WHERE Mtab.Clip_ID = 3) Intab
            WHERE Intab.Segment = 1;
   /* See If the BFILE exists: */
   IF (DBMS_LOB.FILEEXISTS(File_loc) != 0)
   THEN
      DBMS_OUTPUT.PUT_LINE('Processing given that the BFILE exists');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Processing given that the BFILE does not exist');
   END IF;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Operation failed');
END;

C (OCI): Checking If the BFILE Exists

/* 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 Photo 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 BfileExists(envhp, errhp, svchp, stmthp) 
OCIEnv       *envhp; 
OCIError     *errhp; 
OCISvcCtx    *svchp; 
OCIStmt      *stmthp; 
{ 

   OCILobLocator *bfile_loc; 
   boolean is_exist;
 
   /* 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, OCILobFileExists(svchp, errhp, bfile_loc, &is_exist));

   if (is_exist == TRUE)
   {
     printf("File exists\n");
   }
   else
   {
     printf("File does not exist\n");
   }
 
   /* Free the locator descriptor */ 
   OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); 
}

COBOL (Pro*COBOL): Checking If the BFILE Exists

This script is also provided in $ORACLE_HOME/rdbms/demo/lobs/cobol/fexists

       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-EXISTS.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1         SQL-BFILE.
       01  FEXISTS        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-EXISTS.

           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.

           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC.
           EXEC SQL 
                SELECT PHOTO INTO :BFILE1
                FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3
           END-EXEC.

           EXEC SQL
                LOB DESCRIBE :BFILE1 GET FILEEXISTS INTO :FEXISTS
           END-EXEC.
           
           IF FEXISTS = 1
      *        Logic for file exists here
               DISPLAY "File exists"
           ELSE
      *        Logic for file does not exist here
               DISPLAY "File does not exist"
           END-IF.

       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.

C/C++ (Pro*C/C++): Checking If the BFILE Exists

This script is also provided in $ORACLE_HOME/rdbms/demo/lobs/proc/fexists

#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 seeIfBFILEExists_proc()
{
  OCIBFileLocator *Lob_loc;
  unsigned int Exists = 0;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT Mtab.Voiced_ref.Recording INTO :Lob_loc
           FROM Multimedia_tab Mtab WHERE Mtab.Clip_ID = 3;
  /* See if the BFILE Exists:  */
  EXEC SQL LOB DESCRIBE :Lob_loc GET FILEEXISTS INTO :Exists;
  printf("BFILE %s exist\n", Exists ? "does" : "does not");
  EXEC SQL FREE :Lob_loc;
}

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

}

Visual Basic (OO4O): Checking If the BFILE Exists

'Note that the PL/SQL packages and the tables mentioned here are not part of the
'standard OO4O installation: 
Dim MySession As OraSession
Dim OraDb As OraDatabase
Dim OraMusic As OraBfile, OraSql As OraSqlStmt

Set MySession = CreateObject("OracleInProcServer.XOraSession")
Set OraDb = MySession.OpenDatabase("exampledb", "scott/tiger", 0&)

OraDb.Connection.BeginTrans

Set OraParameters = OraDb.Parameters

OraParameters.Add "id", 1001, ORAPARM_INPUT

'Define out parameter of BFILE type: 
OraParameters.Add "MyMusic", Null, ORAPARM_OUTPUT
OraParameters("MyMusic").ServerType = ORATYPE_BFILE

Set OraSql = 
   OraDb.CreateSql(
      "BEGIN SELECT music INTO :MyMusic FROM multimedia_tab WHERE clip_id = :id; 
         END;", ORASQL_FAILEXEC)

Set OraMusic = OraParameters("MyMusic").Value

If OraMusic.Exists Then
    'Process the data
Else
   'Do error processing
End If
OraDb.Connection.CommitTrans

Java (JDBC): Checking If the BFILE Exists

import java.io.InputStream;
import java.io.OutputStream;

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Types;
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_74
{

  static final int MAXBUFSIZE = 32767;

  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");

    // It's faster when auto commit is off: 
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();
    try
    {
       BFILE lob_loc = null;
       ResultSet rset = stmt.executeQuery (
          "SELECT photo FROM multimedia_tab WHERE clip_id = 3");
       if (rset.next())
       {
          lob_loc = ((OracleResultSet)rset).getBFILE (1);
       }

       // See if the BFILE exists:  
       System.out.println("Result from fileExists(): " + lob_loc.fileExists());

       // Return the length of the BFILE: 
       long length = lob_loc.length();
       System.out.println("Length of BFILE: " + length);

       // Get the directory alias for this BFILE: 
       System.out.println("Directory alias: " + lob_loc.getDirAlias());

       // Get the file name for this BFILE: 
       System.out.println("File name: " + lob_loc.getName());
       stmt.close();
       conn.commit();
       conn.close();
    }
    catch (SQLException e)
    {
       e.printStackTrace();
    }
  }
}