Thanks, I've tried that, but to no avail.
Maybe a bit of background, our applaiction generates its froms from the
database layout, this is so it can be used for a variety tasks. It talks
to mysql and sqlite, so I was trying to keep the code as generic as
possibe.
I have a procedure - prepareStatement that takes the data to be saved as a
LinkedHasMap of strings <Columnanme,Value> and the table name, using this
and our database model we get the column types for the data, extract the
value and convert as required to the right type (mysql is a little bit
pickier than sqlite about types).
Heres the bit where I (try and) convert the value and insert it as binary
data. I have an image converted to a base64 string stored in the value,
this all happens inside a loop iterating through the columns for the data
to be stored.
Some of the methods I've tried
//SerialBlob blob = new SerialBlob(bData);
//ps.setBlob(parameterIndex,blob);
//Blob blob=connection.createBlob();
//blob.setBytes(bData.length, bData);
// ps.setBlob(parameterIndex,blob);
//ByteArrayInputStream bais = new ByteArrayInputStream(bData);
//ps.setBinaryStream(parameterIndex, bais);
//ps.setBinaryStream(parameterIndex, bais,bData.length);
<code>
case Types.BLOB:
// convert to byte array to blob
byte[] bData;
try {
bData = tabletapp.util.Base64.toBytes(value);
log.info("I have "+bData.length+" bytes in Bytes[]");
}
catch (Exception e){
bData = null;
log.info("Something went Horribly, Horribly Wrong");
}
ByteArrayInputStream bais = new ByteArrayInputStream(bData);
log.info("Database is "+dbType);
if (dbType.equals("sqlite")){
ps.setBytes(parameterIndex, bData);
log.info("Sqlite Blob set to "+value);
}
else { // mysql
ps.setBlob(parameterIndex, bais, bData.length);
}
log.debug("Parameter " + parameterIndex + " = [BLOB]");
break;
</code>
And if your still reading ;), heres the error
<error>
java.sql.SQLException: SQL logic error or missing database
ERROR 37:45 [DB.prepareStatement() 686]: Error in sql: INSERT INTO
delay_forms (t2_access_wp_action_required, t2_access_key,
t2_access_vegetation, t2_access_streetlight_blocked, t2_access_other,
t4_safety_wp_action_required, t4_safety_hv_line_too_close,
t4_safety_network_unsafe, t4_safety_danger_tag_reference, t4_safety_other,
t4_safety_additional_information, t7_cant_find_job_wp_action_required,
t7_cant_find_job_site_visit_check, t7_cant_find_job_dfis_check,
t7_cant_find_job_street_directory_check, t7_cant_find_job_dome_check,
t7_cant_find_job_pole_check, t7_cant_find_job_streetlights_check,
t7_cant_find_job_time_arrived_on_site, t7_cant_find_job_time_left_site,
t8_underground_fault_supply_voltage_at_base,
t8_underground_fault_description, t8_underground_fault_contractor_name,
t8_underground_fault_contractor_phone, work_order, t1_traffic_management,
t3_material, diagram_or_photograph)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?)
</error>
Which looks to me like the prepared statement is wrong somehow, I'm pretty
sure there is the same number of ? as fields and it works for other tables
that don't have blob data, so I'm fairly convinced the issue is in the
above, but I could be wrong.
Any ideas greatly appreciated :)
Ulric Auger wrote:
> Use PreparedStatement setBytes method.
> No need to convert to base64.
>
> Ulric
>
> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Kelly
> Sent: November 23, 2009 10:47 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Sqlite Java blob
>
> Hi, I've been tearining my hair out trying to figure out how store my
> image data in sqlite using Java. I think the best way to go is just store
> the base64 encoded string in the column, but I can for the life of me get
> a prepared statement for that to work.
>
> Can anybody show me how they are storing blobs in a sqlite databse?
>
> Thnaks in advance :)
>
>
> --
> Peter
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>
--
Peter
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Peter
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
