<< Mapping of java.sql.Types to SQL types | java.sql.Blob and java.sql.Clob interfaces >>

Implementation notes on java.sql.Blob methods

<< Mapping of java.sql.Types to SQL types | java.sql.Blob and java.sql.Clob interfaces >>

Derby Reference Manual

318

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).

Derby implements all of the methods for these interfaces except for the setBlob, getBlob,
setClob, and getClob methods of the CallableStatement interface.

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.

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 127.

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 , 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 128.

Implementation notes on java.sql.Clob methods