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

[max2g]

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 !

[alexisdm]

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

[max2g]

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 !

[max2g]

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 !!!!!!!!!