Oracle8i Application Developer's Guide - Large Objects (LOBs)
Release 2 (8.1.6)

Part Number A76940-01

Library

Product

Contents

Index

Go to previous page Go to beginning of chapter Go to next page

External LOBs (BFILEs), 23 of 41


Read Data from a BFILE

Figure 11-19 Use Case Diagram: Read Data from a BFILE


See Also:

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

Purpose

This procedure describes how to read data from a BFILE.

Usage Notes

Always Specify 4 Gb - 1 Regardless of LOB Size

When reading the LOB value, it is not an error to try to read beyond the end of the LOB. This means that you can specify an input amount of 4 Gb -1 regardless of the starting offset and the amount of data in the LOB. Hence, you do not need to incur a round-trip to the server to call OCILobGetLength() to find out the length of the LOB value in order to determine the amount to read.

Example

For example, assume that the length of a LOB is 5,000 bytes and you want to read the entire LOB value starting at offset 1,000. Also assume that you do not know the current length of the LOB value. Here is the OCI read call, excluding the initialization of all parameters:

#define MAX_LOB_SIZE 4294967295 
ub4  amount =  MAX_LOB_SIZE; 
ub4  offset = 1000; 
OCILobRead(svchp, errhp, locp, &amount, offset, bufp, bufl, 0, 0, 0, 0) 


Note:

The most efficient way to read large amounts of LOB data is to use OCILobRead() with the streaming mechanism enabled via polling or a callback. See Also: Chapter 9, "Internal Persistent LOBs", "Read Data from a BFILE", Usage Notes. 


The Amount Parameter

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:

Scenario

The following examples read a photograph into PHOTO from a BFILE 'PHOTO_DIR'.

Examples

Examples are provided in these six programmatic environments:

PL/SQL (DBMS_LOB Package): Read Data from a BFILE

/* Note that the example procedure readBFILE_proc is not part of the 
   DBMS_LOB package: */
CREATE OR REPLACE PROCEDURE readBFILE_proc IS
   Lob_loc       BFILE := BFILENAME('PHOTO_DIR', 'Jefferson_photo');
   Amount        INTEGER := 32767;
   Position      INTEGER := 1;
   Buffer        RAW(32767);
BEGIN
   /* Select the LOB: */  
   SELECT Photo INTO Lob_loc FROM Multimedia_tab 
      WHERE Clip_ID = 3;
   /* Open the BFILE: */  
   DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY);
   /* Read data: */  
   DBMS_LOB.READ(Lob_loc, Amount, Position, Buffer);
   /* Close the BFILE: */  
   DBMS_LOB.CLOSE(Lob_loc);
END;

C (OCI): Read Data from a BFILE

/* 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)); 
} 

#define MAXBUFLEN 32767

void BfileRead(envhp, errhp, svchp, stmthp) 
OCIEnv       *envhp; 
OCIError     *errhp; 
OCISvcCtx    *svchp; 
OCIStmt      *stmthp; 
{ 

   OCILobLocator *bfile_loc; 
   ub1 bufp[MAXBUFLEN];
   ub4 buflen, amt, offset;
   ub4 retval;
 
   /* 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, OCILobFileOpen(svchp, errhp, bfile_loc, 
                                   OCI_FILE_READONLY));

    /* This example will READ the entire contents of a BFILE piecewise into a
       buffer using a standard polling method, processing each buffer piece
       after every READ operation until the entire BFILE has been read. */
    /* Setting amt = 0 will read till the end of LOB*/
    amt = 0;
    buflen = sizeof(bufp);
    /* Process the data in pieces */
    offset = 1;
    memset(bufp, '\0', MAXBUFLEN);

    retval = OCILobRead(svchp, errhp, bfile_loc, 
                        &amt, offset, (dvoid *) bufp,
                        buflen, (dvoid *)0,
                        (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
                        (ub2) 0, (ub1) SQLCS_IMPLICIT);

    /* Closing the BFILE is mandatory if you have opened it */
    checkerr (errhp, OCILobFileClose(svchp, errhp, bfile_loc));
 
    /* Free the locator descriptor */ 
    OCIDescriptorFree((dvoid *)bfile_loc, (ub4)OCI_DTYPE_FILE); 
}

COBOL (Pro*COBOL): Read Data from a BFILE

       IDENTIFICATION DIVISION.
       PROGRAM-ID. READ-BFILE.
       ENVIRONMENT DIVISION.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  BFILE1         SQL-BFILE.
       01  BUFFER2        PIC X(5) VARYING.
       01  AMT            PIC S9(9) COMP.
       01  OFFSET         PIC S9(9) COMP VALUE 1.
         
           EXEC SQL INCLUDE SQLCA END-EXEC.
           EXEC SQL VAR BUFFER2 IS LONG RAW(5) END-EXEC.

       PROCEDURE DIVISION.
       READ-BFILE.

      * Allocate and initialize the CLOB locator
           EXEC SQL ALLOCATE :BFILE1 END-EXEC.
           EXEC SQL WHENEVER NOT FOUND GOTO END-OF-BFILE END-EXEC.
           EXEC SQL 
                SELECT MUSIC INTO :BFILE1
                FROM MULTIMEDIA_TAB M WHERE M.CLIP_ID = 3
           END-EXEC.
      * Open the BFILE
           EXEC SQL LOB OPEN :BFILE1 READ ONLY END-EXEC.
 
      * Initiate polling read
           MOVE 0 TO AMT.

           EXEC SQL LOB READ :AMT FROM :BFILE1
                 INTO :BUFFER2 END-EXEC.
      *
      *     Display the data here.
      * 
  
      * Close and free the locator
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL LOB CLOSE :BFILE1 END-EXEC.

       END-OF-BFILE.
           EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
           EXEC SQL FREE :BFILE1 END-EXEC.

C/C++ (Pro*C/C++): Read Data from a BFILE

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

#define BufferLength 4096

void readBFILE_proc()
{
  OCIBFileLocator *Lob_loc;
  /* Amount and BufferLength are equal so only one READ is necessary: */
  int Amount = BufferLength;
  char Buffer[BufferLength];
  /* Datatype Equivalencing is Mandatory for this Datatype: */
  EXEC SQL VAR Buffer IS RAW(BufferLength);

  EXEC SQL WHENEVER SQLERROR DO Sample_Error();
  EXEC SQL ALLOCATE :Lob_loc;
  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;
  EXEC SQL WHENEVER NOT FOUND CONTINUE;
  /* Read data: */
  EXEC SQL LOB READ :Amount FROM :Lob_loc INTO :Buffer;
  printf("Read %d bytes\n", Amount);
  /* Close the BFILE: */
  EXEC SQL LOB CLOSE :Lob_loc;
  EXEC SQL FREE :Lob_loc;
}

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

Visual Basic (OO4O): Read Data from a BFILE

Dim MySession As OraSession
Dim OraDb As OraDatabase

Dim OraDyn As OraDynaset, OraMusic As OraBfile, amount_read%, chunksize%, chunk 
As Variant

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

chunksize = 32767
Set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab", ORADYN_DEFAULT)
Set OraMusic = OraDyn.Fields("Music").Value

OraMusic.offset = 1
OraMusic.PollingAmount = OraMusic.Size 'Read entire BFILE contents

'Open the Bfile for reading: 
OraMusic.Open
amount_read = OraMusic.Read(chunk, chunksize)
While OraMusic.Status = ORALOB_NEED_DATA
    amount_read = OraMusic.Read(chunk, chunksize)
Wend
OraMusic.Close

Java (JDBC): Read Data from a BFILE

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

// Core JDBC classes: 
import java.sql.DriverManager;
import java.sql.Connection;
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_53
{
  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");
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();
    try
    {
       BFILE src_lob = null;
       ResultSet rset = null;
       Boolean result = null;
       InputStream in = null;
       byte buf[] = new byte[1000];
       int length = 0;
       boolean alreadyDisplayed = false;
       rset = stmt.executeQuery (
          "SELECT music FROM multimedia_tab WHERE clip_id = 2");
       if (rset.next())
       {
          src_lob = ((OracleResultSet)rset).getBFILE (1);
       }

       // Open the BFILE: 
       src_lob.openFile();

       // Get a handle to stream the data from the BFILE: 
       in = src_lob.getBinaryStream();

       // This loop fills the buf iteratively, retrieving data
       // from the InputStream: 
       while ((in != null) && ((length = in.read(buf)) != -1)) 
       {
          // the data has already been read into buf

          // We will only display the first CHUNK in this example: 
          if (! alreadyDisplayed) 
          {
             System.out.println("Bytes read in: " + Integer.toString(length));
             System.out.println(new String(buf));
             alreadyDisplayed = true;
          }
       }

       // Close the stream, BFILE, statement and connection: 
       in.close();
       src_lob.closeFile();
       stmt.close();
       conn.commit();
       conn.close();
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
  }
}


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index