Re: [sqlite] RFE: allow parameters in PRAGMA statements

[Clemens Ladisch]

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.

 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 1 000  Start at 1
1 Int64  0 1 0 0  00  r[1]=0
2 ResultRow  1 1 000  output=r[1]
3 Halt   0 0 000
0
sqlite> pragma defer_foreign_keys=1;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 1 000  Start at 1
1 Expire 0 0 000
2 Halt   0 0 000
sqlite> pragma defer_foreign_keys=0;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 1 000  Start at 1
1 Expire 0 0 000
2 Halt   0 0 000

Tuesday, June 11, 2019, 2:59:21 PM, Mike King  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  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] RFE: allow parameters in PRAGMA statements

[Mike King]

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  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
> 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

[sqlite] RFE: allow parameters in PRAGMA statements

[Wout Mertens]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users