comparing two byte arrays

[gmseed]

Hi

When comparing two byte arrays [of type BLOB] is the correct method as follows:

"CREATE TABLE BytesTable(id INTEGER PRIMARY KEY NOT NULL, bytes BLOB)";

...

byte[] byteArray;

...

String query = "select * from BytesTable where bytes==byteArray";

Thanks

Graham

[gmseed]

Hi

Simply using the == operator I get the following exception:

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (unrecognized token: "[B@e26fb32")

and if I use a prepared statement as follows:

            String selectSQL = "SELECT * FROM Bytes WHERE bytes=?";

            PreparedStatement pstmtSelectQuery = db_con.prepareStatement(selectSQL);

            pstmtSelectQuery.setBytes(1, bytes);

            rs = pstmtSelectQuery.executeQuery(selectSQL);

            while (rs.next())

            {   

                byte[] byteArray = rs.getBytes("bytes");

                System.out.println("byteArray read from db: " + ToStringBuffer.list(byteArray, "[", ", ","}"));          

            }

I get the exception:

java.sql.SQLException: not supported by PreparedStatment

Is there a solution to this query?

Graham

[gmseed]

Hi

Works fine if the no-argument version of executeQuery() is used with the prepared statement.

Graham

On Wednesday, May 1, 2013 11:50:46 AM UTC+1, gmseed wrote:

[Grace B]

On Wednesday, May 1, 2013 7:10:26 AM UTC-4, gmseed wrote:

Hi

Simply using the == operator I get the following exception:

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (unrecognized token: "[B@e26fb32")

When writing SQL statements, objects need to be translated to SQL literals. 

see http://www.sqlite.org/syntaxdiagrams.html#numeric-literal, and http://stackoverflow.com/questions/9655181/convert-from-byte-array-to-hex-string-in-java

and if I use a prepared statement as follows:

            String selectSQL = "SELECT * FROM Bytes WHERE bytes=?";

            PreparedStatement pstmtSelectQuery = db_con.prepareStatement(selectSQL);

            pstmtSelectQuery.setBytes(1, bytes);

            rs = pstmtSelectQuery.executeQuery(selectSQL);

            while (rs.next())

            {   

                byte[] byteArray = rs.getBytes("bytes");

                System.out.println("byteArray read from db: " + ToStringBuffer.list(byteArray, "[", ", ","}"));          

            }

I get the exception:

java.sql.SQLException: not supported by PreparedStatment 

Is there a solution to this query?

This is a bug in driver.