JDBC Reference
Page 17 of 31

java.sql.Blob and java.sql.Clob

In JDBC 2.0, java.sql.Blob is the mapping for the SQL BLOB (Binary Large Object) type; java.sql.Clob is the mapping for the SQL CLOB (Character Large Object) type.

CLOBs and BLOBS provide a logical pointer to the large objects rather than to the object itself. Cloudscape materializes only one page into memory at a time.

Although Cloudscape does not support the built-in BLOB or CLOB data types, it does provide some support for them in the following ways:

  • It supports some BLOB features through support for the java.sql.Blob interface and the BLOB-related methods in java.sql.PreparedStatement and java.sql.ResultSet (the getBlob methods of CallableStatement are not implemented).
  • It supports some of the CLOB features through support for the java.sql.Clob interface and the CLOB-related methods in java.sql.PreparedStatement and java.sql.ResultSet (the getClob methods of CallableStatement are not implemented).

To use these features:

  • Use the SQL-J LONG VARBINARY type for BLOB storage; BINARY and VARBINARY types will also work.
  • Use the SQL-J LONG VARCHAR type for CLOB storage; CHAR and VARCHAR types will also work.
  • Use the getBlob or getClob methods on java.sql.ResultSet to retrieve a Blob or Clob handle to the underlying data.
  • You cannot use any Blob or Clob methods on such a column with an SQL-J statement; there is no correspondence between LONG VARBINARY/LONG VARCHAR types and the Blob and Clob types.

Cloudscape implements all of the methods for these JDBC 2.0 interfaces except for the set and get methods in CallableStatement.

As per the JDBC specification, the lifespan of a Clob or Blob ends when the transaction commits. For that reason, it is a good idea to turn off auto-commit with these features.

Table 6-4 JDBC 2.0 java.sql.Blob Methods Supported

Returns

Signature

Implementation Notes

InputStream

getBinaryStream()


byte[]

getBytes(long pos, int length)

Exceptions are raised if pos < 1, if pos is larger than the length of the Blob, or if length <= 0.

long

length()


long

position(byte[] pattern, long start)

Exceptions are raised if pattern == null, if start < 1, or if pattern is an array of length 0.

long

position(Blob pattern, long start)

Exceptions are raised if pattern == null, if start < 1, if pattern has length 0, or if an exception is thrown when trying to read the first byte of pattern.

Table 6-5 JDBC 2.0 java.sql.Clob Methods Supported

Returns

Signature

Implementation Notes

InputStream

getAsciiStream()


Reader

getCharacterStream()


String

getSubString(long pos, int length)

Exceptions are raised if pos < 1, if pos is larger than the length of the Clob, or if length <= 0.

long

length()


long

position(Clob searchstr, long start)

Exceptions are raised if searchStr == null or start < 1, if searchStr has length 0, or if an exception is thrown when trying to read the first char of searchStr.

long

position(String searchstr, long start)

Exceptions are raised if searchStr == null or start < 1, or if the pattern is an empty string.

Notes

The usual Cloudscape locking mechanisms (shared locks) prevent other transactions from updating or deleting the database item to which the java.sql.Blob or java.sql.Clob object is a pointer. However, in some cases, Cloudscape's instantaneous lock mechanisms could allow a period of time in which the column underlying the Blob or Clob is unprotected. A subsequent call to getBlob/getClob, or to a Blob/Clob method, could cause undefined behavior. Therefore, it is advisable to turn off instantaneous locking by setting the property bulkFetch to 1. (This problem also exists for the getXXXStream methods.) For example:

SELECT BlobColumn
FROM LargeTable
PROPERTIES bulkFetch=1

Furthermore, there is nothing to prevent the transaction that holds the Blob/Clob (as opposed to another transaction) from updating the underlying row. (The same problem exists with the getXXXStream methods.) Program applications to prevent updates to the underlying object while a Blob/Clob is open on it; failing to do this could result in undefined behavior.

Do not call more than one of the ResultSet getXXX methods on the same column if one of the methods is one of the following:

  • getBlob
  • getClob
  • getAsciiStream
  • getBinaryStream
  • getUnicodeStream

These methods share the same underlying stream; calling one more than one of these methods on the same column so could result in undefined behavior. For example:


ResultSet rs = s.executeQuery("SELECT text FROM CLOBS WHERE i = 1");
while (rs.next()) {
    aclob=rs.getClob(1);
    ip = rs.getAsciiStream(1);
}

The streams that handle long-columns are not thread safe. This means that if a user chooses to open multiple threads and access the stream from each thread, the resulting behavior is undefined.

Clobs are not locale-sensitive.

NOTE: Support for Blobs and Clobs is new in Version 3.5.