Can SQLite know from the statement string if it is row producing or not?

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Can SQLite know from the statement string if it is row producing or not?

Bart Smissaert
I am interested to know from the statement string if the statement is
invalid, row producing (could produce rows) or non row producing. I know
sqlite3_prepare16_v2 can see if the statement is valid or not but how about
the other 2?
I can do this in code no problem, but it may not always be 100% reliable
for example there
could be a new pragma or maybe some new SQL keyword.

RBS
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite know from the statement string if it is row producing or not?

Luuk


On 08-01-16 21:27, Bart Smissaert wrote:
> I am interested to know from the statement string if the statement is
> invalid, row producing (could produce rows) or non row producing. I know
> sqlite3_prepare16_v2 can see if the statement is valid or not but how about
> the other 2?
> I can do this in code no problem, but it may not always be 100% reliable
> for example there
> could be a new pragma or maybe some new SQL keyword.
>
>


What is you exact definition of 'row_producing' and 'non_row_producing'?

Will this statement be 'row_producing' or 'no_row_producing' or ...?:
INSERT INTO table1 VALUES(1);
if table1 is defined as 'CREATE TABLE table1(i INTEGER PRIMARY KEY);'

how does this change if the table was defined as:
CREATE TABLE table1(i);




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite know from the statement string if it is row producing or not?

Bart Smissaert
With row producing I mean you will get return values, so mainly select
statements, but also certain pragma's and for example
the explain statement. In my thinking I would classify INSERT INTO table1
VALUES(1) as non row producing.

RBS

On Sat, Jan 9, 2016 at 9:45 AM, Luuk <[hidden email]> wrote:

>
>
> On 08-01-16 21:27, Bart Smissaert wrote:
>
>> I am interested to know from the statement string if the statement is
>> invalid, row producing (could produce rows) or non row producing. I know
>> sqlite3_prepare16_v2 can see if the statement is valid or not but how
>> about
>> the other 2?
>> I can do this in code no problem, but it may not always be 100% reliable
>> for example there
>> could be a new pragma or maybe some new SQL keyword.
>>
>>
>>
>
> What is you exact definition of 'row_producing' and 'non_row_producing'?
>
> Will this statement be 'row_producing' or 'no_row_producing' or ...?:
> INSERT INTO table1 VALUES(1);
> if table1 is defined as 'CREATE TABLE table1(i INTEGER PRIMARY KEY);'
>
> how does this change if the table was defined as:
> CREATE TABLE table1(i);
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite know from the statement string if it is row producing or not?

Clemens Ladisch
In reply to this post by Bart Smissaert
Bart Smissaert wrote:
> I am interested to know from the statement string if the statement is
> invalid, row producing (could produce rows) or non row producing.

<http://www.sqlite.org/c3ref/column_count.html> says about
sqlite3_column_count():
| Return the number of columns in the result set returned by the
| prepared statement. This routine returns 0 if pStmt is an SQL
| statement that does not return data (for example an UPDATE).


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite know from the statement string if it is row producing or not?

Bart Smissaert
> sqlite3_column_count

Ah, yes that looks good, will try that one.

RBS

On Sat, Jan 9, 2016 at 11:30 AM, Clemens Ladisch <[hidden email]> wrote:

> Bart Smissaert wrote:
> > I am interested to know from the statement string if the statement is
> > invalid, row producing (could produce rows) or non row producing.
>
> <http://www.sqlite.org/c3ref/column_count.html> says about
> sqlite3_column_count():
> | Return the number of columns in the result set returned by the
> | prepared statement. This routine returns 0 if pStmt is an SQL
> | statement that does not return data (for example an UPDATE).
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite know from the statement string if it is row producing or not?

R Smith
In reply to this post by Clemens Ladisch


On 2016/01/09 1:30 PM, Clemens Ladisch wrote:
> Bart Smissaert wrote:
>> I am interested to know from the statement string if the statement is
>> invalid, row producing (could produce rows) or non row producing.
> <http://www.sqlite.org/c3ref/column_count.html> says about
> sqlite3_column_count():
> | Return the number of columns in the result set returned by the
> | prepared statement. This routine returns 0 if pStmt is an SQL
> | statement that does not return data (for example an UPDATE).

Indeed, but unless I am mistaken, that value is not available (in a
meaningful sense anyway) upon compilation of the SQL statement, aka
sqlite3_prepare(), but only after at least one sqlite3_step() occurred.

It seems to me the OP needs to know whether output rows will happen
BEFORE stepping. (I don't see a reason why or argue for it, just
relaying what I think the OP intended with the question).

It might be useful, some Pragmas (for instance) return values, some
don't, and this may change in future, etc. For myself though, knowing
whether output is produced or not is perfectly fine after stepping - and
for that simply checking the return values from the sqlite functions is
enough. See:
https://www.sqlite.org/c3ref/step.html


Cheers,
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite know from the statement string if it is row producing or not?

Bart Smissaert
> but only after at least one sqlite3_step() occurred.

It will work before any step, but indeed the statement needs to be prepared.
I need to know before stepping, but it can do that.

RBS

On Sat, Jan 9, 2016 at 12:57 PM, R Smith <[hidden email]> wrote:

>
>
> On 2016/01/09 1:30 PM, Clemens Ladisch wrote:
>
>> Bart Smissaert wrote:
>>
>>> I am interested to know from the statement string if the statement is
>>> invalid, row producing (could produce rows) or non row producing.
>>>
>> <http://www.sqlite.org/c3ref/column_count.html> says about
>> sqlite3_column_count():
>> | Return the number of columns in the result set returned by the
>> | prepared statement. This routine returns 0 if pStmt is an SQL
>> | statement that does not return data (for example an UPDATE).
>>
>
> Indeed, but unless I am mistaken, that value is not available (in a
> meaningful sense anyway) upon compilation of the SQL statement, aka
> sqlite3_prepare(), but only after at least one sqlite3_step() occurred.
>
> It seems to me the OP needs to know whether output rows will happen BEFORE
> stepping. (I don't see a reason why or argue for it, just relaying what I
> think the OP intended with the question).
>
> It might be useful, some Pragmas (for instance) return values, some don't,
> and this may change in future, etc. For myself though, knowing whether
> output is produced or not is perfectly fine after stepping - and for that
> simply checking the return values from the sqlite functions is enough. See:
> https://www.sqlite.org/c3ref/step.html
>
>
> Cheers,
> Ryan
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite know from the statement string if it is row producing or not?

Simon Slavin-3

On 9 Jan 2016, at 1:18pm, Bart Smissaert <[hidden email]> wrote:

>> but only after at least one sqlite3_step() occurred.
>
> It will work before any step, but indeed the statement needs to be prepared.
> I need to know before stepping, but it can do that.

For a SELECT which returns no rows I presume it returns the number of columns asked for.  Presumably this is something the original poster wants.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite know from the statement string if it is row producing or not?

Stephan Beal-3
On Sat, Jan 9, 2016 at 5:57 PM, Simon Slavin <[hidden email]> wrote:

>
> On 9 Jan 2016, at 1:18pm, Bart Smissaert <[hidden email]> wrote:
>
> >> but only after at least one sqlite3_step() occurred.
> >
> > It will work before any step, but indeed the statement needs to be
> prepared.
> > I need to know before stepping, but it can do that.
>
> For a SELECT which returns no rows I presume it returns the number of
> columns asked for.


It does. i use this in a db abstraction layer to fetch column names without
needing to fetch data.

--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite know from the statement string if it is row producing or not?

Bart Smissaert
In reply to this post by Simon Slavin-3
> Presumably this is something the original poster wants.

Yes, need to know if the statement >could< produce rows.

RBS

On Sat, Jan 9, 2016 at 4:57 PM, Simon Slavin <[hidden email]> wrote:

>
> On 9 Jan 2016, at 1:18pm, Bart Smissaert <[hidden email]> wrote:
>
> >> but only after at least one sqlite3_step() occurred.
> >
> > It will work before any step, but indeed the statement needs to be
> prepared.
> > I need to know before stepping, but it can do that.
>
> For a SELECT which returns no rows I presume it returns the number of
> columns asked for.  Presumably this is something the original poster wants.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite know from the statement string if it is row producing or not?

Stayros Mpampis


On 09/01/2016 07:11 μμ, Bart Smissaert wrote:

>> Presumably this is something the original poster wants.
> Yes, need to know if the statement >could< produce rows.
>
> RBS
>
> On Sat, Jan 9, 2016 at 4:57 PM, Simon Slavin <[hidden email]> wrote:
>
>> On 9 Jan 2016, at 1:18pm, Bart Smissaert <[hidden email]> wrote:
>>
>>>> but only after at least one sqlite3_step() occurred.
>>> It will work before any step, but indeed the statement needs to be
>> prepared.
>>> I need to know before stepping, but it can do that.
>> For a SELECT which returns no rows I presume it returns the number of
>> columns asked for.  Presumably this is something the original poster wants.
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite know from the statement string if it is row producing or not?

Hick Gunter
In reply to this post by Bart Smissaert
You may also like to consider sqlite3_stmt_readonly() which will return TRUE for all statments that do not (directly) modify the db contents

-----Ursprüngliche Nachricht-----
Von: [hidden email] [mailto:[hidden email]] Im Auftrag von Bart Smissaert
Gesendet: Freitag, 08. Jänner 2016 21:28
An: General Discussion of SQLite Database
Betreff: [sqlite] Can SQLite know from the statement string if it is row producing or not?

I am interested to know from the statement string if the statement is invalid, row producing (could produce rows) or non row producing. I know
sqlite3_prepare16_v2 can see if the statement is valid or not but how about the other 2?
I can do this in code no problem, but it may not always be 100% reliable for example there could be a new pragma or maybe some new SQL keyword.

RBS
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can SQLite know from the statement string if it is row producing or not?

Bart Smissaert
Thanks, will look at that one and see how it compares to using
sqlite3_column_count.

RBS

On Mon, Jan 11, 2016 at 7:17 AM, Hick Gunter <[hidden email]> wrote:

> You may also like to consider sqlite3_stmt_readonly() which will return
> TRUE for all statments that do not (directly) modify the db contents
>
> -----Ursprüngliche Nachricht-----
> Von: [hidden email] [mailto:
> [hidden email]] Im Auftrag von Bart
> Smissaert
> Gesendet: Freitag, 08. Jänner 2016 21:28
> An: General Discussion of SQLite Database
> Betreff: [sqlite] Can SQLite know from the statement string if it is row
> producing or not?
>
> I am interested to know from the statement string if the statement is
> invalid, row producing (could produce rows) or non row producing. I know
> sqlite3_prepare16_v2 can see if the statement is valid or not but how
> about the other 2?
> I can do this in code no problem, but it may not always be 100% reliable
> for example there could be a new pragma or maybe some new SQL keyword.
>
> RBS
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: [hidden email]
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users