[resolved] How to run sql query on SQLITE with QSqlQuery?
-
I've test the code posted by Joerg Pauly. But I have the same thinks. I even put the request like this @ QSqlQuery qry("CREATE TABLE
MyTable
(idTab
integer PRIMARY KEY,field1
text,field2
text,field3
boolean);"); @, but nothing works.An when I have a look at the QSqlError, I have the following information:
@driverError = ""
databaseError = ""
errorType = NoError
errorNumber = -1@And when I click on the detail button, I have nothing.
-
What do you do after
@
QSqlQuery qry("CREATE TABLEMyTable
(idTab
integer PRIMARY KEY,field1
text,field2
text,field3
boolean);");@
Do you ask for qry.lastError immediately after qry.exec()? -
As Clochydd said:
You need to catch qry.lastError() immediatly after qry.exec().
If you did so, your QSqlError-Object tells you everything went well.Let's clearify some things:
- What Qt-Version are you using? My code compiles against Qt 5.2.1.
- What OS are you using? My code runs pretty good on Linux and Win7-64.
-
her's my code
@QSqlQuery qry("CREATE TABLEMyTable
(idTab
integer PRIMARY KEY,field1
text,field2
text,field3
boolean);");if(qry.exec()) { ///do somthing } else { QMessageBox* msg = new QMessageBox(); msg->setText("Fehler beim Öffnen der Datenbank!"); msg->setInformativeText("Datenbank lässt sich nicht öffnen!"); msg->setIcon(QMessageBox::Critical); QSqlError lError; lError = m_db.lastError(); msg->setDetailedText(lError.text()); msg->exec(); }@
I'm using Qt 4.8.5.
I'm using windows7 32bits with Visual studio 2008.NB: When I place DatabaseError in the sub node data I have this message: @CXX0030: Error: expression cannot be evaluated@
-
Are you sure, the table has not been created?
I test my queries like that:
@
if (!qry.exec()) { // if not successful:
QMessageBox::warning(this, "App", "Error executing query!\n" + qry.lastError().text(), QMessageBox::Ok);
} else {
//do something eg.:
while (qry.next()) {
qDebug() << qry.value(0).toString());
}
}
@EDIT:
Try to verify your query with: @qDebug() << qry;@ -
It seem the tables are already created. I have the message @Table "MyTable" already existe@
But when I connect to the database with SQliteManager, I don't see the tables
How can I show the names of tables in the QMessagbox?
-
You may use this:
@
QStringList QSqlDatabase::tables(QSql::TableType type = QSql::Tables) const
@
Returns a list of the database's tables, system tables and views, as specified by the parameter type. -
Hi
This is what I do to create and show the tables of my database:
@QSqlDatabase m_db = QSqlDatabase::addDatabase("QSQLITE");
m_db.setDatabaseName(ai_dbName);
if(!m_db.isOpen())
{
m_db.open();
}
if(m_db.isOpen())
{
/Création des table*/
QSqlQuery query(m_db);
QFile schemaFile("myFile_SQl.sql");
schemaFile.open(QFile::ReadOnly);
QStringList schemaTableList = QString(schemaFile.readAll()).split(";");
foreach(const QString schemaTable, schemaTableList)
{
QString req = schemaTable;
if(!schemaTable.trimmed().isEmpty())
{
if(!query.exec(schemaTable))
{
QMessageBox* msg = new QMessageBox();
msg->setText("Une erreur est survenue lors de l'exécution de a requête!\n");
msg->setInformativeText("Error executing query!\n" + query.lastError().text());
msg->setIcon(QMessageBox::Critical);
msg->exec();
}
}
}
schemaFile.close();
}
closeDB();
m_myQsettings->setValue("firstExecution", true);
}
//========= To SHOW TABLES ============
else
{
QStringList myTables = m_db.tables(QSql::Tables);
foreach(const QString table, myTables)
{
QMessageBox* msg = new QMessageBox();
msg->setText("Affichage de la table "+table);
msg->setInformativeText("Error executing query!\n" + table);
msg->setIcon(QMessageBox::Critical);
msg->exec();
}
}@In the variable "myTables", there is no tables. It's emplty.
NB: Is it necessary to call the commit method after executing the creation requests?? or what can I do else???
-
Hi SamFaye,
some questions:- do you close m_db in line 31?
- can you check (messagebox or qDebug) if the code following line 17 is executed
- you stated in a previous post, that your tables exist. You should add the databasename to that message.
Maybe you work with two databases?
EDIT: Its not necessary to use COMMIT if you did not start your transaction with BEGIN.
-
@1. do you close m_db in line 31?@
Yes I close the database at this line.@2. can you check (messagebox or qDebug) if the code following line 17 is executed@
Ok , I will do it soon.@you stated in a previous post, that your tables exist.@
In fact when I display the content of query.lastError().text(), during a second execution, it say that the tables are already exist.@You should add the databasename to that message. @
Ok I'll add it tell you after.@Maybe you work with two databases?@
I've define a variable whitch content the name of my database like this@#define ai_dbName "Name_of_myDatabase"@I test and tell you. Thanks
-
Now I'm able to list the tables whitch are created. I forgot to open the database in the part listing the tables. Here's my code for that part:
@//========= To SHOW TABLES ============
else
{
if(!m_db.isOpen())
{
m_db.open();
}
QStringList myTables = m_db.tables(QSql::Tables);
foreach(const QString table, myTables)
{
QMessageBox* msg = new QMessageBox();
msg->setText("Affichage de la table "+table);
msg->setInformativeText("Error executing query!\n" + table);
msg->setIcon(QMessageBox::Critical);
msg->exec();
}
closeDB();
}@But now my problem is, when I open the database with Sqlitmanager (on windows), or sqliteman(on linux), there are any table at the 'Table' node. The database is empty.
-
You mean, there is NOT any table with sqliteman, don't you?
Are you sure, that you are not working with two databases - maybe a In-Memory-db? -
When I add m_db.databaseName at the message displayed in the QMessageBox, I have the same name whitch is mydatabase.sqlite. But before I used this database, I used an other database. I have change the value of the variable #define ai_dbName.
Here is the code.
Before
@#define ai_dbName "dbmyproject.db"@Now
@#define ai_dbName "mydatabase.sqlite"@How can I flush the memory-db??
-
I haven't worked with in-memory-db yet, but AFIK the memory-db is deleted when you close the connection "SQLite":https://www.sqlite.org/inmemorydb.html.
But anyhow I think your problem results now from the use of more than one db.
I would analyse every step of opening and creating/inserting with the full db-name to find out where the new tables are created. -
Hi Clochydd
Now my problem is revolved. I opened the rong database file. Thank you a lot and to all the guys who helped me. -
Hi SamFaye, you are welcome :)