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.

java.sql.Blob and java.sql.Clob provide a logical pointer to the large object rather than a complete copy of the objects. Derby processes only one data page into memory at a time. The whole BLOB does not need to be processed and stored in memory just to access the first few bytes of the LOB object

Derby now supports the built-in BLOB or CLOB data types. Derby also provides the following support for these data types:

BLOB Features Derby supports 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.
CLOB Features Derby supports the java.sql.Clob interface and the CLOB-related methods in java.sql.PreparedStatement and java.sql.ResultSet. The getClob methods of CallableStatement procedures are not implemented.

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

Use the SQL BLOB type for storage; LONG VARCHAR FOR BIT DATA, BINARY, and VARCHAR FOR BIT DATA types also work.
Use the SQL CLOB type for storage; LONG VARCHAR, CHAR, and VARCHAR types also work.
Use the getBlob or getClob methods on the java.sql.ResultSet interface to retrieve a BLOB or CLOB handle to the underlying data.
You cannot call static methods on any LOB-columns.

In addition, casting between strings and BLOBs is not recommended because casting is platform and database dependent.

Derby uses unicode strings (2 byte characters), while other database products may use ASCII characters (1 byte per character). If various codepages are used, each character might need several bytes. A larger BLOB type might be necessary to accommodate a normal string in Derby. You should use CLOB types for storing strings.

Restrictions on BLOB, CLOB, (LOB-types):

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

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

Recommendations: 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.

Table 1. 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 , 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 2. 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.