External LOBs (BFILEs), 32 of 41

Get DIRECTORY Alias and Filename

Figure 11-28 Use Case Diagram: Get DIRECTORY Alias and Filename

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

Purpose

This procedure describes how to get DIRECTORY alias and filename.

Usage Notes

Not applicable.

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" -- FILEGETNAME
C (OCI) (Oracle Call Interface Programmer's Guide): Chapter 7, "LOB and File Operations" for usage notes. Chapter 15, "Relational Functions" -- LOB Functions, OCILobFileGetName
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 ...GET DIRECTORY ...
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 DIRECTORY ...
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help PROPERTIES > DirectoryName, FileName. See also OBJECTS > OraBfile > Examples
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

This example retrieves the DIRECTORY alias and filename related to the BFILE, Music.

Examples

The examples are provided in the following six programmatic environments:

PL/SQL (DBMS_LOB Package): Get Directory Alias and Filename

CREATE OR REPLACE PROCEDURE getNameBFILE_proc IS
   Lob_loc         BFILE;
   DirAlias_name   VARCHAR2(30);
   File_name       VARCHAR2(40);
BEGIN
   SELECT Music INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 3;
   DBMS_LOB.FILEGETNAME(Lob_loc, DirAlias_name, File_name);
   /* do some processing based on the directory alias and file names */
END;

C (OCI): Get Directory Alias and Filename

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

   OCILobLocator *bfile_loc; 
   OraText dir_alias[32] = NULL;
   OraText filename[256]  = NULL;
   ub2 d_length = 32;
   ub2 f_length = 256;
 
   /* Allocate the locator descriptors */ 
   (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, OCILobFileGetName(envhp, errhp, bfile_loc,
                       dir_alias, &d_length, filename, &f_length));

   printf("Directory Alias : [%s]\n", dir_alias);
   printf("File name : [%s]\n", filename);

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

COBOL (Pro*COBOL): Get Directory Alias and Filename

       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-DIR-ALIAS.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID         PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1         SQL-BFILE.
       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-DIR-ALIAS.

           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.

      * Populate the BFILE locator: 
           EXEC SQL 
                SELECT PHOTO INTO :BFILE1
                FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3 END-EXEC.

      * Use the LOB DESCRIBE functionality to get 
      * the directory alias and the filename: 
           EXEC SQL LOB DESCRIBE :BFILE1 
                GET DIRECTORY, FILENAME INTO :DIR-ALIAS, :FNAME END-EXEC.
      
           DISPLAY "DIRECTORY: ", DIR-ALIAS-ARR, "FNAME: ", FNAME-ARR.
       END-OF-BFILE.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BFILE1 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++): Get Directory Alias and Filename

#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 getBFILEDirectoryAndFilename_proc()
{
  OCIBFileLocator *Lob_loc;
  char Directory[31], Filename[255];
  /* Datatype Equivalencing is Optional: */
  EXEC SQL VAR Directory IS STRING;
  EXEC SQL VAR Filename IS STRING;
  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;

  /* Select the BFILE: */
  EXEC SQL SELECT Photo INTO :Lob_loc
     FROM Multimedia_tab WHERE Clip_ID = 3;
  /* Open the BFILE: */
  EXEC SQL LOB OPEN :Lob_loc READ ONLY;
  /* Get the Directory Alias and Filename: */
  EXEC SQL LOB DESCRIBE :Lob_loc
     GET DIRECTORY, FILENAME INTO :Directory, :Filename;

  /* Close the BFILE: */
  EXEC SQL LOB CLOSE :Lob_loc;
  printf("Directory Alias: %s\n", Directory);
  printf("Filename: %s\n", Filename);
  /* Release resources held by the locator: */
  EXEC SQL FREE :Lob_loc;
}

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

}

Visual Basic (OO4O): Get Directory Alias and Filename

'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 OraMusic1 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 OraMusic1 = OraParameters("MyMusic").Value
'Get Directory alias and filename: 
MsgBox " Directory alias is " & OraMusic1.DirectoryName & 
   " Filename is " & OraMusic1.filename

OraDb.Connection.CommitTrans

Java (JDBC): Get Directory Alias and Filename

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();
    }
  }
}