Sqlite + Java: table not updating

Question

I'm using a Java wrapper for SQLite called SQLiteJDBC - this may have some bearing on any answers...

I have a table which I've displayed in a GUI, within that UI I have a button for the fields for an individual row of that table. When I save my changes I do this...

Statement stmt = connection.createStatement();
stmt.execute("update 'tableName' set 'fieldName'=1 where userid=1");

int updateCount = stmt.getUpdateCount();

My connection is valid, I get no exceptions thrown and getUpdateCount() returns '1' indicating that one row has been updated. However my table is not updated. I've spent the last few hours trying to work out what's going on but I'm not having any luck. Help!!

UPDATE

The problem seems to be that it's not committing the changes I make. While the application is running my changes are visible but as soon as I close the application and reopen it or check the database directly my changes are lost and we're back to square one. What's going on??!

Auto-commit is set to true and if I set it to false and try to commit I get an exception thrown.

Answer 1

I tried your example using sqlitejdbc-v056.jar and it worked fine, even with using the single quotes.

You could rewrite your call to use executeUpdate, but this should not make a difference:

int updateCount =
      stmt.executeUpdate("update 'tableName' set 'fieldName'=1 where userid=1");

Make sure to use setAutoCommit (should default to true anyway)

connection.setAutoCommit(true);

or to perform a commit explicitely after updating:

connection.commit();

Double-check that you are connecting to the right database. If the problem still is not solved, try to perform a SELECT on that record before and after the update to see if the value has changed.

---

Original answer:

Have you tried to remove the single quotes around your tableName and fieldName?

These would be treated as strings otherwise, which makes no sence. From the documentation:

A string constant is formed by enclosing the string in single quotes (')

Answer 2

My problem was that I was relying on the garbage collector to clear up my used ResultSets, I was not explicitly closing them.

In SQLite accessing a table locks the entire database so these used ResultSets that were still 'open' were preventing my obtaining write access to a table after I'd read its contents.

If you have this problem and are using SQLite just call close() on each ResultSet when you're done with it. (You may also want to try doing the same to any PreparedStatements, I'd assume that these would also close any ResultSets they were used to create but I haven't experimented to verify this.)

Answer 3

Oh god! Thank you man! You solved my problem which has taken me almost week to solve. Closing resultset is something you often forgot, like deleting objects in c++ ;-)

Full of optimizm I'm movin on to the next level of my app.