SQLite3 and "pragma application_id"

[Robert Hairgrove]

I am trying to programmatically determine whether a SQLite database was created by my application or not. There is a feature in SQLite: "PRAGMA application_id". When I create the database file using QSqlDatabase etc., I set this value to a magic number, and if I open the database in the sqlite3 console, I get the correct value returned when I use this command.

However, when I issue "PRAGMA application_id" as a query in QSqlQuery, there is a different value returned entirely. It is documented that the value is stored in a four-byte range starting at offset 68, so I can easily use the standard file I/O operations to check it. However, I am wondering why the query is not returning the value correctly?

[SGaist]

Hi,

Can you show the code you are using to handle your database and application id ?

[Robert Hairgrove]

Hello,
Here is the code I use to set the application_id:

    // assume that db is a valid and open QSqlDatabase object:
    sql = "PRAGMA application_id=%1";
    // this can be any 32-bit signed integer value,
    // according to the SQLite documentation.
    // In my code it is defined as 1,396,851,276 which is within the range:
    sql = sql.arg(SBBL_MAGIC_NUMBER);
    db.exec(sql);

When I open the file in the sqlite3 terminal app, I see this same number. But when I run the query like this:

    bool own_app_db = false;
    sql = "PRAGMA application_id";
    if ((retval = Q.exec())) {
      while (Q.next()) {
        bool ok = false;
        int magic_number = Q.value(0).toInt(&ok);
        own_app_db = (magic_number == SBBL_MAGIC_NUMBER);
        retval = own_app_db;
        if (!own_app_db) {
          erm = tr("The SQLite database was not an SBBL database.");
        }
      }
    }

In the debugger, I get the value 153 and see that "ok" is set to "true". Perhaps "PRAGMA" commands return values differently than regular queries?

Thanks for looking into this!

[Robert Hairgrove]

Oops, now I see that I forgot to give the query the SQL statement entirely! Still it is strange that there was no error...?

Anyhow, it is now working!

(Grüezi nach Sion ... ich bin in Zürich, und bald in Winterthur)