SQLite returns result for sqlite_master but not for a user table ??????????



  • Hi all !

    This is not a new problem but answers from different places never get me a workink solution ! So...

    QT = 4.8.0
    IDE = QT Creator with MINGW
    OS = Windows XP (and I tried on Win 7 also and on Ubuntu !!!)

    The problem :

    I try to select some data in a table from a SQLite DB. If I tried to query the sqlite_master table, it does work ! However with user table named pkserial, it does not ? Why ????????

    The project file is :

    @
    #-------------------------------------------------

    Project created by QtCreator 2012-04-27T13:17:26

    #-------------------------------------------------

    QT += core gui sql

    TARGET = TestMySQL
    TEMPLATE = app

    SOURCES += main.cpp
    mainwindow.cpp

    HEADERS += mainwindow.h

    FORMS += mainwindow.ui
    @

    The code :

    @void MainWindow::toolButtonClicked()
    {
    qDebug() << "drivers :" << QSqlDatabase::drivers();
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");

    QString dbFile = "C:/Documents and Settings/Jack/My Documents/assmehari2010-v1a";
    qDebug() << dbFile << QFileInfo(dbFile).exists();
    db.setDatabaseName(dbFile);

    if (!db.open())
    {
    qDebug() << "unable to open db :" << db.lastError().text();
    return;
    }

    qDebug() << "tables :" << db.tables();
    QSqlQuery query(db);

    // Query 1
    QString sql = QLatin1String("SELECT tableName FROM pkserial");

    // Query 2
    // QString sql = QLatin1String("SELECT name FROM sqlite_master WHERE type='table' OR type='view' "
    // "UNION ALL SELECT name FROM sqlite_temp_master WHERE type='table' OR type='view'");

    if (query.exec(sql))
    {
    while(query.next())
    qDebug() << query.value(0).toString();
    } else
    qDebug() << "error :" << query.lastError().text();

    qDebug() << "End of test";
    }
    @

    When the code is executed, the result is :

    @
    Starting C:...\TestMySQL-build-desktop-Qt_Commercial_4_8_0_for_Desktop__MinGW__Debug\debug\TestMySQL.exe...
    drivers : ("QSQLITE", "QODBC3", "QODBC")
    "C:/Documents and Settings/Jack/My Documents/assmehari2010-v1a" true
    tables : ("pkserial")
    End of test
    C:...\TestMySQL-build-desktop-Qt_Commercial_4_8_0_for_Desktop__MinGW__Debug\debug\TestMySQL.exe exited with code 0
    @

    If I comment query 1 and uncomment query 2, the result is :

    @
    Starting C:...\TestMySQL-build-desktop-Qt_Commercial_4_8_0_for_Desktop__MinGW__Debug\debug\TestMySQL.exe...
    drivers : ("QSQLITE", "QODBC3", "QODBC")
    "C:/Documents and Settings/Jack/My Documents/assmehari2010-v1a" true
    tables : ("pkserial")
    "pkserial"
    End of test
    C:...\TestMySQL-build-desktop-Qt_Commercial_4_8_0_for_Desktop__MinGW__Debug\debug\TestMySQL.exe exited with code 0
    @

    You can see that line 5 shows the name of the table that was retrieved by the sql query 2 !

    Therefore, whenever I query the catalog table, it works and when I query a user table, it does not ???????

    Does anyone have a clue on what is happening ?

    Thank you !



  • The table pkserial might be empty. That wouldn't be an error: query.exec() would return true, but query.next() would return false.



  • Hi ! Thank you for your reply ! Before beginning this thread, I did tested that case and I insure that the table pkserial was not empty !

    Here is the content of that table as seen in SQLite Maestro:

    @
    tableName lastSerial fkProject
    GClassif 389 1
    GCodes 4953 1
    GExpo 3016 1
    GGravite 16 1
    GGroupeTypeActif 30 1
    GProcessus 0 1
    GProcessusTypeActif 0 1
    GProject 3 1
    GQuestions 2905 1
    @

    This table has three columns : tableName, lastSerial and fkProject. The first row gives the title and roow 2 up to the end gives each record.

    So the problem remain unresolved !

    Thank you to continue searching for a solution !



  • Solved !

    The database used to save the data has been build using SQL Maestro. In that case, the data inserted in the table is not returned when a Select is done on the table.

    If I used SQLite Expert to create the database, everything works fine, that is, the data can be selected and is accessed through the query in my Qt Application.

    Concluison : The tool used to create the database is the cause of the problem ! This means that you must take care of what tool you use to create the database. Moreover, I found that if I create (using my Qt application) a table X in a database Z created with SQL Maestro, than the data for that table X is returned as expected. But the other tables within that database Z are not !!!!!!!!!


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.