Inserting BLOBs

[Charles]

Hi all,

I'm trying to insert BLOBs into a table as follows (paraphrased from
my source:)
/*
Table created as
statement.addBatch("CREATE TABLE " + FILE_TABLE + "( "
+ "file_name TEXT NOT NULL, "
+ "user_id TEXT NOT NULL, "
+ "user_bitmap BLOB NOT NULL, "
+ "FOREIGN KEY (user_id) REFERENCES user_table(user_id), "
+ "UNIQUE(file_name, user_id));");
statement.executeBatch();
*/
...
PreparedStatement fileInsertStatement =
dbConnection_.prepareStatement("INSERT INTO
"
+ FILE_TABLE + "(user_id,
file_name, user_bitmap) values (?, ?, ?);");
seedBitmapBlob = dbConnection_.createBlob();
seedBitmapBlob.setBytes(1, BinaryOperations.SEED_BITMAP_BYTES);
...
fileInsertStatement.setBlob(3, seedBitmapBlob);

When this executes, I get the following result:
Exception in thread "main" java.lang.AbstractMethodError:
org.sqlite.Conn.createBlob()Ljava/sql/Blob;

I have gone and examined the source for myself just to confirm that it
is the case that createBlob is not implemented in the driver. It is
not. My question becomes how should I go about inserting binary data
into the database, then? I have googled quite a bit, but sadly 99% of
the results (even just for generic jdbc tasks) use files and
inputstreams. I just have a byte array I'd like to store in the
database.

For now I've commented out the offending lines and replaced

fileInsertStatement.setBlob(...)

with

fileInsertStatement.setBytes(...);

I have no idea whether or not this is safe or if it does what I want.
I was merely trying to see what worked and what didn't.

[Taro L. Saito]

Hi Charls,

Sorry for my late reply.

The best way to insert blob data with sqlite-jdbc is as follows:

1. Create a table with a column of blob type: create table T (id
integer, data blob)
2. Create a prepared statement: insert into T values(1, ?)
3. Prepare an byte array (say byte[] data = ...)
4. preparedStatement.setBytes(1, data)
5. preparedStatement.execute()...

For now, we have no plan to implement setBlob() method since
SQLite has no functionality to incrementally update blob data.

[Charles Duyk]

Thanks, this is in essence what I ended up doing.

Charles

> --
> You received this message because you are subscribed to the Google Groups "Xerial" group.
> To post to this group, send email to xer...@googlegroups.com.
> To unsubscribe from this group, send email to xerial+un...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/xerial?hl=en.
>
>

--
Charles Duyk

PGP Fingerprint: 9708 9865 38A8 ED4F FD19 1171 C3D6 1A38 72FC 9F72