Determining If One LOB Locator for a BFILE Is Equal to Another

Figure 12-27 Use Case Diagram: Determining If One LOB Locator for a BFILE Is Equal to Another

Text description of the illustration adl12b24.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 one BFILE LOB locator is equal to another.

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): A syntax reference is not applicable in this release.

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

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

• 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 ASSIGN. See also C(OCI) function, OCILobIsEqual

• 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

If two locators are equal, this means that they refer to the same version of the LOB data (see "Read Consistent Locators" in Chapter 5, "Large Objects: Advanced Topics").

Examples

The examples are provided in the following three programmatic environments:

• PL/SQL: An example is not provided with this release.

• C (OCI): Determining If One LOB Locator for a BFILE Is Equal to Another

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

• C/C++ (Pro*C/C++): Determining If One LOB Locator for a BFILE Is Equal to Another

• Visual Basic (OO4O): An example is not provided with this release.

• Java (JDBC): Determining If One LOB Locator for a BFILE Is Equal to Another

C (OCI): Determining If One LOB Locator for a BFILE Is Equal to Another

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

   OCILobLocator *bfile_loc1; 
   OCILobLocator *bfile_loc2; 
   boolean  is_equal;
 
   /* Allocate the locator descriptors */ 
   (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc1,
                             (ub4) OCI_DTYPE_FILE,  
                             (size_t) 0, (dvoid **) 0);

   (void) OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &bfile_loc2,
                             (ub4) OCI_DTYPE_FILE,  
                             (size_t) 0, (dvoid **) 0);

   /* Select the bfile */ 
   selectLob(bfile_loc1, errhp, svchp, stmthp); 

   checkerr(errhp,
            OCILobLocatorAssign(svchp, errhp, bfile_loc1, &bfile_loc2));

   checkerr(errhp, OCILobIsEqual(envhp, bfile_loc1, bfile_loc2, &is_equal));

   if (is_equal == TRUE)
   {
     printf("Locators are equal\n");
   }
   else
   {
     printf("Locators are not equal\n");
   }

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

C/C++ (Pro*C/C++): Determining If One LOB Locator for a BFILE Is Equal to Another

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

/* Pro*C/C++ does not provide a mechanism to test the equality of two
   locators However, by using the OCI directly, two locators can be
   compared to determine whether or not they are equal as this example
   demonstrates: */

#include <sql2oci.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 BFILELocatorIsEqual_proc()
{
  OCIBFileLocator *Lob_loc1, *Lob_loc2;
  OCIEnv *oeh;
  boolean isEqual = 0;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc1;
  EXEC SQL ALLOCATE :Lob_loc2;
  EXEC SQL SELECT Photo INTO :Lob_loc1
           FROM Multimedia_tab WHERE Clip_ID = 3;
  EXEC SQL LOB ASSIGN :Lob_loc1 TO :Lob_loc2;
  /* Now you can read the BFILE from either Lob_loc1 or Lob_loc2 */
  /* Get the OCI Environment Handle using a SQLLIB Routine: */
  (void) SQLEnvGet(SQL_SINGLE_RCTX, &oeh);
  /* Call OCI to see if the two locators are Equal: */
  (void) OCILobIsEqual(oeh, Lob_loc1, Lob_loc2, &isEqual);
  if (isEqual)
    printf("Locators are equal\n");
  else
    printf("Locators are not equal\n");
  /* Note that in this example, the LOB locators will be Equal: */
  EXEC SQL FREE :Lob_loc1;
  EXEC SQL FREE :Lob_loc2;
}

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

}

Java (JDBC): Determining If One LOB Locator for a BFILE Is Equal to Another

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

  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 = stmt.executeQuery (
          "SELECT photo FROM multimedia_tab WHERE clip_id = 3");
      if (rset.next())
      {
          lob_loc1 = ((OracleResultSet)rset).getBFILE (1);
      }

      // Set both LOBS to reference the same BFILE: 
      lob_loc2 = lob_loc1;

      // Note that in this example, the Locators will be equal: 
      if (lob_loc1.equals(lob_loc2))  
      {
         // The Locators are equal: 
         System.out.println("The BFILEs are equal");
      }
      else 
      {
         // The Locators are different: 
         System.out.println("The BFILEs are NOT equal");
      }

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

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