How to use SQLite's PRAGMA application_id

l3u_

Hi :-)

I'd like to use SQlite's PRAGMA application_id feature, but I get an odd error:

If I execute a query with my desired value hard-coded, the pragma call works as expected:

QSqlQuery query(m_db);
query.exec(QStringLiteral("PRAGMA application_id = 123"));

The file program shows the id correctly:

$ file some_db
some_db: SQLite 3.x database, application id 123, last written using SQLite version 3029000

But when I use a predefined variable and a bind call to insert the id, the query fails:

constexpr int APPLICATION_ID = 123;
QSqlQuery query(m_db);
query.prepare(QStringLiteral("PRAGMA application_id = ?"));
query.bindValue(0, APPLICATION_ID);
if (! query.exec()) {
    QSqlError error = query.lastError();
    qDebug() << error.databaseText() << error.driverText();
}

The output is:

"" "Wrong parameter count"

So what's wrong here?! Thanks for all help!

JonB

@l3u_
At some level the driver/SQLite parses stuff you write with prpepare/bindValue etc., and barfs if it doesn't like it, and it has limited knowledge, e.g. the space in PRAGMA application_id is probably a no-no for it :) Suggest you do it as you originally showed with an exec() of the literal desired statement, substitute the number into the literal string instead of trying to pass as a parameter.

l3u_

@JonB Is this a bug or a feature?! :-P

JonB

@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 a PRAGMA ...? 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 a CREATE TABLE, or perhaps a column name in a query, and you can't do that, for the same reason....

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!