[resolved] How to run sql query on SQLITE with QSqlQuery?
-
I have a database created with SQLite 3. I have a sql file. In my program I browse this file and retrieves its content to create my tables. I manage to open my database and retrieve the instructions for creating tables found in the sql file. But when running the queries, my table is not created.
Here's my code:@
QSqlDatabase m_db = QSqlDatabase::addDatabase("QSQLITE");
m_db.setDatabaseName(MaBaseDeDonnées);
if( !m_db.isOpen())
{
m_db.open() ;--
if(m_db.isOpen())
{
/Création des table*/
QSqlQuery query(m_db);
QFile schemaFile("MonFichier_SQL.sql");
schemaFile.open(QFile::ReadOnly);
QStringList schemaTableList = QString(schemaFile.readAll()).split(";");
foreach(const QString schemaTable, schemaTableList)
{
QString req = schemaTable;
if(!schemaTable.trimmed().isEmpty())
{
query.exec(schemaTable);
}
}
schemaFile.close();
}
}@How can I do? Is there some thing I misse?
-
Hi, there,
here is how I do this:
@CDatabaseManager::CDatabaseManager(QObject parent) :
QObject(parent)
{
// Save pointer of CMainWindow (as QObject)
m_parent = parent;
/ Define & open db
* if the file doesn't exist, it will be created and
* populated with the table definitions
*/
m_db = QSqlDatabase::addDatabase("QSQLITE");
QString dbname = QGuiApplication::applicationDirPath();
dbname.append("/mr.sqlite");
m_db.setDatabaseName(dbname);// Let's check if the file exists QFile dbfile(dbname); bool dbmiss = false; if (!dbfile.open(QFile::ReadOnly)) { // file isn't there; set a flag to create it dbmiss = true; } if (!m_db.open()) { 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(); } if (dbmiss) { // opening the db created a plain file; now let's create the tables QString sqlname = QGuiApplication::applicationDirPath(); sqlname.append("/mr.sql"); QFile sqlfile(sqlname); sqlfile.open(QFile::ReadOnly); while (!sqlfile.atEnd()) { QString sql = sqlfile.readLine(); QSqlQuery qry(sql); qry.exec(); } sqlfile.close(); }
}
@
Works fine for me, so you might give it a try... ;)Regards
Joerg -
I bet a beer that passing a complete dml file as a query will never work.
That is why I push it line by line to the db. -
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.
-
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;@ -
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.