SQLite JDBC driver version 3.21.0 (the latest).
Summary
A prepared statement is opened for multiple insert operations on a table, but is unable to survive primary key violations.
If one "bad" insert fails due to a Primary Key violation, the prepared statement is unable to process subsequent "good" inserts. An exception "statement is not executing" is raised when calling pstmt.setString().
Tracing the error into org.Sqlite.core.CorePreparedStatement the call is failing in checkOpen() pointer==0.
Example is below.
Does anyone know why this is happening? I see a similar bug report was raised but supposedly fixed.
Connection conn = DriverManager.getConnection("jdbc:sqlite:./test.db");
String createtable = "CREATE TABLE dummy(ID text, VAL text, PRIMARY KEY(ID) )";
String psSQL = "INSERT INTO dummy (ID, VAL) VALUES (?,?)";
String id = "123456789";
String val = "FooBar";
String id2 = "123456789";
String val2 = "FooBar2";
String id3 = "345678901";
String val3 = "FooBar3";
try {
Statement st= conn.createStatement();
st.executeUpdate(createtable);
PreparedStatement pst = conn.prepareStatement(psSQL);
// 1 insert good entry
pst.setString(1, id);
pst.setString(2, val);
pst.executeUpdate();
System.out.println("1st insert OK for " + id);
// 2. try to insert bad duplicate entry with pkey violation
try {
pst.setString(1, id);
pst.setString(2, val);
pst.executeUpdate();
System.out.println("2nd insert OK for " + id);
} catch (SQLException e) {
System.out.println("2nd insert Failed for " + id);
e.printStackTrace();
}
// 3. try to insert 3rd good value
try {
pst.setString(1, id3); // exception raised here
pst.setString(2, val3);
pst.executeUpdate();
System.out.println("3 insert OK for " + id3);
} catch (SQLException e) {
System.out.println("3 insert Failed for " + id3);
e.printStackTrace();
}
pst.close();
st.executeUpdate(droptable);
} catch (SQLException e) {
e.printStackTrace();
}