See If a LOB Is Open

Figure 9-17 Use Case Diagram: See If a LOB Is Open

Purpose

This procedure describes how to see if LOB is open.

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 (DBM_LOB Package): Oracle8i Supplied PL/SQL Packages Reference Chapter 18, "DBMS_LOB" -- OPEN, ISOPEN.

• C (OCI): Oracle Call Interface Programmer's Guide Chapter 15, "Relational Functions" -- LOB Functions -- OciSeeIfLob IsOpen.

• COBOL (Pro*COBOL): Pro*COBOL Precompiler Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" -- LOB DESCRIBE

• C/C++ (Pro*C): Pro*C/C++ Precompiler Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" -- LOB DESCRIBE ... ISOPEN ...

• Visual Basic (OO4O): There is no applicable syntax reference for this use case.

• Java (JDBC): Oracle8i JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" -- Creating and Populating a BLOB or CLOB Column. Oracle8i SQLJ Developer's Guide and Reference Chapter 5, "Type Support", Oracle Type Support, Support for BLOB, CLOB, and BFILE.

Scenario

The following "See if a LOB is Open" examples open a Video frame (Frame), and then evaluate it to see if the LOB is open.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): See if a LOB is Open

• C (OCI): See if a LOB is Open

• COBOL (Pro*COBOL): See if a LOB is Open

• C/C++ (Pro*C): See if a LOB is Open

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

• Java (JDBC): See if a LOB is Open

PL/SQL (DBMS_LOB Package): See if a LOB is Open

/* Note that the example procedure lobIsOpen_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE lobIsOpen_proc IS
   Lob_loc     BLOB;
   Retval      INTEGER;
BEGIN
   SELECT Frame INTO Lob_loc  FROM Multimedia_tab where Clip_ID = 1;

   /* Opening the LOB is optional: */
   DBMS_LOB.OPEN (Lob_loc , DBMS_LOB.LOB_READONLY);

   /* See if the LOB is open: */
   Retval := DBMS_LOB.ISOPEN(Lob_loc);
  /* The value of Retval will be 1 meaning that the LOB is open. */
END;

C (OCI): See if a LOB is Open

/* Select the locator into a locator variable */
sb4 select_frame_locator(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text      *sqlstmt = 
    (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=1";
  OCIDefine *defnp1;
  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                                  (ub4)strlen((char *)sqlstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
  checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1,
                                  (dvoid *)&Lob_loc, (sb4)0, 
                                  (ub2) SQLT_BLOB,(dvoid *) 0, 
                                  (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT));
  /* Execute the select and fetch one row */
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                 (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                 (ub4) OCI_DEFAULT));
    return (0);
}

void seeIfLOBIsOpen(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *Lob_loc;
  int isOpen;

  /* Allocate locator resources */
  (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&Lob_loc,
                            (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0);
  /* Select the locator */
  (void)select_frame_locator(Lob_loc, errhp, svchp, stmthp);
  
  /* See if the LOB is Open */
  checkerr (errhp, OCILobIsOpen(svchp, errhp, Lob_loc, &isOpen));
 
  if (isOpen)
  {
    printf("  Lob is Open\n");
    /* ... Processing given that the LOB has already been Opened */
  }
  else
  {
    printf("  Lob is not Open\n");
    /* ... Processing given that the LOB has not been Opened */
  }
  /* Free resources held by the locators*/
  (void) OCIDescriptorFree((dvoid *)  Lob_loc, (ub4) OCI_DTYPE_LOB);
  
  return;
  }

COBOL (Pro*COBOL): See if a LOB is Open

       IDENTIFICATION DIVISION.
       PROGRAM-ID. LOB-OPEN.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.

       01  BLOB1          SQL-BLOB.
       01  LOB-ATTR-GRP.
           05 ISOPN       PIC S9(9) COMP.
       01  SRC            SQL-BFILE.
       01  DIR-ALIAS      PIC X(30) VARYING.
       01  FNAME          PIC X(20) VARYING.
       01  DIR-IND        PIC S9(4) COMP.
       01  FNAME-IND      PIC S9(4) COMP.
       01  USERID   PIC X(11) VALUES "SAMP/SAMP".
           EXEC SQL INCLUDE SQLCA END-EXEC.

       PROCEDURE DIVISION.
       LOB-OPEN.
           EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CONNECT :USERID END-EXEC.

      * Allocate and initialize the target BLOB
           EXEC SQL ALLOCATE :BLOB1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BLOB END-EXEC.
           EXEC SQL SELECT FRAME INTO :BLOB1
              FROM MULTIMEDIA_TAB WHERE CLIP_ID = 1 END-EXEC.
  
      * See if the LOB is OPEN
           EXEC SQL 
              LOB DESCRIBE :BLOB1 GET ISOPEN INTO :ISOPN END-EXEC.

           IF ISOPN = 1 
      *       <Processing for the LOB OPEN case>
              DISPLAY "The LOB is open"
           ELSE
      *         <Processing for the LOB NOT OPEN case>
              DISPLAY "The LOB is not open"
           END-IF.

      * Free the resources used by the BLOB
       END-OF-BLOB.
           EXEC SQL FREE :BLOB1 END-EXEC.
           EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

       SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           DISPLAY " ".
           DISPLAY "ORACLE ERROR DETECTED:".
           DISPLAY " ".
           DISPLAY SQLERRMC.
           EXEC SQL
              ROLLBACK WORK RELEASE END-EXEC.
           STOP RUN.

C/C++ (Pro*C): See if a LOB is Open

#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 seeIfLOBIsOpen()
{
  OCIBlobLocator *Lob_loc;
  int isOpen = 1;

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  EXEC SQL SELECT Frame INTO :Lob_loc
           FROM Multimedia_tab WHERE Clip_ID = 1;
  /* See if the LOB is Open: */
  EXEC SQL LOB DESCRIBE :Lob_loc GET ISOPEN INTO :isOpen;
  if (isOpen)
    printf("LOB is open\n");
  else
    printf("LOB is not open\n");
  /* Note that in this example, the LOB is not open */
  EXEC SQL FREE :Lob_loc;
}

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

Java (JDBC): See if a LOB is Open

// Core JDBC classes: 
import java.io.OutputStream;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.Types;
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 Ex2_48
{
  public Ex2_48 ()
  {
  }

  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
    {
      BLOB blob = null;
      ResultSet rset = stmt.executeQuery (
          "SELECT frame FROM multimedia_tab WHERE clip_id = 1");
   if (rset.next())
   {
     blob = ((OracleResultSet)rset).getBLOB (1);
   }
       OracleCallableStatement cstmt =
            (OracleCallableStatement) conn.prepareCall (
            "BEGIN ? := DBMS_LOB.ISOPEN(?); END;");
   cstmt.registerOutParameter (1, Types.NUMERIC);
   cstmt.setBLOB(2, blob);
   cstmt.execute();
   int result = cstmt.getInt(1);
   System.out.println("The result is: " + Integer.toString(result));

   OracleCallableStatement cstmt2 = (OracleCallableStatement) 
       conn.prepareCall (
       "BEGIN DBMS_LOB.OPEN(?,DBMS_LOB.LOB_READONLY); END;");
   cstmt2.setBLOB(1, blob);
   cstmt2.execute();

   System.out.println("The LOB has been opened with a call to DBMS_LOB.OPEN()");

   // Use the existing cstmt handle to re-query the status of the locator: 
   cstmt.setBLOB(2, blob);
   cstmt.execute();
   result = cstmt.getInt(1);
   System.out.println("This result is: " + Integer.toString(result));

   stmt.close();
   cstmt.close();
   cstmt2.close();
   conn.commit();
   conn.close();
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
  }
}