[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



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



  • 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.



  • Thanks. I will test and tell you after



  • 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 TABLE MyTable (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 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@



  • 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:

    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.



  • @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 :)


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.