How to use SQLite's PRAGMA application_id
-
@l3u_
Depends whether it's at the SQLite side or not. First thing is I think the db has to state which things it accepts with?
-binding syntax, it's not just a text replacement like you might think, and it may not even allow it in aPRAGMA ...
? Or it could be a driver thing, not sure. Just as an e.g., a while ago someone tried to using binding for the table name in aCREATE TABLE
, or perhaps a column name in a query, and you can't do that, for the same reason.... -
Well okay, I already did quite some wicked stuff with that
?
syntax andbindValue
, so I thought it would simply escape the data bound to it in a proper/safe way and built a query … still, I think for this very use-case, it would be quite useful. I filed a bug about this ( https://bugreports.qt.io/browse/QTBUG-80082 ), let's see what the Trolls say … -
@l3u_ said in How to use SQLite's PRAGMA application_id:
According to the surprisingly fast answer in the bug report
When you would have waited some hours before opening the report I would have told you the problem here.
-
Hi,
Just a side note, the
PRAGMA
statement is also SQLite specific and may run at different execution stage and that may change at any point in time with an SQLite release. -
@l3u_
Although I have yet to find a list of what you can/cannotprepare
/bind
, my understanding is that it is not Qt but instead the database level/driver which does binding, e.g. SQLite C Interface. If Qt supportsbind
across all SQL drivers, I don't know if it has a "fallback" of doing the binding itself (in the driver perhaps) if there is a SQL backend which does not support it natively. -
@Christian-Ehrlicher Sorry again for bothering you with the bug report, but from an end-user's point of view, without knowing the very internals of Qt's SQL implementation, the internals of SQLite, at which point what is processed where, and without any hint in the documentation that binding values doesn't work for all queries but only for a subset of commands … it simply looked like a bug.
But at least one will possibly find this bug report if one searches for why binding values doesn't work with a QSqlQuery (or at all) now, not like when I searched why this doesn't work when I really thought it should.
-
@l3u_ said in How to use SQLite's PRAGMA application_id:
and without any hint in the documentation
See https://www.sqlite.org/pragma.html
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.
-
@Christian-Ehrlicher I meant the Qt documentation concerning binding values. WIthout further knowledge/investigstion, one could think that binding values to SQL statements does nothing else than escaping the values properly. At least this is what I thought; now I know better. I simpy didn't know that it's not Qt that handles the binding, but the underlying SQL engine.
When I now read this SQLite doc, I (think I) understand what it means. Maybe,
PRAGMA application_id
is executed when preparing it, SQLite gets that?
instead of an integer and silently ignores the query. The subsequentbindValue
call fails, as there's nothing to bind anything to, as the statement already has been executed during the prepare stage.Or whatever. After all, the quintessence is that one can't bind values to an SQLite
PRAGMA application_id
query, and it's caused by SQLite's implementation, not by a Qt. -
@l3u_ said in How to use SQLite's PRAGMA application_id:
one could think that binding values to SQL statements does nothing else than escaping the values properly
Then you should start understanding the tools you're using - in this case the sql database. Qt can't do anything for you here.
-
This post is deleted!