SQLITE JDBC driver prepared statement fails (internal pointer 0)

Refresh

April 2019

Views

47 time

1

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();
} 

1 answers

0

This bug has been fixed in sqlite-jdbc version 3.25.2, see https://github.com/xerial/sqlite-jdbc#news.