Re: [sqlite] SQLite3Close returns SQLITE_BUSY after executing PRAGMAs

[James K. Lowden]

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

[gwenn]

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

[Richard Hipp]

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

[gwenn]

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

[Richard Hipp]

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

[Richard Hipp]

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

[kk]

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

[Richard Hipp]

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

[Jens Alfke]

> 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

[Simon Slavin]

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

[Richard Hipp]

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

[Kyle]

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

[Simon Slavin]

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

[Kyle]

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