Mapping of java.sql.Blob and java.sql.Clob interfaces

In the JDBC API, 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. BLOB and CLOB objects are collectively referred to as LOBs (large objects).

The Derby implementation of the java.sql.Blob and java.sql.Clob interfaces is LOCATOR-based, meaning that the implementation provides a logical pointer to a LOB rather than a complete copy of the object. Also, Derby does not materialize a LOB when you use the BLOB or CLOB data type. You can, however, call methods on a java.sql.Blob and java.sql.Clob object to materialize it (that is, to retrieve the entire object or parts of it).

You can access a LOB column only once within a row, by invoking a getter method on it.

To use the java.sql.Blob and java.sql.Clob features:

Use the SQL BLOB type for columns which hold very large binary values.
Use the SQL CLOB type for columns which hold very large string values.
Use the getBlob and getClob methods of the java.sql.ResultSet interface to retrieve a LOB using its locator. You can then materialize all or part of the LOB by calling Blob and Clob methods. Alternatively, you can call the ResultSet.getBytes method to materialize a BLOB, and you can call the ResultSet.getString method to materialize a CLOB.

Casting between strings and BLOBs is not recommended because casting is platform- and database-dependent. See CAST function for more information.

As with other character datatypes, Derby treats CLOBs as unicode strings and writes them to disk using UTF8 encoding. With a Java database like Derby, you do not need to worry about character sets and codepages.

Restrictions on BLOB and CLOB objects (LOB-types)

LOB-types cannot be compared for equality (=) and non-equality (!=, <>).
LOB-typed values are not orderable, so <, <=, >, >= tests are not supported.
LOB-types cannot be used in indices or as primary key columns.
DISTINCT, GROUP BY, and ORDER BY clauses are also prohibited on LOB-types.
LOB-types cannot be involved in implicit casting as other base-types.

Recommendation: Because the lifespan of a java.sql.Blob or java.sql.Clob ends when the transaction commits, turn off auto-commit with the java.sql.Blob or java.sql.Clob features.

The following table describes features of java.sql.Blob methods that are specific to Derby.

Table 1. Implementation notes on *java.sql.Blob* methods
Returns	Signature	Implementation Notes
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	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.

The following table describes features of java.sql.Clob methods that are specific to Derby.

Table 2. Implementation notes on *java.sql.Clob* methods
Returns	Signature	Implementation Notes
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	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.