Ulric Auger wrote:
> What is failing at line: [DB.prepareStatement() 686]
>
> Connection.prepareStatement("INSERT INTO delay_forms (...) VALUES (...)");
>
> OR
>
> ps.executeUpdate()
>
> Your code (between <code></code>) seem fine.
>
> UlricHi ulric, the line that is failing <code> 695 connection.commit(); </code> Thanks for help so far. > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Kelly > Sent: November 24, 2009 12:51 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Sqlite Java blob > > 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 > > > > -- Peter _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
