sqlite - Java JDBC PreparedStatement Foreign Key Constraint Failed -
i'm designing billing program sqlite , jdbc, , i'm trying use helper method:
public static void preparedinsert(string query, string[] inserters) { connection c = connect(); try { preparedstatement statement = c.preparestatement(query); (int = 0; < inserters.length; i++) { statement.setobject(i + 1, "\'" + inserters[i] + "\'"); } statement.executeupdate(); c.commit(); joptionpane.showmessagedialog(null, "database updated!"); } catch (sqlexception e) { joptionpane.showmessagedialog(null, "error updating database: " + e.getmessage()); } disconnect(c); } public static connection connect() { connection c = null; try { class.forname("org.sqlite.jdbc"); sqliteconfig config = new sqliteconfig(); config.enforceforeignkeys(true); c = drivermanager.getconnection("jdbc:sqlite:mrwbilling.db", config.toproperties()); c.setautocommit(false); } catch ( exception e ) { joptionpane.showmessagedialog(null, "error connecting database: " + e.getmessage()); } return c; } public static void disconnect(connection c) { try { c.close(); } catch (sqlexception e) { joptionpane.showmessagedialog(null, "error disconnecting database: " + e.getmessage()); } }
the parameter i'm trying pass in this:
sqlitejdbc.preparedinsert("insert timesheets(date, attorney, notes) values(?, ?, ?);", new string[]{date, attorneyname, notes});
timesheets has 4 rows: id, date, attorney , notes id set autoincrement , attorney foreign key attorney table. attorneyname i'm passing in exists in attorney's table.
this working fine during prior build when using regular statements, i've swapped prepared statements, i'm getting this:
error updating database: [sqlite_constraint] abort due constraint violation (foreign key constraint failed)
i'm @ loss i'm doing wrong. suggestions?
the additional single quotes wrapping parameters causing fk violation. use instead in loop:
statement.setstring(i+1, inserters[i]);
you may need remove semicolon insert
statement.
Comments
Post a Comment