[resolved] How to run sql query on SQLITE with QSqlQuery?
-
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.
-
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.