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 = appSOURCES += main.cpp
mainwindow.cppHEADERS += 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 !!!!!!!!!