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:[email protected]] Im Auftrag von Graham Holden Gesendet: Dienstag, 11. Juni 2019 17:50 An: SQLite mailing list <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 [email protected] 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
