[SQLiteJDBC] Force write to disk

[Javier Sedano]

I am not using transactions. Autocommit is on. But I will try to turn autocommit off and use begin/commit.

I will also consider switching to another library. Thanks for the hint on sqlite4java.

Any other idea?


----
Javier Sedano
Novabase
javier.sedano at novabase.es<mailto:javier.sedano at novabase.es>
+34 666 584 734

________________________________
De: Brian Vincent [brainn at gmail.com]
Enviado el: miércoles, 21 de marzo de 2012 16:44
Para: Javier Sedano
CC: sqlitejdbc at lists.hcoop.net
Asunto: Re: [SQLiteJDBC] Force write to disk

I don't understand.  You're using transactions right?  You're grouping your writes into logical groups starting with "BEGIN" and ending with "COMMIT"?  When the commit returns back to you, the database is assuring you that your transaction has been written to disk.

Read this:
http://www.sqlite.org/lang_transaction.html

You must be creating an implicit transaction and never doing any of the actions that will make it commit to disk, other than close.  Use BEGIN and COMMIT.  It'll solve all your problems.

P.S.  This sqlitejdbc library isn't really maintained anymore.  I switched to sqlite4java a long time ago.  It's better maintained, but it is not a jdbc implementation.  It's simply a lightweight wrapper over sqlite native functions.  Because of that, it works better if you want to use any weird sqlite specific features.


On Wed, Mar 21, 2012 at 8:25 AM, Javier Sedano <javier.sedano at novabase.es<mailto:javier.sedano at novabase.es>> wrote:
Hi, friends,

 I've got a little problem (well... not so little), let's see if you have any idea.

 I've been using sqlitejdbc for several months with the following approach:

1) Upon star-up, I open a connection to the DB.

2) During the application life-cycle it creates threads, that do operations onto the DB (both insert/update/delete and selects). FYI, it is a simple SOAP WS (each SOAP connection creates a thread).

3) When the application ends, it closes the DB connection.

So far, so good.

However, while the DB schema has grown, an extrange behaviour has been noticed: the writes are not made to the DB file until it is closed. I suppose it is done as a performance enhancement (notice that although changes are not written to disk, they are available for further SELECTs on other threads, so I gues they are kept in a memory caché).

It has two problems for me:

1) For debugging, we use to do the SOAP request and (without stopping the server), browse the DB on sqlitestudio. Now this cannot be done, because changes are not written to the DB until the application closes the DB.

2) If the application crashes for any reason, the changes are not written to the file.

Is there a way that I can force a write to the file?

I've searched the Internet and the only proposed solution is to close() de DB to force the write. However, I cannot do that on my multi-threaded environment, because another therad may be currently doing anything.

Any other idea?

Thanks and regards,

----
Javier Sedano
Novabase
javier.sedano at novabase.es<mailto:javier.sedano at novabase.es>
+34 666 584 734<tel:%2B34%20666%20584%20734>

_______________________________________________
SQLiteJDBC mailing list
SQLiteJDBC at lists.hcoop.net<mailto:SQLiteJDBC at lists.hcoop.net>
https://lists.hcoop.net/listinfo/sqlitejdbc