Informix JDBC Driver Programmer's Guide Manipulating Informix Data Types

You can access BLOB and CLOB data types in two ways:

• You can use the standard JDBC API methods described in the JDBC 2.0 specification from Sun Microsystems.
• If you are familiar with Informix BLOB and CLOB data types, you can use Informix extensions that are based on smart large object support within Informix Dynamic Server with Universal Data Option, which are described in this section.

Support for Informix smart large object data types is only available with 9.x versions of the database server.

The Informix extensions allow a JDBC application to create a smart large object independently and then insert the smart large object into different columns, even in multiple tables. Using multiple threads, an application can write or read data from various portions of the smart large object in parallel, which is very efficient.

Columns of either data type have a theoretical limit of 4 terabytes and a practical limit determined by your disk capacity.

The Informix smart large object implementation is based on the following classes:

• IfxLobDescriptor
• IfxLocator
• IfxSmartBlob
• IfxBblob and IfxCblob

These classes allow you to create, insert, and fetch large objects.

1. Create an IfxLobDescriptor object.
2. Create an IfxLocator object.
3. Create an IfxSmartBlob object that includes all the methods necessary to create, open, read, and write to a smart large object.
4. Execute the IfxSmartBlob.IfxLoCreate() method to create a large object on the database server. This method returns a locator handle.
5. Execute IfxSmartBlob.IfxLoWrite() to write the data to the smart large object.
6. Execute IfxSmartBlob.IfxLoClose() to close the large object.
7. Execute IfxSmartBlob.IfxLoRelease() to release the locator.

The IfxLobDescriptor class stores the internal storage characteristics for a smart large object. Before you can create a smart large object on the database server, you have to create an IfxLobDescriptor object, as follows:

IfxLobDescriptor loDesc = new IfxLobDescriptor(conn);

The conn is a java.sql.Connection object. The IfxLobDescriptor() constructor sets all the default values for the object.

The IfxLocator object (usually known as the locator pointer or large object locator) is the communication link between the database server and the client for a particular large object. Before it creates a large object or opens a large object for reading or writing, an application must create an IfxLocator object, as follows:

IfxLocator loPtr = new IfxLocator();

The IfxSmartBlob class provides all the methods necessary to create, open, read, and write to a smart large object. You can create an IfxSmartBlob object as follows:

IfxSmartBlob smb = new IfxSmartBlob(conn); 

You can use the following methods to create, open, read, and write to a smart large object. Here are the signatures and descriptions of each method:

public int IfxLoCreate(IfxLobDescriptor lo_desc, int flag, IfxLocator loPtr)

throws SQLException

This method creates a new smart large object on the server and opens it for access within a Java application.

The lo_desc is an IfxLobDescriptor object.

The flag is an integer value that specifies the mode in which the new smart large object is opened on the server. A table of flag values appears in IfxSmartBlob Flag Values.

The loPtr is an IfxLocator object.

The return value is of type integer; this integer is a large object locator handle that you can use in subsequent read, write, seek, and close methods. This is similar to a file handle in a file management system.

public int IfxLoOpen(IfxLocator loPtr, int flag) throws SQLException

This method opens an existing smart large object in the database server.

The loPtr is an IfxLocator object.

The flag is an integer value that specifies the mode in which the new smart large object is opened on the server. A table of flag values appears in IfxSmartBlob Flag Values.

The return value is an integer locator handle that you can use in subsequent read, write, seek, and close methods. This is similar to a file handle in a file management system.

public void IfxLoRelease(IfxLocator loPtr) throws SQLException

This method releases an IfxLocator object after a smart large object is closed using the IfxLoClose() method. This frees the resources on the server.

The loPtr is an IfxLocator object.

public void IfxLoClose(int lofd) throws SQLException

This method closes the large object on the database server side. For any further access to the same large object, you must reopen it with the IfxLoOpen() method.

The lofd is a locator handle obtained by the IfxLoCreate() or IfxLoOpen() method.

public long IfxLoSeek(int lofd, long offset, int whence) throws SQLException

This method sets the read or write position within an already opened large object. The absolute position depends on the value of the second parameter, offset, and the value of the third parameter, whence.

The lofd is a locator handle obtained by the IfxLoCreate() or IfxLoOpen() method.

The offset is an offset from the starting seek position.

The whence identifies the starting seek position. A table of whence values appears in IfxSmartBlob Whence Values.

The return value is a long integer representing the absolute position within the smart large object.

public void IfxLoTruncate(int lofd, long offset) throws SQLException

This method truncates a large object at an offset defined by the second parameter.

The lofd is a locator handle obtained by the IfxLoCreate() or IfxLoOpen() method.

The offset is the absolute position at which the smart large object is truncated.

public long IfxLoSize(int lofd) throws SQLException

This method returns a long integer representing the size of the large object.

The lofd is a locator handle obtained by the IfxLoCreate() or IfxLoOpen() method.

public byte[] IfxLoRead(int lofd, int nbytes) throws SQLException

This method returns nbytes bytes of data as a byte[] from the smart large object residing on the database server. This method allocates memory.

The lofd is a locator handle obtained by the IfxLoCreate() or IfxLoOpen() method.

The nbytes is the number of bytes read.

public int IfxLoRead(int lofd, byte[] buffer, int nbytes) throws SQLException

This method returns nbytes bytes of data in an already allocated buffer.

The lofd is a locator handle obtained by the IfxLoCreate() or IfxLoOpen() method.

The buffer is the byte[] buffer where the data is read.

The nbytes is the number of bytes read.

public int IfxLoRead(int lofd, FileOutputStream fout, int nbytes) throws

SQLException

This method reads nbytes bytes of data and stores it in a FileOutputStream object.

The lofd is a locator handle obtained by the IfxLoCreate() or IfxLoOpen() method.

The fout is the FileOutputStresm object in which the data is stored.

The nbytes is the number of bytes read.

The return value is the number of bytes read.

public int IfxLoWrite(int lofd, byte[] buffer) throws SQLException

This method writes buffer.length bytes of data from the buffer into the smart large object.

The lofd is a locator handle obtained by the IfxLoCreate() or IfxLoOpen() method.

The buffer is the byte[] buffer where the data is read.

The return value is the number of bytes written.

public int IfxLoWrite(int lofd, InputStream fin, int length) throws SQLException

This method writes length bytes of data from an InputStream object into a smart large object.

The lofd is a locator handle obtained by the IfxLoCreate() or IfxLoOpen() method.

The fin is the InputStream object from which data is written into the smart large object.

The length is the number of bytes written into the smart large object.

The return value is the number of bytes written.

Use the flag values in the following table with the IfxLoCreate() and IfxLoOpen() methods to open or create smart large objects with specific access modes.

Here is an example of how to use a LO_RDWR flag value:

IfxSmartBlob smb = new IfxSmartBlob(myConn);

int loFd = smb.IfxLoCreate(loDesc, smb.LO_RDWR, loPtr);

The loDesc and loPtr objects are previously created IfxLobDescriptor and IfxLocator objects, respectively.

Use the whence values in the following table with the IfxLoSeek() methods to define the position within a smart large object to start a seek operation.

Here is an example of how to use a LO_SEEK_SET whence value:

IfxLobDescriptor loDesc = new IfxLobDescriptor(myConn);

IfxLocator loPtr = new IfxLocator();

IfxSmartBlob smb = new IfxSmartBlob(myConn);

int loFd = smb.IfxLoCreate(loDesc, smb.LO_RDWR, loPtr);

System.out.println("A Smart blob is created ..");

int n = smb.IfxLoWrite(loFd, fin, fileLength);

System.out.println("Wrote data from FileInputStream ..");

smb.IfxLoClose(loFd);

System.out.println("Closed the Smart Blob ..");

loFd = smb.IfxLoOpen(loPtr, smb.LO_RDWR);

long m = smb.IfxLoSeek(loFd, 200, smb.LO_SEEK_SET);

The writing position is set at an offset of 200 bytes from the beginning of the smart large object.

The IfxBblob and IfxCblob classes are a bridge between the way of handling smart large object data described in Sun's JDBC 2.0 specification and the Informix extensions. The Informix extensions require an IfxLocator object to identify a smart large object.

As described in Sun's JDBC 2.0 specification, when you query a table containing a BLOB or CLOB column, a Blob or Clob object is returned, depending upon the column type. You can then use the JDBC 2.0 supporting methods for Blob and Clob objects to access the smart large object.

The IfxBblob class implements the java.sql.Blob interface. In addition to the methods defined by Sun, this class has the following Informix-specific methods to support the Informix extensions for smart binary large objects. Here are the signatures and descriptions of each extension:

public IfxBblob(IfxLocator loPtr)


This constructor creates an IfxBblob object from the IfxLocator object loPtr.

public IfxLocator getLocator() throws SQLException


This method returns an IfxLocator object from an IfxBblob object. You can then open, read, and write to the smart large object using the IfxSmartBlob.IfxLoOpen(), IfxLoRead(), and IfxLoWrite() methods. 

The IfxCblob class implements the java.sql.Clob interface. In addition to the methods defined by Sun, this class has the following Informix-specific methods to support the Informix extensions for smart binary large objects. Here are the signatures and descriptions of each extension:

public IfxCblob(IfxLocator loPtr)


This constructor creates an IfxCblob object from the IfxLocator object loPtr.

public IfxLocator getLocator() throws SQLException


This method returns an IfxLocator object from an IfxCblob object. You can then open, read, and write to the smart large object using the IfxSmartBlob.IfxLoOpen(), IfxLoRead(), and IfxLoWrite() methods. 

Whenever a BLOB, CLOB, text, or byte object is fetched from the database server, the data is cached into client memory. If the size of the large object is bigger than the value in the LOBCACHE environment variable, the large object data is stored in a temporary file. For more information about the LOBCACHE variable, see Memory Management of Large Objects.

The example in this section illustrates the following steps.

1. Create an IfxLobDescriptor object.
2. Create an IfxLocator object.
3. Create an IfxSmartBlob object.
4. Use the IfxSmartBlob.IfxLoCreate() method to create the smart large object.
5. Use the IfxSmartBlob.IfxLoWrite() method to write the data into the smart large object.
6. Close the smart large object using the IfxSmartBlob.IfxLoClose() method.
7. Release the IfxLocator object on the server using the IfxSmartBlob.IfxLoRelease() method.

The following code demonstrates these steps:

file = new File("data.dat");

FileInputStream fin = new FileInputStream(file);

byte[] buffer = new byte[200];;

IfxLobDescriptor loDesc = new IfxLobDescriptor(myConn);

IfxLocator loPtr = new IfxLocator();

IfxSmartBlob smb = new IfxSmartBlob(myConn);

// Now create the large object in server. Read the data from the file

// data.dat and write to the large object.

int loFd = smb.IfxLoCreate(loDesc, smb.LO_RDWR, loPtr);

System.out.println("A smart-blob is created ");

int n = fin.read(buffer);

if (n > 0)

n = smb.IfxLoWrite(loFd, buffer);

System.out.println("Wrote: " + n +" bytes into it");

// Close the large object and release the locator.

smb.IfxLoClose(loFd);

System.out.println("Smart-blob is closed " );

smb.IfxLoRelease(loPtr);

System.out.println("Smart Blob Locator is released ");

After creating a smart large object, you must insert it into a BLOB or CLOB column. You must convert the IfxLocator object to an IfxBblob or IfxCblob object, depending upon the column type.

1. Create an IfxBblob or IfxCblob object, as follows:

		IfxBblob blb = new IfxBblob(loPtr);


The loPtr is an IfxLocator object obtained from one of the previous sets of steps.

2. Use the PreparedStatement.setBlob() or setClob() method to insert the Blob or Clob object into the table.

The following code demonstrates these steps:

String s = "insert into large_tab (col1, col2) values (?,?)";

pstmt = myConn.prepareStatement(s);

file = new File("data.dat");

FileInputStream fin = new FileInputStream(file);

byte[] buffer = new byte[200];;

IfxLobDescriptor loDesc = new IfxLobDescriptor(myConn);

IfxLocator loPtr = new IfxLocator();

IfxSmartBlob smb = new IfxSmartBlob(myConn);

// Create a smart large object in server

int loFd = smb.IfxLoCreate(loDesc, smb.LO_RDWR, loPtr);

System.out.println("A smart-blob has been created ");

int n = fin.read(buffer);

if (n > 0)

n = smb.IfxLoWrite(loFd, buffer);

smb.IfxLoClose(loFd);

System.out.println("Wrote: " + n +" bytes into it");

System.out.println("Smart-blob is closed " );

Blob blb = new IfxBblob(loPtr);

pstmt.setInt(1, 2); // set the Integer column

pstmt.setBlob(2, blb); // set the blob column

pstmt.executeUpdate();

System.out.println("Binding of smart large object to table is done");

pstmt.close();

smb.IfxLoRelease(loPtr);

System.out.println("Smart Blob Locator is released ");

The example in this section illustrates the following steps.

1. Cast the java.sql.Blob or java.sql.Clob object to an IfxBblob or IfxCblob object.
2. Use the IfxBblob.getLocator() or IfxCblob.getLocator() method to extract an IfxLocator object.
3. Create an IfxSmartBlob object.
4. Use the IfxSmartBlob.IfxLoOpen() method to open the smart large object.
5. Use the IfxSmartBlob.IfxLoRead() method to read the data from the smart large object.
6. Close the smart large object using the IfxSmartBlob.IfxLoClose() method.
7. Release the IfxLocator object on the server using the IfxSmartBlob.IfxLoRelease() method.

Standard JDBC ResultSet methods such as ResultSet.getBinaryStream(), getAsciiStream(), getString(), getBytes(), getBlob(), and getClob() can fetch BLOB or CLOB data from a table. The Informix extension classes can then access the data.

The following code example shows how to access the smart large object data using Informix extension classes:

byte[] buffer = new byte[200];

System.out.println("Reading data now ...");

try

{

int row = 0;

Statement stmt = myConn.createStatement();

ResultSet rs = stmt.executeQuery("Select * from demo_14");

while( rs.next() )

{

row++;

String str = rs.getString(1);

InputStream value = rs.getAsciiStream(2);

IfxBblob b = (IfxBblob) rs.getBlob(2);

IfxLocator loPtr = b.getLocator();

IfxSmartBlob smb = new IfxSmartBlob(myConn);

int loFd = smb.IfxLoOpen(loPtr, smb.LO_RDONLY);

System.out.println("The Smart Blob is Opened for reading ..");

int number = smb.IfxLoRead(loFd, buffer, buffer.length);

System.out.println("Read total " + number + " bytes");

smb.IfxLoClose(loFd);

System.out.println("Closed the Smart Blob ..");

smb.IfxLoRelease(loPtr);

System.out.println("Locator is released ..");

}

rs.close();

}

catch(SQLException e)

{

System.out.println("Select Failed ...\n" +e.getMessage());

}

First, the rs.getBlob() method gets a Blob object. The casting is required to convert the returned object to an IfxBblob object. Next, the IfxBblob.getLocator() method gets an IfxLocator object from the IfxBblob object. After the IfxLocator object is available, you can instantiate an IfxSmartBlob object and use the IfxLoOpen() and IfxLoRead() methods to read the smart large object data. Fetching Clob data is similar, but it uses the methods rs.getClob, IfxCblob.getLocator, and so on.

If you use getBlob() or getClob() to fetch a BLOB column, you do not need to use the Informix extensions to retrieve the actual BLOB content as outlined in the preceding sample code. You can simply use Java.Blob.getBinaryStream() or Java.Clob.getAsciiStream() to retrieve the content. Informix JDBC Driver implicitly gets the content from the database server for you, using basically the same steps as the sample code.