[SQLiteJDBC] Force write to disk

[Javier Sedano]

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
+34 666 584 734