sqlite preparedstatement finalized

java, sqlite

Solution

I believe that this is a bug in version 3.14.2.1 of the SQLite JDBC driver.

It seems to me that you're getting a unique constraint violation exception, but the SQLite JDBC driver is tripping over itself attempting to report this exception and some other exception is getting thrown instead.

I was able to reproduce your exception using sqlite-jdbc version 3.14.2.1 if I tried to insert the same data multiple times, e.g. by re-running your code. I downgraded the SQLite JDBC driver to 3.8.11.2 and I got the following exception after running your code:

java.sql.SQLException: [SQLITE_CONSTRAINT]  Abort due to constraint violation (UNIQUE constraint failed: charge.username, charge.date)
    at org.sqlite.core.DB.newSQLException(DB.java:890)
    at org.sqlite.core.DB.newSQLException(DB.java:901)
    at org.sqlite.core.DB.execute(DB.java:810)
    at org.sqlite.core.DB.executeUpdate(DB.java:847)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:86)
    at com.example.MontsRentalDatabase.createCharge(MontsRentalDatabase.java:40)
    at com.example.Main.main(Main.java:17)

Of course, when re-running the program, the data I'm trying to insert is in the table already. So a unique constraint violation is to be expected.

I then added the line

        statement.execute("DROP TABLE IF EXISTS charge");

to `createChargeTable()`, above the line that creates the table. The code then ran successfully multiple times using either version of the SQLite JDBC driver.

This bug has now been fixed in sqlite-jdbc version 3.15.1, so the fix is therefore to upgrade to this version or later.

Problem

I have an error of java.sql.SQLException: The prepared statement has been finalized which happens when I call the same preparedStatement a second time. I am calling it in a method. Here is the database Java class (the relevant piece) ``` //create the charge table. (ps is PreparedStatement) try{ statement.executeUpdate("CREATE TABLE charge(username TEXT NOT NULL, date DATETIME DEFAULT CURRENT_TIMESTAMP, charge REAL, PRIMARY KEY(username, date));"); } catch (SQLException ex) { System.out.println("charge table creation failed. exception" + ex); } ``` Method that creates a charge: ``` public void createCharge(String username, double amount){ try { System.out.println(username + amount); ps = connection.prepareStatement("INSERT INTO charge VALUES(?, ?, ?);"); ps.setString(1, username); ps.setDate(2, DateConvert.toSQLDate(Date.valueOf(LocalDate.MIN))); ps.setDouble(3, amount); ps.executeUpdate(); ps.clearParameters(); System.out.println("Complete"); } catch (SQLException ex) { Logger.getLogger(MontsRentalDatabase.class.getName()).log(Level.SEVERE, null, ex); } } ``` This is in the class where the charge is created: ``` public void createCharge(String username, double amount){ try { System.out.println(username + amount); ps = connection.prepareStatement("INSERT INTO charge VALUES(?, ?, ?);"); ps.setString(1, username); ps.setDate(2, DateConvert.toSQLDate(Date.valueOf(LocalDate.MIN))); ps.setDouble(3, amount); ps.executeUpdate(); //Line 170 ps.clearParameters(); System.out.println("Complete"); } catch (SQLException ex) { Logger.getLogger(MontsRentalDatabase.class.getName()).log(Level.SEVERE, null, ex); } } ``` The class that converts a normal date to sqldate: public class DateConvert { ``` public static java.sql.Date toSQLDate(java.util.Date date){ return new java.sql.Date(date.getTime()); } public static java.util.Date toJavaDate(java.sql.Date date){ return new java.util.Date(date.getTime()); } } ``` The error is in line 170 of create charge, which is when ps.executeUpdate runs. It runs successful the first time, fails on the second. Log: ``` 450100.0 Complete 450150.0 SEVERE: null java.sql.SQLException: The prepared statement has been finalized at org.sqlite.core.NativeDB.throwex(NativeDB.java:429) at org.sqlite.core.NativeDB.reset(Native Method) at org.sqlite.core.DB.executeUpdate(DB.java:878) at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:99) at server.RentalDatabase.createCharge(RentalDatabase.java:170) ``` Thanks for any help, J

Original source