How to use SQLite's PRAGMA application_id

[l3u_]

Well okay, I already did quite some wicked stuff with that ? syntax and bindValue, 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 …

[JonB]

@l3u_
What output do you get with:

query.prepare(QStringLiteral("NONSENSE piffle_bottom = ?"));

?

[l3u_]

@JonB According to the surprisingly fast answer in the bug report, it's already the prepare statement that fails. I actually didn't check for this … so seems that SQLite doesn't like prepared statements in a PRAGMA call.

[Christian Ehrlicher]

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

[SGaist]

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.

[JonB]

@l3u_
Although I have yet to find a list of what you can/cannot prepare/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 supports bind 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.

[l3u_]

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

[Christian Ehrlicher]

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

[l3u_]

@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 subsequent bindValue 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.

[Christian Ehrlicher]

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

[l3u_]

This post is deleted!