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

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&#40;dbname&#41;;
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&#40;&#41;;
}

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&#40;sqlname&#41;;
    sqlfile.open(QFile::ReadOnly);
    while (!sqlfile.atEnd())
    {
        QString sql = sqlfile.readLine();
        QSqlQuery qry(sql);
        qry.exec&#40;&#41;;
    }
    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&#40;&#41;&#41;
                            {
                                   ///do somthing  
                            }
                        else
                            {
                                QMessageBox* msg = new QMessageBox(&#41;;
                                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(&#41;;
                            }@

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:

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.

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.

clochydd

Hi SamFaye, you are welcome :)