CODEWITHSUNDEEP
blobprepared-statementderby
krammer
krammer

Reputation: 2658

Comparing Blob types in PreparedStatement

I am using Derby database. I wrote this query:

InputStream is = new java.io.ByteArrayInputStream(BYTES);
PreparedStatement st11 = conn.prepareStatement("select f from a1 where dBlob =?)");
st11.setBlob(1,is,BYTES.length);

dBlob is a BLOB datatype. BYTES is also a BLOB datatype.

But I get the following exception when I execute this query:

Caused by: ERROR 42818: Comparisons between 'BLOB' and 'BLOB' are not supported. Types must be comparable. String types must also have matching collation. If co llation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tabl ename AS VARCHAR(128)) = 'T1')

How can write the query to circumvent this problem ?

I have also tried using Convert, but it doesn't work with that too:

PreparedStatement st11 = conn.prepareStatement("select f from a1 where dBlob    
=CONVERT(?,BINARY)");
st11.setBlob(1,is,BYTES.length);

Moreover, is it possible to get the datatype of the argument being passed in ? by reflection or by some other means.

Upvotes: 0

Views: 1401

Answers (1)

Bryan Pendleton
Bryan Pendleton

Reputation: 16349

The inability to search directly on a BLOB column is a very fundamental limitation, and I don't think you'll be able to circumvent it.

If your application requires that you routinely search the database by exact match on BLOB data, what I generally do in my applications is to add an additional column to the database, of type VARCHAR(128), and store a SHA-1 hash of the BLOB value in the hash column.

Then, to search your database, compute the hash of the blob you want to search for, and search the hash column for an exact match.

Upvotes: 2

Related Questions