[resolved] How to run sql query on SQLITE with QSqlQuery?

[SamFaye]

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?

[joergpauly]

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

[clochydd]

Hi, I would qDebug() the "schemaTable" and - if it shows what you expect - look at query.lastError.

[joergpauly]

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.

[SamFaye]

Thanks. I will test and tell you after

[SamFaye]

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.

[clochydd]

What do you do after
@
QSqlQuery qry("CREATE TABLE MyTable (idTab integer PRIMARY KEY,field1 text,field2 text,field3 boolean);");

@
Do you ask for qry.lastError immediately after qry.exec()?

[joergpauly]

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.

[SamFaye]

her's my code
@QSqlQuery qry("CREATE TABLE MyTable (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@

[clochydd]

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;@

[SamFaye]

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?

[clochydd]

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.

[SamFaye]

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

[clochydd]

Hi SamFaye,
some questions:

1. do you close m_db in line 31?
2. can you check (messagebox or qDebug) if the code following line 17 is executed
3. 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.

[SamFaye]

@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

[SamFaye]

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.

[clochydd]

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?

[SamFaye]

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

[clochydd]

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.

[SamFaye]

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.