Currently you can do this with SQLiteJDBC:
rs = conn.prepareStatement("select * from t;").executeQuery();
rs.next();
conn.prepareStatement("insert into t values (1);").executeUpdate();
But you shouldn't.
The problem is, that insert does not get run until the ResultSet is
closed. This is the serverless nature of SQLite, it cannot handle
concurrent writes. It has worked so far, because SQLite presumes that
connections are used in a single thread by programmers who know what
they're doing: i.e. they don't expect that 'insert' to run until they
close the ResultSet.
This is not what people expect from JDBC. When executeUpdate()
returns, the database needs to be updated. At the moment, if the
program crashes before rs.close() is called, the update is lost. So
there is only one solution compatible with a lack of concurrent
writes: stop doing it.
Expect the above code to throw an exception under v041.
Note: this only affects you if you're working from a single
connection. With two connections the write will pause for three
seconds and timeout. I.e. in the following:
rs = conn1.prepareStatement("select * from t;").executeQuery();
rs.next();
conn2.prepareStatement("insert into t values (1);").executeUpdate();
The executeUpdate() will pause execution for 3000ms, in a futile wait
for conn1 to finish reading. Then it will throw an SQLException. Soon
the single connection code will throw the same error (without the
pause).
If you want to update while holding a select open on the same
connection, use a transaction. The following is fine:
rs = conn.prepareStatement("select * from t;").executeQuery();
rs.next();
conn.setAutoCommit(false);
conn.prepareStatement("insert into t values (1);").executeUpdate();
rs.close();
conn.commit();
Expect version v041 today (in GMT+10).
d.
--~--~---------~--~----~------------~-------~--~----~
Mailing List: http://groups.google.com/group/sqlitejdbc?hl=en
To unsubscribe, send email to [EMAIL PROTECTED]
-~----------~----~----~----~------~----~------~--~---
