Do not get result for all sqlite pragmas via QSqlQuery

Question

I want to get the current states of sqlite pragma using Qt's QSqlQuery. But I do not get a result for some values from the program but from sqlite console. Qt version 5.2.1, Sqlite version 3.8.4.3, Windows 7, Ubuntu 12.4 LTS

Output:
application_id = "0"
auto_vacuum = "0"
automatic_index = "1"
busy_timeout = "5000"
cache_size = "2000"
cache_spill = [NoResult]
case_sensitive_like = [NoResult]

void MySqliteInfo::PrintState(QString state)
{
    printf("%s = ", state.toStdString().c_str());
    QSqlQuery query(*m_db);
    query.prepare(QString("PRAGMA %1").arg(state));
    query.exec();

    if(0 == query.size()) {
        printf("%s returns nothing\n", state.toStdString().c_str());
    }
    else {
        if(query.next()) {
            QVariant value = query.value(0);
            if(value.canConvert(QMetaType::QString)) {
                printf("\"%s\"\n", value.toString().toStdString().c_str());
            }
            else {
                printf("[UnknownDataType]\n");
            }
        }
        else {
            printf("[NoResult]\n");
        }
    }
    query.finish();
}

PrintState("application_id");
PrintState("auto_vacuum");
PrintState("automatic_index");
PrintState("busy_timeout");
PrintState("cache_size");
PrintState("cache_spill");
PrintState("case_sensitive_like");

What is the resulit of `SELECT sqlite_version()`? – CL. May 08 '14 at 18:05 — CL., May 08 '14 at 18:05
sqlite> select sqlite_version(); ---> 3.8.4.3 – Th. Thielemann May 09 '14 at 08:28 — Th. Thielemann, May 09 '14 at 08:28
Qt brings Version = 3.7.17. – Th. Thielemann May 09 '14 at 10:46 — Th. Thielemann, May 09 '14 at 10:46

score 0 · Answer 1 · answered May 08 '14 at 18:47

0

First of all, the query.size() test ist useless, since it returns -1 for every value. query.size() can only be used with SELECT queries.

Now [NoResult] can have two different meanings:

A pragma value does not exist
A pragma value is Not Set

In an example database created from another program, I observe the first case for cache_spill and the second case for case_sensitive_like. You might want to check the values using sqliteman on Ubuntu.

answered May 08 '14 at 18:47

Simon Warta

10,850
5
40
78

@Th.Thielemann This setting applies only to the current connection, not to the database file. – CL. May 09 '14 at 11:04

score 0 · Accepted Answer · answered May 09 '14 at 11:06

0

The SQLite version that you are actually using in your Qt program does not yet implement PRAGMA cache_spill.

SQLite just ignores any PRAGMA it does not recognize.

As documented, PRAGMA case_sensitive_like does not allow reading the current value.

answered May 09 '14 at 11:06

CL.

173,858
17
217
259

Th. Thielemann · Answer 3 · 2014-05-09T11:51:05.687

0

I found the solution tt least for cache_spill: The used sqlite3.exe supports pragma cache_spill but not the sqlite version comes with Qt 5.2. (3.7.17).
Qt 5.3 comes with Sqlite 3.8.4.3 - the git repro says this.

edited May 09 '14 at 11:51

answered May 09 '14 at 11:15

Th. Thielemann

2,592
1
23
38

Do not get result for all sqlite pragmas via QSqlQuery

3 Answers3