Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
On Sat, 7 Jan 2017 10:58:56 +0100 gwenn wrote: > After 3.16, > sqlite3_step returns SQLITE_DONE > and > sqlite3_column_count does not return 0 but 6 That is the correct answer. The function returns a set of rows. Every invocation returns the same number of columns. Some invocations return more rows than others; some return zero rows. The column definitions of a SELECT statement are not affected by the WHERE clause. select * from T where 0 = 1; returns all rows of T meeting the criterion. The fact that no rows meet the criterion does not change their definition. --jkl ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
Sorry, I mean that there are impacts on SQLite wrappers: https://github.com/xerial/sqlite-jdbc/commit/42557128d56da563126003180fd8b8e8978ec818#diff-5a06ee6e8dbd2f4087ab8d361df52832 https://github.com/gwenn/gosqlite/commit/ac9891a74d94fb57679407bd36b80a5be218c6d6 https://github.com/gwenn/sqlite-jna/commit/be502a521b5c7f2becd0d10381cd8ccc3bd50234 Before 3.16, these drivers were throwing an error when executing a SELECT/PRAGMA with a zero column count. But the new SQLite behaviour seems good to me. On Sat, Jan 7, 2017 at 3:20 PM, Richard Hipp wrote: > On 1/7/17, gwenn wrote: >> Hello, >> You should try executing a PRAGMA with no result such as: >> PRAGMA table_info('no_such_table'); >> >> Before 3.16, >> sqlite3_step returns SQLITE_DONE >> and >> sqlite3_column_count returns 0 >> >> After 3.16, >> sqlite3_step returns SQLITE_DONE >> and >> sqlite3_column_count does not return 0 but 6 >> > > Please help us to understand why this is a problem? > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
On 1/7/17, gwenn wrote: > Hello, > You should try executing a PRAGMA with no result such as: > PRAGMA table_info('no_such_table'); > > Before 3.16, > sqlite3_step returns SQLITE_DONE > and > sqlite3_column_count returns 0 > > After 3.16, > sqlite3_step returns SQLITE_DONE > and > sqlite3_column_count does not return 0 but 6 > Please help us to understand why this is a problem? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
Hello, You should try executing a PRAGMA with no result such as: PRAGMA table_info('no_such_table'); Before 3.16, sqlite3_step returns SQLITE_DONE and sqlite3_column_count returns 0 After 3.16, sqlite3_step returns SQLITE_DONE and sqlite3_column_count does not return 0 but 6 Regards. On Fri, Jan 6, 2017 at 1:50 AM, Richard Hipp wrote: > On 1/5/17, Richard Hipp wrote: >> But apparently, many people are looking at the result of >> sqlite3_column_count() and if it is zero, they never bother to >> sqlite3_finalize() their PRAGMA statements. > > Or maybe not. > > I just did an analysis of the sqlite3_column_count() changes between > 3.15.2 and 3.16.0. All changes involve PRAGMAs that used to return 0 > but now return a positive number. > > So if applications were using a zero sqlite3_column_count() return to > indicate that the pragma returned no rows, that still works. > > In the cases where sqlite3_column_count() used to return 0 but now > returns positive, that would induce the application to call > sqlite3_step() on the pragma, which would then return SQLITE_DONE. > > Everything should still work. > > So now I don't really understand what is going wrong. > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
On 1/5/17, Richard Hipp wrote: > But apparently, many people are looking at the result of > sqlite3_column_count() and if it is zero, they never bother to > sqlite3_finalize() their PRAGMA statements. Or maybe not. I just did an analysis of the sqlite3_column_count() changes between 3.15.2 and 3.16.0. All changes involve PRAGMAs that used to return 0 but now return a positive number. So if applications were using a zero sqlite3_column_count() return to indicate that the pragma returned no rows, that still works. In the cases where sqlite3_column_count() used to return 0 but now returns positive, that would induce the application to call sqlite3_step() on the pragma, which would then return SQLITE_DONE. Everything should still work. So now I don't really understand what is going wrong. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
On 1/5/17, kk wrote: > All, > Many thanks for all the replies. It seems that I had now need to run > SQLite3Finalize against the PRAGMA stmts, You should *always* run sqlite3_finalize() on every statement, before you call sqlite3_close(). No exceptions. Pragmas are no different from any other statement in this respect. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
On 05/01/2017 23:35, Richard Hipp wrote: On 1/5/17, Kyle wrote: Dear all, Under version 3.16.1, SQLite3Close is returning SQLITE_BUSY when I open a database, execute some PRAGMAS, (run some stmts) then attempt the close. This did not occur under version 3.15.2, nor does it occur under the new version if I omit the PRAGMAs. Probably you are not talking to SQLite directly but are using some wrapper software. What is your wrapper software? All, Many thanks for all the replies. It seems that I had now need to run SQLite3Finalize against the PRAGMA stmts, and including this now corrects the issue. Whilst the behaviour of PRAGMAs may change for release to release, IMO, it would be useful if this was documented in the release notes, since in this instance we see a behaviour change after upgrade. BTW, I am using running the most excellent SQLiteForExcel wrapper to access SQLite from Excel. Many thanks again, K Furthermore the PRAGMAs now seem to return a resultset whereas previously they did not? Is this expected (or documented)? Please advise, Thanks, K -- PRAGMA encoding="UTF-8" PRAGMA cache_size="-2" (hopefully a reasonable attempt to tell SQLite that memory is more plentiful than default would suggest). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
On 1/5/17, Jens Alfke wrote: > > That would explain the SQLITE_BUSY error — if the pragma returns a non-empty > list of rows, and you’re not reading the rows or resetting the statement, > the statement stays busy and will block closing the database. > There has been a change in behavior of the sqlite3_column_count() interface. The behavior change was caused by the new pragma table-valued functions. For columns that return no rows, sqlite3_column_count() is sometimes now returning a different number of columns than it used to. This should be of no consequence, since zero rows times N columns is still zero regardless of the value of N. If you check the documentation, it says "This routine returns 0 if pStmt is an SQL statement that does not return data (for example an UPDATE)." Th3 sqlite3_column_count() interface is intended to tell you how many columns are in the result set. It was never intended to tell you whether or not there is a result set. I put in that sentence about "returns 0" many many years ago. I had completely forgotten about it. But apparently, many people are looking at the result of sqlite3_column_count() and if it is zero, they never bother to sqlite3_finalize() their PRAGMA statements. Or something. In other words, they are using sqlite3_column_count() as an indicator about whether or not the statement has content to return. The idea sqlite3_column_count() could be used to determine if the statement returns anything has never been true. "SELECT * FROM table WHERE 0" never returns any rows, but it will still give a non-zero column count. The column count is determined when the SQL is compiled into bytecode, but we often do not know if the number of rows will be zero until after the bytecode starts to execute. We are trying to get the 3.16.2 patch release out the door to fix the serious blunder described by https://www.sqlite.org/src/info/30027b613b4 but now I'm thinking we need to go back and make sure sqlite3_column_count() always returns *exactly* the same value as it did in 3.15.2, regardless of how inconsistent that value might have been, so as not to break applications that were using sqlite3_column_count() in unintended ways. That means delaying 3.16.2 until next week sometime. Maybe I should rollback the website to 3.15.2 temporarily until we can get 3.16.2 ready... -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
> On Jan 5, 2017, at 3:11 PM, Kyle wrote: > > Furthermore the PRAGMAs now seem to return a resultset whereas previously > they did not? Is this expected (or documented)? That would explain the SQLITE_BUSY error — if the pragma returns a non-empty list of rows, and you’re not reading the rows or resetting the statement, the statement stays busy and will block closing the database. For this reason, if you’re running a query it’s a good idea to reset the statement afterwards, even if you weren’t expecting it to return any rows. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
On 5 Jan 2017, at 11:30pm, Kyle wrote: > no, the database is :memory: You should not be getting SQLITE_BUSY when using a memory database. So far my guess is the same as DRH’s. Can you tell us how you are executing your SQLite functions ? Are you using the C interface or a precompiled library ? You can also download the SQLite command-line tool and try executing the same commands with that. If you get the same results with that they will be easier to investigate. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
On 1/5/17, Kyle wrote: > Dear all, > Under version 3.16.1, SQLite3Close is returning SQLITE_BUSY when I open > a database, execute some PRAGMAS, (run some stmts) then attempt the > close. This did not occur under version 3.15.2, nor does it occur under > the new version if I omit the PRAGMAs. Probably you are not talking to SQLite directly but are using some wrapper software. What is your wrapper software? > Furthermore the PRAGMAs now seem to return a resultset whereas > previously they did not? Is this expected (or documented)? > Please advise, > Thanks, > K > -- > PRAGMA encoding="UTF-8" > PRAGMA cache_size="-2" (hopefully a reasonable attempt to tell > SQLite that memory is more plentiful than default would suggest). > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
On 05/01/2017 23:14, Simon Slavin wrote: On 5 Jan 2017, at 11:11pm, Kyle wrote: Under version 3.16.1, SQLite3Close is returning SQLITE_BUSY when I open a database, execute some PRAGMAS, (run some stmts) then attempt the close. This did not occur under version 3.15.2, nor does it occur under the new version if I omit the PRAGMAs. Do other computers or processes have that database open at the same time as you’re doing this ? Simon, no, the database is :memory: but I think the same happens with on disk database. It is a change in behaviour with 3.16.1 vs 3.15.2. Furthermore the PRAGMAs now seem to return a resultset whereas previously they did not? Is this expected (or documented)? Some PRAGMAs are meant to return results. Others are not. Can you tell us one (or more) of the PRAGMAs which do this ? Pls refer my original mail. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
On 5 Jan 2017, at 11:11pm, Kyle wrote: > Under version 3.16.1, SQLite3Close is returning SQLITE_BUSY when I open a > database, execute some PRAGMAS, (run some stmts) then attempt the close. This > did not occur under version 3.15.2, nor does it occur under the new version > if I omit the PRAGMAs. Do other computers or processes have that database open at the same time as you’re doing this ? > Furthermore the PRAGMAs now seem to return a resultset whereas previously > they did not? Is this expected (or documented)? Some PRAGMAs are meant to return results. Others are not. Can you tell us one (or more) of the PRAGMAs which do this ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs
Dear all, Under version 3.16.1, SQLite3Close is returning SQLITE_BUSY when I open a database, execute some PRAGMAS, (run some stmts) then attempt the close. This did not occur under version 3.15.2, nor does it occur under the new version if I omit the PRAGMAs. Furthermore the PRAGMAs now seem to return a resultset whereas previously they did not? Is this expected (or documented)? Please advise, Thanks, K -- PRAGMA encoding="UTF-8" PRAGMA cache_size="-2" (hopefully a reasonable attempt to tell SQLite that memory is more plentiful than default would suggest). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users