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.2k 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.
  • C Offline
    C Offline
    clochydd
    wrote on last edited by
    #7

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

    1 Reply Last reply
    0
    • 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