CODEWITHSUNDEEP
javajdbcderby
sproketboy
sproketboy

Reputation: 9452

An attempt was made to get a data value of type 'java.sql.Blob' from a data value of type 'CHAR () FOR BIT DATA'

I'm trying to read from a Derby database in a generalized way and I want to retrieve UUID as byte array 16. Usually with binary types, I can use Blob and map this to a byte array. In Derby though, I get this exception: 'An attempt was made to get a data value of type 'java.sql.Blob' from a data value of type 'CHAR () FOR BIT DATA'.'

SCHEMA

CREATE TABLE Contacts(  ID CHAR(16) FOR BIT DATA NOT NULL PRIMARY KEY ... etc

This gives me java.sql.Types -2 (BINARY)

It fails when I use rs.getBlob :

 byte[] buffer = new byte[1024];
    Blob blob = rs.getBlob(column);
    try (InputStream in = blob.getBinaryStream()) {
        ByteArrayOutputStream bos = new ByteArrayOutputStream((int) blob.length());
        for (int len; (len = in.read(buffer)) != -1; ) {
            bos.write(buffer, 0, len);
        }
        value = bos.toByteArray();
    }

But it does work if I use:

value = rs.getBytes(column)

Now I could just use rs.getBytes unless later I would also need a "real" Blob from Derby. How could I tell the difference? Or does Derby just always use getBytes?

Upvotes: 0

Views: 1756

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 108999

Using java.sql.Blob for JDBC type BINARY is wrong from a strict JDBC API interpretation, no matter which driver you use. Using getBytes is always the appropriate method for this type (getBinaryStream should also work). See also the JDBC 4.3 specification, appendix B, specifically table B-3, which defines the default Java type for JDBC type BINARY as byte[], and B-5, which only defines a mapping from a java.sql.Blob to JDBC type BLOB, and B-6 which only defines support for getBlob for columns of JDBC type BLOB.

To be clear, JDBC type BINARY - usually - maps to/from the SQL type BINARY (Derby's equivalent is called CHAR FOR BIT DATA), which is a fixed-length byte string/array type. Whereas a SQL (and JDBC) BLOB is a type holding binary data of varying length (usually of unlimited length (or at least extremely large)) through a so-called locator object. BLOB data is generally stored off-row (the row only contains a pointer or id of the actual data), whereas BINARY (and VARBINARY) are usually stored inline. A java.sql.Blob is the Java representation of that locator object, providing an indirection to access the off-row data of the blob.

However, some drivers are more lenient, either for backwards compatibility with earlier versions of the driver, or for maximum portability (e.g. bridge implementation differences between different database systems). For example, some drivers may define their (SQL) BLOB types as JDBC LONGVARBINARY instead (which supports getBytes and getBinaryStream), but may then also define support for getBlob for cases where users assume "I use a SQL BLOB, so I should use getBlob".

So, in short, if the column is identified as JDBC type BINARY, then use getBytes (or getInputStream, however if you want to get a UUID, which is always 16 bytes, then using getBytes makes more sense). If the row is identified as JDBC type BLOB, then use getBlob (though some drivers may also support getBytes and getInputStream, this is not specified in JDBC).

Upvotes: 2

Related Questions