Skip Headers
Oracle® C++ Call Interface Programmer's Guide,
11g Release 2 (11.2)

E10764-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

7 Programming with LOBs

This chapter provides an overview of LOBs and their use in OCCI.

This chapter contains these topics:

See also:

Oracle Database SecureFiles and Large Objects Developer's Guide for extensive information about LOBs

Overview of LOBs

Oracle C++ Call Interface includes classes and methods for performing operations on large objects, LOBs. LOBs are either internal or external depending on their location with respect to the database.

Introducing Internal LOBs

Internal LOBs are stored inside database tablespaces in a way that optimizes space and enables efficient access. Internal LOBs use copy semantics and participate in the transactional model of the server. You can recover internal LOBs after transaction or media failure, and any changes to an internal LOB value can be committed or rolled back. There are three SQL data types for defining instances of internal LOBs:

  • BLOB: A LOB whose value is composed of unstructured binary (raw) data

  • CLOB: A LOB whose value is composed of character data that corresponds to the database character set defined for the Oracle database

  • NCLOB: A LOB whose value is composed of character data that corresponds to the national character set defined for the Oracle database

The copy semantics for LOBs dictate that when you insert or update a LOB with a LOB from another row in the same table, both the LOB locator and the LOB value are copied. In other words, each row has a copy of the LOB value.

Introducing External LOBs

BFILEs are large binary (raw) data objects data stored in operating system files outside database tablespaces; therefore, they are referred to as external LOBs. These files use reference semantics, where only the locator for the LOB is reproduced when inserting or updating in the same table. Apart from conventional secondary storage devices such as hard disks, BFILEs may also be located on tertiary block storage devices such as CD-ROMs, PhotoCDs and DVDs. The BFILE data type allows read-only byte stream access to large files on the file system of the database server. Oracle can access BFILEs if the underlying server operating system supports stream mode access to these files.

External LOBs do not participate in transactions. Any support for integrity and durability must be provided by the underlying file and operating systems. An external LOB must reside on a single device; it may not be striped across a disk array.

Storing LOBs

The size of the LOB value, among other things, dictates where it is stored. The LOB value is either stored inline with the row data or outside the row.

  • Locator storage: a LOB locator, a pointer to the actual location of the LOB value, is stored inline with the row data and indicates where the LOB value is stored.

    For internal LOBs, the LOB column stores a locator to the LOB value stored in a database tablespace. Each internal LOB column and attribute for a particular row has its own unique LOB locator and a distinct copy of the LOB value stored in the database tablespace.

    For external LOBs, the LOB column stores a locator to the external operating system file that houses the BFILE. Each external LOB column and attribute for a given row has its own BFILE locator. However, two different rows can contain a BFILE locator that points to the same operating system file.

  • Inline storage: Data stored in a LOB is termed the LOB value. The value of an internal LOB may or may not be stored inline with the other row data. If you do not set DISABLE STORAGE IN ROW, and if the internal LOB value is less than approximately 4,000 bytes, then the value is stored inline.Otherwise, it is stored outside the row.

    Since LOBs are intended to be large objects, inline storage is only relevant if your application mixes small and large LOBs.The LOB value is automatically moved out of the row once it extends beyond approximately 4,000 bytes.

Creating LOBs in OCCI Applications

Follow these steps to use LOBs in your application:

Restricting the Opening and Closing of LOBs

The definition of a transaction within which an open LOB value must be closed is one of the following:

The LOB opening and closing mechanism has the following restrictions:

Reading and Writing LOBs

There are two general methods for reading and writing LOBs: non-streamed, and streamed.

Reading LOBs

Example 7-1 illustrates how to get data from a non-NULL internal LOB, using a non-streamed method. This method requires that you keep track of the read offset and the amount remaining to be read, and pass these values to the read() method.

Example 7-1 How to Read Non-Streamed BLOBs

ResultSet *rset=stmt->executeQuery("SELECT ad_composite FROM print_media
                                    WHERE product_id=6666");
while(rset->next())
{
   Blob blob=rset->getBlob(1);
   if(blob.isNull())
     cerr <<"Null Blob"<<endl;
   else
   {
      blob.open(OCCI_LOB_READONLY);
 
      const unsigned int BUFSIZE=100;
      char buffer[BUFSIZE];
      unsigned int readAmt=BUFSIZE;
      unsigned int offset=1;
 
      //reading readAmt bytes from offset 1
      blob.read(readAmt,buffer,BUFSIZE,offset);
 
      //process information in buffer
      ...
      blob.close();
   }
}
stmt->closeResultSet(rset);

Example 7-2 is similar as it demonstrates how to read data from a BFILE, where the BFILE locator is not NULL, by using a non-streamed read.

Example 7-2 How to Read Non-Streamed BFILESs

ResultSet *rset=stmt->executeQuery("SELECT ad_graphic FROM print_media
                                    WHERE product_id=6666");
while(rset->next())
{
   Bfile file=rset->getBfile(1);
   if(bfile.isNull())
      cerr <<"Null Bfile"<<endl;
   else
   {
      //display the directory alias and the file name of the BFILE
      cout <<"File Name:"<<bfile.getFileName()<<endl;
      cout <<"Directory Alias:"<<bfile.getDirAlias()<<endl;
 
      if(bfile.fileExists())
      {
         unsigned int length=bfile.length();
         char *buffer=new char[length];
         bfile.read(length, buffer, length, 1);
         //read all the contents of the BFILE into buffer, then process
         ...
         delete[] buffer;
      }
      else
         cerr <<"File does not exist"<<endl;
   }
}
stmt->closeResultSet(rset);

In contrast to Example 7-1 and Example 7-2, the streamed reading demonstrated in Example 7-3 on a non-NULL BLOB does not require keeping track of the offset.

Example 7-3 How to Read Streamed BLOBs

ResultSet *rset=stmt->executeQuery("SELECT ad_composite FROM print_media
                                    WHERE product_id=6666");
while(rset->next())
{
   Blob blob=rset->getBlob(1);
   if(blob.isNull())
      cerr <<"Null Blob"<<endl;
   else
   {
      Stream *instream=blob.getStream(1,0);
      //reading from offset 1 to the end of the BLOB
 
      unsigned int size=blob.getChunkSize();
      char *buffer=new char[size];
 
      while((unsigned int length=instream->readBuffer(buffer,size))!=-1)
      {
         //process "length" bytes read into buffer
         ...
      }
      delete[] buffer;
      blob.closeStream(instream);
   }
}
stmt->closeResultSet(rset);

Writing LOBs

Example 7-4 demonstrates how to write data to an internal non-NULL LOB by using a non-streamed write. The writeChunk() method is enclosed by the open() and close() methods; it operates on a LOB that is currently open and ensures that triggers do not fire for every chunk read. The write() method can be used for the writeChunk() method; however, the write() method implicitly opens and closes the LOB.

Example 7-4 How to Write Non-Streamed BLOBs

ResultSet *rset=stmt->executeQuery("SELECT ad_composite FROM print_media
                                    WHERE product_id=6666 FOR UPDATE");
while(rset->next())
{
   Blob blob=rset->getBlob(1);
   if(blob.isNull())
      cerr <<"Null Blob"<<endl;
   else
   {
      blob.open(OCCI_LOB_READWRITE);
 
      const unsigned int BUFSIZE=100;
      char buffer[BUFSIZE];
      unsigned int writeAmt=BUFSIZE;
      unsigned int offset=1;
 
      //writing writeAmt bytes from offset 1
      //contents of buffer are replaced after each writeChunk(),
      //typically with an fread()
      while(<fread "BUFSIZE" bytes into buffer succeeds>)
      {
         blob.writeChunk(writeAmt, buffer, BUFSIZE, offset);
         offset += writeAmt;
      }
      blob.writeChunk(<remaining amt>, buffer, BUFSIZE, offset);
 
      blob.close();
   }
}
stmt->closeResultSet(rset);
conn->commit();

Example 7-5 demonstrates how to write data to an internal LOB that is populated by using a streamed write.

Example 7-5 How to Write Streamed BLOBs

ResultSet *rset=stmt->executeQuery("SELECT ad_composite FROM print_media
                                    WHERE product_id=6666 FOR UPDATE");
while(rset->next())
{
   Blob blob=rset->getBlob(1);
   if(blob.isNull())
      cerr <<"Null Blob"<<endl;
   else
   {
      char buffer[BUFSIZE];
      Stream *outstream=blob.getStream(1,0);
 
      //writing from buffer beginning at offset 1 until 
      //a writeLastBuffer() method is issued.
      //contents of buffer are replaced after each writeBuffer(),
      //typically with an fread()
      while(<fread "BUFSIZE" bytes into buffer succeeds>)
         ostream->writeBuffer(buffer,BUFSIZE);
      ostream->writeLastBuffer(buffer,<remaining amt>);
      blob.closeStream(outstream);
   }
}
stmt->closeResultSet(rset);
conn->commit();

Enhancing the Performance of LOB Reads and Writes

Reading and writing of internal LOBs can be improved by using either getChunkSize() method.

Using the getChunkSize() Method

The getChunkSize() method returns the usable chunk size in bytes for BLOBs, and in characters for CLOBs and NCLOBs. Performance improves when a read or a write begins on a multiple of the usable chunk size, and the request size is also a multiple of the usable chunk size. You can specify the chunk size for a LOB column when you create a table that contains the LOB.

Calling the getChunkSize() method returns the usable chunk size of the LOB. An application can batch a series of write operations until an entire chunk can be written, rather than issuing multiple LOB write calls that operate on the same chunk

To read through the end of a LOB, use the read() method with an amount of 4 GB. This avoids the round-trip involved with first calling the getLength() method because the read() method with an amount of 4 GB reads until the end of the LOB is reached.

For LOBs that store variable width characters, the GetChunkSize() method returns the number of Unicode characters that fit in a LOB chunk.

Updating LOBs

To update a value of a LOB in the database, you must assign the new value to the LOB, execute a SQL UPDATE command in the database, and then commit the transaction. Example 7-6 demonstrates how to update an existing CLOB (in this case, by setting it to empty), while Example 7-7 demonstrates how to update a BFILE.

Example 7-6 Updating a CLOB Value

Clob clob(conn);
clob.setEmpty();
stmt->setSQL("UPDATE print_media SET ad_composite = :1
              WHERE product_id=6666");
stmt->setClob(1, clob);
stmt->executeUpdate();
conn->commit();

Example 7-7 Updating a BFILE Value

Bfile bfile(conn);
bfile.setName("MEDIA_DIR", "img1.jpg");
stmt->setSQL("UPDATE print_media SET ad_graphic = :1 
              WHERE product_id=6666");
stmt->setBfile(1, bfile);
stmt->executeUpdate();
conn->commit();

Reading and Writing Multiple LOBs

As of Oracle Database 10g Release 2, OCCI has new interfaces that enhance application performance while reading and writing multiple LOBs, such as Bfiles, Blobs, Clobs and NClobs.

These interfaces have several advantages over the standard methods for reading and writing a single LOB at a time:

  • Reading and writing multiple LOBs through OCCI in a single server round-trip improves performance by decreasing I/O time between the application and the back end.

  • The new APIs provide support for LOBs that are larger than the previous limit of 4 GB. The new interfaces accept the oraub8 data type for amount, offsets, buffer and length parameters. These parameters are mapped to the appropriate 64-bit native data type, which is determined by the compiler and the operating system.

  • For Clob-related methods, the user can specify the data amount read or written in terms of character counts or byte counts.

New APIs for this features are described in Chapter 13, "OCCI Application Programming Interface", section on Connection Class, and include readVectorOfBfiles(), readVectorOfBlobs(), readVectorOfClobs() (overloaded to support general charactersets, and the UTF16 characterset in particular), writeVectorOfBlobs(), and writeVectorOfClobs() (overloaded to support general charactersets, and the UTF16 characterset in particular).

Using the Interfaces for Reading and Writing Multiple LOBs

Each of the readVectorOfxxx() and writeVectorOfxxx() interface uses the following parameters:

  • conn, a Connection class object

  • vec, a vector of LOB objects: Bfile, Blob, Clob, or NClob

  • byteAmts, array of amounts, in bytes, for reading or writing

  • charAmts, array of amounts, in characters, for reading or writing (only applicable for Clobs and NClobs)

  • offsets, array of offsets, in bytes for Bfiles and Blobs, and in characters for Clobs and NClobs

  • buffers, array of buffer pointers

  • bufferLengths, array of buffer lengths.

If there are errors in either reading or writing of one of the LOBs in the vector, the whole operation is cancelled. The byteAmts or charAmts parameters should be checked to determine the actual number of bytes or characters read or written.

Using Objects with LOB Attributes

An OCCI application can use the operator new() to create a persistent object with a LOB attribute. By default, all LOB attributes are constructed by using the default constructor, and are initialized to NULL.

Example 7-8 demonstrates how to create and use persistent objects with internal LOB attributes. Example 7-9 demonstrates how to create and use persistent objects with external LOB attributes.

Example 7-8 How to Use a Persistent Object with a BLOB Attribute

  1. Create a persistent object with a BLOB attribute:

    Person *p=new(conn,"PERSON_TAB")Person();
    p->imgBlob = Blob(conn);
    
  2. Either initialize the Blob object to empty:

    p->imgBlob.setEmpty();
    

    Or set it to some existing value

  3. Mark the Blob object as dirty:

    p->markModified();
    
  4. Flush the object:

    p->flush();
    
  5. Repin the object after obtaining a REF to it, thereby retrieving a refreshed version of the object from the database and acquiring an initialized LOB:

    Ref<Person> r = p->getRef();
    delete p;
    p = r.ptr();
    
  6. Write the data:

    p->imgBlob.write( ... );
    

Example 7-9 How to Use a Persistent Object with a BFILE Attribute

  1. Create a persistent object with a BFILE attribute:

    Person *p=new(conn,"PERSON_TAB")Person();
    p->imgBFile = BFile(conn);
    
  2. Initialize the Bfile object:

    p->setName(directory_alias, file_name);
    
  3. Mark the Bfile object as dirty:

    p->markModified();
    
  4. Flush the object:

    p->flush();
    
  5. Read the data:

    p->imgBfile.read( ... );
    

Using SecureFiles

Introduced with Oracle Database 11g Release 1, SecureFiles LOBs add powerful new features for LOB compression, encryption, and deduplication.

Using SecureFile Compression

SecureFiles compression enables server-side compression of LOB data, transparent to the application. Using SecureFiles compression saves storage space with minimal impact on reading and updating performance for SecureFiles LOB data.

Using SecureFiles Encryption

SecureFiles introduce a new encryption capability for LOB data and extend Transparent Data Encryption by enabling efficient random read and write access to encrypted SecureFiles LOBs.

Using SecureFiles Deduplication

SecureFiles deduplication allows the Oracle Database to automatically detect duplicate LOB data, and to conserve space by storing a single copy of the SecureFiles LOB.

Combining SecureFiles Compression, Encryption, and Deduplication

You can combine compression, encryption and deduplication in any combination. Oracle Database applies these features according to the following rules:

  • Deduplicate detection, if enabled, is performed before compression and encryption. This prevents potentially unnecessary and expensive compression and encryption operations on duplicate SecureFiles LOBs.

  • Compression is performed before encryption, to allow for the highest possible compression ratios.

SecureFiles LOB Types and Constants

The following types for SecureFiles LOBs enable additional flexibility for compression, encryption, and deduplication. Table 7-1 lists options for the LobOptionType, while Table 7-2 lists options for the LobOptionValue.

Table 7-1 Values of Type LobOptionType

Value Description
OCCI_LOB_OPT_COMPRESS

Compression option type

OCCI_LOB_OPT_ENCRYPT

Encryption option type

OCCI_LOB_OPT_DEDUPLICATE

Deduplicate option type


Table 7-2 Values of Type LobOptionValue

Value Description
OCCI_LOB_COMPRESS_OFF

Turns off SecureFiles compression

OCCI_LOB_COMPRESS_ON

Turns on SecureFiles compression

OCCI_LOB_ENCRYPT_OFF

Turns off SecureFiles encryption

OCCI_LOB_ENCRYPT_ON

Turns on SecureFiles encryption

OCCI_LOB_DEDUPLICATE_OFF

Turns off SecureFiles deduplication

OCCI_LOB_DEDUPLICATE_ON

Turns off LOB deduplication