Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Get Qt Extensions
  • Unsolved
Collapse
Brand Logo
  1. Home
  2. Qt Development
  3. General and Desktop
  4. [resolved] How to run sql query on SQLITE with QSqlQuery?
Forum Updated to NodeBB v4.3 + New Features

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

Scheduled Pinned Locked Moved General and Desktop
21 Posts 3 Posters 8.0k Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • J Offline
    J Offline
    joergpauly
    wrote on last edited by
    #8

    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.
    1 Reply Last reply
    0
    • S Offline
      S Offline
      SamFaye
      wrote on last edited by
      #9

      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@

      1 Reply Last reply
      0
      • C Offline
        C Offline
        clochydd
        wrote on last edited by
        #10

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

        1 Reply Last reply
        0
        • S Offline
          S Offline
          SamFaye
          wrote on last edited by
          #11

          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?

          1 Reply Last reply
          0
          • C Offline
            C Offline
            clochydd
            wrote on last edited by
            #12

            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.

            1 Reply Last reply
            0
            • S Offline
              S Offline
              SamFaye
              wrote on last edited by
              #13

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

              1 Reply Last reply
              0
              • C Offline
                C Offline
                clochydd
                wrote on last edited by
                #14

                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 Reply Last reply
                0
                • S Offline
                  S Offline
                  SamFaye
                  wrote on last edited by
                  #15

                  @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

                  1 Reply Last reply
                  0
                  • S Offline
                    S Offline
                    SamFaye
                    wrote on last edited by
                    #16

                    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.

                    1 Reply Last reply
                    0
                    • C Offline
                      C Offline
                      clochydd
                      wrote on last edited by
                      #17

                      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?

                      1 Reply Last reply
                      0
                      • S Offline
                        S Offline
                        SamFaye
                        wrote on last edited by
                        #18

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

                        1 Reply Last reply
                        0
                        • C Offline
                          C Offline
                          clochydd
                          wrote on last edited by
                          #19

                          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.

                          1 Reply Last reply
                          0
                          • S Offline
                            S Offline
                            SamFaye
                            wrote on last edited by
                            #20

                            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.

                            1 Reply Last reply
                            0
                            • C Offline
                              C Offline
                              clochydd
                              wrote on last edited by
                              #21

                              Hi SamFaye, you are welcome :)

                              1 Reply Last reply
                              0

                              • Login

                              • Login or register to search.
                              • First post
                                Last post
                              0
                              • Categories
                              • Recent
                              • Tags
                              • Popular
                              • Users
                              • Groups
                              • Search
                              • Get Qt Extensions
                              • Unsolved