Re: [sqlite] Problem with sqlite3_interrupt on Windows
On 10/06/2009 9:02 AM, Igor Tandetnik wrote: > Jeremy Smith wrote: >> John Machin wrote: >>> On 10/06/2009 4:40 AM, Jeremy Smith wrote: >>> When I run sqlite3_interrupt, it doesn't close existing file handles, making further searches tricky. >>> Which handles? How do you know? What does "tricky" mean -- "difficult >>> but I can cope with it" or "causes an error" (if so, which?) or >>> something else? >>> >> I mean that it's impossible to use the same data files or SQL database >> file without running a new session (which means closing my GUI). So >> not 'tricky', but 'impossible'. > > There is a bug in your application. After a call to sqlite3_interrupt, > you leave behind an open transaction. Naturally, any other connections > are then blocked by that transaction. > > The only thing sqlite3_interrupt does is force a failure of sqlite3_step > call currently in progress, if any. It doesn't finalize any statements, > commit or roll back any transactions, or close any connections. It's > your responsibility to do that, if that's your intent. However the documentation (http://www.sqlite.org/c3ref/interrupt.html) does say: """An SQL operation that is interrupted will return SQLITE_INTERRUPT. If the interrupted SQL operation is an INSERT, UPDATE, or DELETE that is inside an explicit transaction, then the entire transaction will be rolled back automatically.""" In any case, as you say the interrupted thread needs to do some recovery work that should not require back-door nobbling of file handles. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite3_interrupt on Windows
Jeremy Smith wrote: > John Machin wrote: >> On 10/06/2009 4:40 AM, Jeremy Smith wrote: >> >>> When I run sqlite3_interrupt, it doesn't close existing file >>> handles, making further searches tricky. >>> >> >> Which handles? How do you know? What does "tricky" mean -- "difficult >> but I can cope with it" or "causes an error" (if so, which?) or >> something else? >> > I mean that it's impossible to use the same data files or SQL database > file without running a new session (which means closing my GUI). So > not 'tricky', but 'impossible'. There is a bug in your application. After a call to sqlite3_interrupt, you leave behind an open transaction. Naturally, any other connections are then blocked by that transaction. The only thing sqlite3_interrupt does is force a failure of sqlite3_step call currently in progress, if any. It doesn't finalize any statements, commit or roll back any transactions, or close any connections. It's your responsibility to do that, if that's your intent. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite3_interrupt on Windows
John Machin wrote: > On 10/06/2009 4:40 AM, Jeremy Smith wrote: > >> When I run sqlite3_interrupt, it doesn't close existing file handles, >> making further searches tricky. >> > > Which handles? How do you know? What does "tricky" mean -- "difficult > but I can cope with it" or "causes an error" (if so, which?) or > something else? > I mean that it's impossible to use the same data files or SQL database file without running a new session (which means closing my GUI). So not 'tricky', but 'impossible'. >> So I wrote code which clears all normal >> file handles (fopen in shell.c), but... >> > > Have you tried calling sqlite3_finalize() on each prepared statement and > sqlite3_close() on each open connection? > I ran sqlite3_close, and that didn't do much. So I wrote code in shell.c to close all fopen handles. After you (and someone else) replied, I edited os_win.c so a function closes the DB handle after an sqlite3_interrupt calls it. Basically, I was trying to close the *latest* handle to CreateFile when in fact the *very first* handle is the DB. So now it sets the handle, if not NULL, to the first file handle opened with CreateFile. In other words: It's now running queries without a problem, after repeated interrupts. Anyone else curious about sqlite3_interrupt on Windows, can email me. >> How do I close the database file too? It's not opened using fopen, but >> with CreateFileA (in winOpen in os_win.c) so I don't know what to call >> after calling sqlite3_interrupt. CloseHandle(global_h) just throws up an >> error. And I can't do further searches until the database file is free. >> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > By the way, I'm using SQLite by selecting script files to run, and not via a DLL so I can't close down a search in any way. Thanks everyone for their help! Jeremy. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite3_interrupt on Windows
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jeremy Smith wrote: > When I run sqlite3_interrupt, it doesn't close existing file handles, Why would it? sqlite3_interrupt sets a flag on the sqlite3 handle. Various operations periodically check the flag and if it is true then error out with SQLITE_INTERRUPT. It does not terminate or clean up anything. In your example you would have one thread calling sqlite3_step which is taking a long time and another thread calling sqlite3_interrupt. The first thread would notice and sqlite3_step would return SQLITE_INTERRUPT. At that point it is up to you what to do. You can prepare new queries to execute, call sqlite3_reset to run the query again, or finalize the statements and call sqlite3_close on the connection. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkouwS8ACgkQmOOfHg372QTu8wCfa2/XM75a0A4LAaWbiMaE+/xJ 1YEAoMJp2/A19GdABeRRgjswKn7gMTnr =i+lq -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite3_interrupt on Windows
On 10/06/2009 4:40 AM, Jeremy Smith wrote: > When I run sqlite3_interrupt, it doesn't close existing file handles, > making further searches tricky. Which handles? How do you know? What does "tricky" mean -- "difficult but I can cope with it" or "causes an error" (if so, which?) or something else? > So I wrote code which clears all normal > file handles (fopen in shell.c), but... Have you tried calling sqlite3_finalize() on each prepared statement and sqlite3_close() on each open connection? > How do I close the database file too? It's not opened using fopen, but > with CreateFileA (in winOpen in os_win.c) so I don't know what to call > after calling sqlite3_interrupt. CloseHandle(global_h) just throws up an > error. And I can't do further searches until the database file is free. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with sqlite3_interrupt on Windows
When I run sqlite3_interrupt, it doesn't close existing file handles, making further searches tricky. So I wrote code which clears all normal file handles (fopen in shell.c), but... How do I close the database file too? It's not opened using fopen, but with CreateFileA (in winOpen in os_win.c) so I don't know what to call after calling sqlite3_interrupt. CloseHandle(global_h) just throws up an error. And I can't do further searches until the database file is free. Thanks in advance, Jeremy. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
