Compare All or Parts of Two BFILES

Figure 11-21 Use Case Diagram: Compare All or Parts of Two BFILES

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

Purpose

This procedure describes how to compare all or parts of two BFILES.

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" -- INSTR

• C (OCI): A syntax reference is not applicable in this release.

• 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 OPEN. See PL/SQL DBMS_LOB.COMPARE.

• 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 OPEN. See PL/SQL DBMS_LOB.COMPARE.

• Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > Open, Compare, and > OBJECTS > OraDatabase > PROPERTIES > Parameters. 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

The following examples determine whether a photograph in file, 'PHOTO_DIR', has already been used as a specific PHOTO by comparing each data entity bit by bit.

Note: LOBMAXSIZE is set at 4 Gb so that you do not have to find out the length of each BFILE before beginning the comparison.

Examples

Examples are provided in these five programmatic environments:

• PL/SQL (DBMS_LOB Package): Compare All or Parts of Two BFILES

• C (OCI): No example is provided with this release.

• COBOL (Pro*COBOL): Compare All or Parts of Two BFILES

• C/C++ (Pro*C/C++): Compare All or Parts of Two BFILES

• Visual Basic (OO4O): Compare All or Parts of Two BFILES

• Java (JDBC): Compare All or Parts of Two BFILES

PL/SQL (DBMS_LOB Package): Compare All or Parts of Two BFILES

/* Note that the example procedure instringBFILE_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE instringBFILE_proc IS
   Lob_loc        BFILE;
   Pattern        RAW(32767);
   Position       INTEGER;
BEGIN
   /* Select the LOB: */
   SELECT Intab.Recording INTO Lob_loc
      FROM THE(SELECT Mtab.InSeg_ntab FROM Multimedia_tab Mtab
         WHERE Clip_ID = 3) Intab
            WHERE Segment = 1;
   /* Open the BFILE: */
   DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY);
   /*  Initialize the pattern for which to search, find the 2nd occurrence of
       the pattern starting from the beginning of the BFILE: */
   Position := DBMS_LOB.INSTR(Lob_loc, Pattern, 1, 2);
   /* Close the BFILE: */
   DBMS_LOB.CLOSE(Lob_loc);
END;

COBOL (Pro*COBOL): Compare All or Parts of Two BFILES

       IDENTIFICATION DIVISION.
       PROGRAM-ID. BFILE-COMPARE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  USERID          PIC X(11) VALUES "SAMP/SAMP".
       01  BFILE1          SQL-BFILE.
       01  BFILE2          SQL-BFILE.
       01  RET             PIC S9(9) COMP.
       01  AMT             PIC S9(9) COMP.
       01  DIR-ALIAS       PIC X(30) VARYING.
       01  FNAME           PIC X(20) 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-COMPARE.

           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL
                CONNECT :USERID
           END-EXEC.

      * Allocate and initialize the BLOB locators: 
           EXEC SQL ALLOCATE :BFILE1 END-EXEC.
           EXEC SQL ALLOCATE :BFILE2 END-EXEC. 
  
      * Set up the directory and file information: 
           MOVE "PHOTO_DIR" TO DIR-ALIAS-ARR.
           MOVE 9 TO DIR-ALIAS-LEN.
           MOVE "fdroosevelt_photo" TO FNAME-ARR.
           MOVE 17 TO FNAME-LEN.
 
           EXEC SQL
              LOB FILE SET :BFILE1 DIRECTORY = :DIR-ALIAS,
              FILENAME = :FNAME
           END-EXEC.

           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC.
           EXEC SQL 
                SELECT PHOTO INTO :BFILE2
                FROM MULTIMEDIA_TAB WHERE CLIP_ID = 3
           END-EXEC.
 
      * Open the BLOBs for READ ONLY: 
           EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC.
           EXEC SQL LOB OPEN :BFILE2 READ ONLY END-EXEC.

      * Execute PL/SQL to get COMPARE functionality: 
           MOVE 5 TO AMT.
           EXEC SQL EXECUTE
             BEGIN 
               :RET := DBMS_LOB.COMPARE(:BFILE1,:BFILE2,
                                        :AMT,1,1);
             END;
           END-EXEC.
           
           IF RET = 0
      *        Logic for equal BFILEs goes here
               DISPLAY "BFILES are equal"
           ELSE
      *        Logic for unequal BFILEs goes here
               DISPLAY "BFILEs are not equal"
           END-IF.

           EXEC SQL LOB CLOSE :BFILE1 END-EXEC.
           EXEC SQL LOB CLOSE :BFILE2 END-EXEC.
       END-OF-BFILE.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BFILE1 END-EXEC.
           EXEC SQL FREE :BFILE2 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++): Compare All or Parts of Two BFILES

/* Pro*C/C++ lacks an equivalent embedded SQL form for the
   DBMS_LOB.COMPARE() function.  Like the DBMS_LOB.SUBSTR() function,
   however, Pro*C/C++ can invoke DBMS_LOB.COMPARE() in an anonymous PL/SQL
   block as is shown here:  */

#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 compareBFILEs_proc()
{
  OCIBFileLocator *Lob_loc1, *Lob_loc2;
  int Retval = 1;
  char *Dir1 = "PHOTO_DIR", *Name1 = "RooseveltFDR_photo";

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc1;
  EXEC SQL LOB FILE SET :Lob_loc1 DIRECTORY = :Dir1, FILENAME = :Name1;
  EXEC SQL ALLOCATE :Lob_loc2;
  EXEC SQL SELECT Photo INTO :Lob_loc2 FROM Multimedia_tab
           WHERE Clip_ID = 3;
  /* Open the BFILEs: */
  EXEC SQL LOB OPEN :Lob_loc1 READ ONLY;
  EXEC SQL LOB OPEN :Lob_loc2 READ ONLY;
  /* Compare the BFILEs in PL/SQL using DBMS_LOB.COMPARE() */
  EXEC SQL EXECUTE
    BEGIN
      :Retval := DBMS_LOB.COMPARE(
                   :Lob_loc2, :Lob_loc1, DBMS_LOB.LOBMAXSIZE, 1, 1);
    END;
  END-EXEC;
  /* Close the BFILEs:  */
  EXEC SQL LOB CLOSE :Lob_loc1;
  EXEC SQL LOB CLOSE :Lob_loc2;
  if (0 == Retval)
    printf("BFILEs are the same\n");
  else
    printf("BFILEs are not the same\n");
  /* Release resources used by the locators: */
  EXEC SQL FREE :Lob_loc1;
  EXEC SQL FREE :Lob_loc2;
}

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

Visual Basic (OO4O): Compare All or Parts of Two BFILES

'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 OraDyn As OraDynaset, OraMusic As OraBfile, OraMyMusic 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 OraMyMusic = OraParameters("MyMusic").Value

'Create dynaset: 
Set OraDyn = 
   OraDb.CreateDynaset(
      "SELECT * FROM Multimedia_tab WHERE Clip_Id = 1001", ORADYN_DEFAULT)
Set OraMusic = OraDyn.Fields("Music").Value

'Open the Bfile for reading: 
OraMusic.Open
OraMyMusic.Open

If OraMusic.Compare(OraMyMusic) Then
    'Process the data
Else
   'Do error processing
End If
OraDb.Connection.CommitTrans

Java (JDBC): Compare All or Parts of Two BFILES

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_66
{

  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_loc1 = null;
       BFILE lob_loc2 = null;
       ResultSet rset = null;

       rset = stmt.executeQuery (
          "SELECT photo FROM multimedia_tab WHERE clip_id = 2");
       if (rset.next())
       {
          lob_loc1 = ((OracleResultSet)rset).getBFILE (1);
       }

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

       lob_loc1.openFile ();
       lob_loc2.openFile ();

       if (lob_loc1.length() > lob_loc2.length()) 
       System.out.println("Looking for LOB2 inside LOB1.  result = " +
          lob_loc1.position(lob_loc2, 1));
   else
     System.out.println("Looking for LOB1 inside LOB2.  result = " +
          lob_loc2.position(lob_loc1, 1));

   stmt.close();
   conn.commit();
   conn.close();

    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
  }
}