RFE: allow parameters in PRAGMA statements

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

RFE: allow parameters in PRAGMA statements

wmertens
Hi,

I am using the user_version pragma for implementing an event-handling
database. I'd like to prepare the statement to update it, e.g. `PRAGMA
user_version = ?`.

However, sqlite3 won't let me do that, so I just run the text query every
time with the number embedded.

Not a huge problem, more of a surprise. Would be nice if it worked.

Wout.
_______________________________________________
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: RFE: allow parameters in PRAGMA statements

Mike King-6
I’ve got a similar issue. I user user_version to store a .net version
object I’ve serialised as an int. I convert them to/from text to get them
in and out using the pragma. It would be a nice to have for pragmas to
support parameters.

Cheers

On Tue, 11 Jun 2019 at 14:43, Wout Mertens <[hidden email]> wrote:

> Hi,
>
> I am using the user_version pragma for implementing an event-handling
> database. I'd like to prepare the statement to update it, e.g. `PRAGMA
> user_version = ?`.
>
> However, sqlite3 won't let me do that, so I just run the text query every
> time with the number embedded.
>
> Not a huge problem, more of a surprise. Would be nice if it worked.
>
> Wout.
> _______________________________________________
> 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: RFE: allow parameters in PRAGMA statements

Graham Holden
I suspect this doesn't happen because from an in-expert bit of poking,
I don't think PRAGMA commands "really" get compiled to VDBE (which is
what I believe "preparing" a statement does).

In the transcript below, although the _query_ seems to use VDBE (to
return the result in "p4", the two attempts to _set_ the value have
the same VDBE (which doesn't feature the value being set, and
according to https://www.sqlite.org/opcode.html does nothing except
immediately expire all prepared statements before halting).

(And I suspect the "query" is a red-herring: I suspect the VDBE is
generated _after_ the pragma value has been retrieved).

Graham

sqlite> .eqp full
sqlite> .ver
SQLite 3.28.0 2019-04-10 13:24:35 09435b5700a2650816ad9ffa628be5fa19da62369c30329801feb5e840463c7e
msvc-1500
sqlite> pragma defer_foreign_keys;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Int64          0     1     0     0              00  r[1]=0
2     ResultRow      1     1     0                    00  output=r[1]
3     Halt           0     0     0                    00
0
sqlite> pragma defer_foreign_keys=1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Expire         0     0     0                    00
2     Halt           0     0     0                    00
sqlite> pragma defer_foreign_keys=0;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Expire         0     0     0                    00
2     Halt           0     0     0                    00

Tuesday, June 11, 2019, 2:59:21 PM, Mike King <[hidden email]> wrote:

> I’ve got a similar issue. I user user_version to store a .net version
> object I’ve serialised as an int. I convert them to/from text to get them
> in and out using the pragma. It would be a nice to have for pragmas to
> support parameters.

> Cheers

> On Tue, 11 Jun 2019 at 14:43, Wout Mertens <[hidden email]> wrote:

>> Hi,
>>
>> I am using the user_version pragma for implementing an event-handling
>> database. I'd like to prepare the statement to update it, e.g. `PRAGMA
>> user_version = ?`.
>>
>> However, sqlite3 won't let me do that, so I just run the text query every
>> time with the number embedded.
>>
>> Not a huge problem, more of a surprise. Would be nice if it worked.
>>
>> Wout.



_______________________________________________
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: RFE: allow parameters in PRAGMA statements

Clemens Ladisch
In reply to this post by wmertens
Wout Mertens wrote:
> I am using the user_version pragma for implementing an event-handling
> database. I'd like to prepare the statement to update it, e.g. `PRAGMA
> user_version = ?`.
>
> However, sqlite3 won't let me do that, so I just run the text query every
> time with the number embedded.

<https://www.sqlite.org/pragma.html> says:
| Some pragmas take effect during the SQL compilation stage, not the
| execution stage. This means if using the C-language sqlite3_prepare(),
| sqlite3_step(), sqlite3_finalize() API (or similar in a wrapper
| interface), the pragma may run during the sqlite3_prepare() call, not
| during the sqlite3_step() call as normal SQL statements do. Or the
| pragma might run during sqlite3_step() just like normal SQL statements.
| Whether or not the pragma runs during sqlite3_prepare() or
| sqlite3_step() depends on the pragma and on the specific release of
| SQLite.

This implies that parameters are not available when some pragmas are
executed.


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: [EXTERNAL] Re: RFE: allow parameters in PRAGMA statements

Hick Gunter
In reply to this post by Graham Holden
Since setting certain pragmas may affect the SQL code generated from a statement during sqlite3_prepare(), it is a good idea to have currently prepared statements re-prepared via the expire opcode.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Graham Holden
Gesendet: Dienstag, 11. Juni 2019 17:50
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] RFE: allow parameters in PRAGMA statements

I suspect this doesn't happen because from an in-expert bit of poking, I don't think PRAGMA commands "really" get compiled to VDBE (which is what I believe "preparing" a statement does).

In the transcript below, although the _query_ seems to use VDBE (to return the result in "p4", the two attempts to _set_ the value have the same VDBE (which doesn't feature the value being set, and according to https://www.sqlite.org/opcode.html does nothing except immediately expire all prepared statements before halting).

(And I suspect the "query" is a red-herring: I suspect the VDBE is generated _after_ the pragma value has been retrieved).

Graham

sqlite> .eqp full
sqlite> .ver
SQLite 3.28.0 2019-04-10 13:24:35 09435b5700a2650816ad9ffa628be5fa19da62369c30329801feb5e840463c7e
msvc-1500
sqlite> pragma defer_foreign_keys;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Int64          0     1     0     0              00  r[1]=0
2     ResultRow      1     1     0                    00  output=r[1]
3     Halt           0     0     0                    00
0
sqlite> pragma defer_foreign_keys=1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Expire         0     0     0                    00
2     Halt           0     0     0                    00
sqlite> pragma defer_foreign_keys=0;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     1     0                    00  Start at 1
1     Expire         0     0     0                    00
2     Halt           0     0     0                    00

Tuesday, June 11, 2019, 2:59:21 PM, Mike King <[hidden email]> wrote:

> I’ve got a similar issue. I user user_version to store a .net version
> object I’ve serialised as an int. I convert them to/from text to get
> them in and out using the pragma. It would be a nice to have for
> pragmas to support parameters.

> Cheers

> On Tue, 11 Jun 2019 at 14:43, Wout Mertens <[hidden email]> wrote:

>> Hi,
>>
>> I am using the user_version pragma for implementing an event-handling
>> database. I'd like to prepare the statement to update it, e.g.
>> `PRAGMA user_version = ?`.
>>
>> However, sqlite3 won't let me do that, so I just run the text query
>> every time with the number embedded.
>>
>> Not a huge problem, more of a surprise. Would be nice if it worked.
>>
>> Wout.



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


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users