[sqlite] Unicode problem when setting PRAGMA journal_mode
Hi all, Our windows application uses sqlite, and we've had no problems with it in our existing builds, which use the multibyte character set. We are now converting our codebase to Unicode. SqLite is quite happily running with our Unicode builds, creating SqLite databases in UTF-16LE encoding. We have come across one very odd thing, though. If I run the query "PRAGMA journal_mode = NORMAL" (actually, setting it to ANY of the value values, including "OFF" does the same thing), everything runs fine until the code closes the database, at which point the database returns SQLITE_BUSY, with the error "unable to close due to unfinalised statements". This happens even if the only query I run on the database is that pragma command. I can run other pragmas fine (e.g. "PRAGMA synchronous = NORMAL") and all SQL queries are fine, and the database will close happily; it's just this journal_mode pragma that's causing this odd behaviour, and ONLY in the Unicode build of our code - the multibyte build doesn't show this problem. This happens whether the database exists already [in UTF-16LE mode], or whether it creates it on first opening. Any ideas what could be wrong? We're using SqLite build v3.7.11. Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode problem when setting PRAGMA journal_mode
Realised I made a typo below: should have said "PRAGMA journal_mode = DELETE" (though setting it to WAL or OFF causes the same problem). -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nick Shaw Sent: 05 April 2012 13:34 To: General Discussion of SQLite Database (sqlite-users@sqlite.org) Subject: [sqlite] Unicode problem when setting PRAGMA journal_mode Hi all, Our windows application uses sqlite, and we've had no problems with it in our existing builds, which use the multibyte character set. We are now converting our codebase to Unicode. SqLite is quite happily running with our Unicode builds, creating SqLite databases in UTF-16LE encoding. We have come across one very odd thing, though. If I run the query "PRAGMA journal_mode = NORMAL" (actually, setting it to ANY of the value values, including "OFF" does the same thing), everything runs fine until the code closes the database, at which point the database returns SQLITE_BUSY, with the error "unable to close due to unfinalised statements". This happens even if the only query I run on the database is that pragma command. I can run other pragmas fine (e.g. "PRAGMA synchronous = NORMAL") and all SQL queries are fine, and the database will close happily; it's just this journal_mode pragma that's causing this odd behaviour, and ONLY in the Unicode build of our code - the multibyte build doesn't show this problem. This happens whether the database exists already [in UTF-16LE mode], or whether it creates it on first opening. Any ideas what could be wrong? We're using SqLite build v3.7.11. Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode problem when setting PRAGMA journal_mode
On 10 Apr 2012, at 1:42pm, Nick Shaw wrote: > Realised I made a typo below: should have said "PRAGMA journal_mode = DELETE" > (though setting it to WAL or OFF causes the same problem). Are you by any chance having a technical problem with the PRAGMA command itself ? For instance, suppose the command was encoded in UTF-16 and SQLite was expecting ASCII. Figure out what the default value should be (e.g. use the shell tool to do a "PRAGMA journal_mode;") then change your normal app to set the mode to that value. See if it gives you the same problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode problem when setting PRAGMA journal_mode
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: 10 April 2012 19:04 To: General Discussion of SQLite Database Subject: Re: [sqlite] Unicode problem when setting PRAGMA journal_mode >> Realised I made a typo below: should have said "PRAGMA journal_mode = >> DELETE" (though setting it to WAL or OFF causes the same problem). > > Are you by any chance having a technical problem with the PRAGMA command > itself ? For instance, suppose the command was encoded in UTF-16 and SQLite > was expecting ASCII. Figure out what the default > value should be (e.g. use > the shell tool to do a "PRAGMA journal_mode;") then change your normal app to > set the mode to that value. See if it gives you the same problem. I'm not getting the same problem with other PRAGMA commands (quick_check(1) and synchronous=NORMAL), so I don't know why journal_mode would be any different. If I leave this one PRAGMA out, everything is fine. If I include it, the DB always fails to close. But only in Unicode. I'll try your suggestion of setting it to what it currently is (it *should* be DELETE) and see what it does. Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode problem when setting PRAGMA journal_mode
On 04/11/2012 09:50 PM, Nick Shaw wrote: -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: 10 April 2012 19:04 To: General Discussion of SQLite Database Subject: Re: [sqlite] Unicode problem when setting PRAGMA journal_mode Realised I made a typo below: should have said "PRAGMA journal_mode = DELETE" (though setting it to WAL or OFF causes the same problem). Are you by any chance having a technical problem with the PRAGMA command itself ? For instance, suppose the command was encoded in UTF-16 and SQLite was expecting ASCII. Figure out what the default> value should be (e.g. use the shell tool to do a "PRAGMA journal_mode;") then change your normal app to set the mode to that value. See if it gives you the same problem. I'm not getting the same problem with other PRAGMA commands (quick_check(1) and synchronous=NORMAL), so I don't know why journal_mode would be any different. If I leave this one PRAGMA out, everything is fine. If I include it, the DB always fails to close. But only in Unicode. I'll try your suggestion of setting it to what it currently is (it *should* be DELETE) and see what it does. After sqlite3_close() returns SQLITE_BUSY, you can use sqlite3_next_stmt() to loop through those statements that SQLite thinks are unfinalized. Then use sqlite3_sql() to identify each. The results might reveal something. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode problem when setting PRAGMA journal_mode
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy Sent: 11 April 2012 16:07 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Unicode problem when setting PRAGMA journal_mode > > I'll try your suggestion of setting it to what it currently is (it *should* > > be DELETE) and see what it does. > > After sqlite3_close() returns SQLITE_BUSY, you can use > sqlite3_next_stmt() to loop through those statements that SQLite thinks are > unfinalized. Then use sqlite3_sql() to identify each. > > The results might reveal something. Oooh, good plan, thanks Dan, I'll try that. Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode problem when setting PRAGMA journal_mode
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy Sent: 11 April 2012 16:07 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Unicode problem when setting PRAGMA journal_mode > After sqlite3_close() returns SQLITE_BUSY, you can use > sqlite3_next_stmt() to loop through those statements that SQLite thinks are > unfinalized. Then use sqlite3_sql() to identify each. > > The results might reveal something. Ok, did this. It tells me the SQL command that is unfinalized is: "PRAGMA journal_mode = DELETE". I also did Simon's suggestion of setting the PRAGMA to what it already was set to (confirmed it was set to DELETE) - made no difference. Something seems amiss. I'll do some more debugging and get back to you all. Could be an error in my code which only occurs in Unicode build... Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unicode problem when setting PRAGMA journal_mode
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nick Shaw Sent: 11 April 2012 16:29 To: General Discussion of SQLite Database Subject: Re: [sqlite] Unicode problem when setting PRAGMA journal_mode -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Kennedy Sent: 11 April 2012 16:07 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Unicode problem when setting PRAGMA journal_mode > After sqlite3_close() returns SQLITE_BUSY, you can use > sqlite3_next_stmt() to loop through those statements that SQLite thinks are > unfinalized. Then use sqlite3_sql() to identify each. > > The results might reveal something. Found the problem. Was indeed in my code, deep inside my own wrapper function. There's no Unicode equivalent of sqlite3_exec(), so in unicode, it has to go through the prepare/step/finalize steps. It wasn't finalizing the stmt (as that bit of code in my wrapper is usually only called if the caller wants data back, so assumes the caller will finalize the stmt at a later time). Whoops! Thanks for the help anyway guys! Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
