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. C++ sqlite commit db after an update
Forum Updated to NodeBB v4.3 + New Features

C++ sqlite commit db after an update

Scheduled Pinned Locked Moved Solved General and Desktop
24 Posts 5 Posters 9.2k Views 2 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.
  • gfxxG Offline
    gfxxG Offline
    gfxx
    wrote on last edited by gfxx
    #13
    QSqlDatabase db;
    
        QSqlQuery query2(db);
        query2.prepare("SELECT MAX(id_stc) FROM stc ");
        query2.exec();
        query2.next();
        index2 = query2.value(0).toInt();
        query2.clear();
    db.commit();
    

    this is my query ...

    I'm sorry I can not tell the difference with this, my query ...

    regards
    Giorgio

    bkt

    1 Reply Last reply
    0
    • mrjjM Offline
      mrjjM Offline
      mrjj
      Lifetime Qt Champion
      wrote on last edited by
      #14

      well check return value of next() and exec()
      if false, there is no record.

      qDebug << "exec says:" << query2.exec();
      qDebug << "next says:" << query2.next();

      also use
      http://doc.qt.io/qt-5/qsqlquery.html#size

      to see how many u got from the SELECT.

      The code seems to lack all error handling or this is just simplified code to show?

      gfxxG 2 Replies Last reply
      1
      • mrjjM mrjj

        well check return value of next() and exec()
        if false, there is no record.

        qDebug << "exec says:" << query2.exec();
        qDebug << "next says:" << query2.next();

        also use
        http://doc.qt.io/qt-5/qsqlquery.html#size

        to see how many u got from the SELECT.

        The code seems to lack all error handling or this is just simplified code to show?

        gfxxG Offline
        gfxxG Offline
        gfxx
        wrote on last edited by gfxx
        #15

        @mrjj

        this is just simplified code to show

        I try to insert qDebug on query exec and next ... also I try to change all queryxx.clear into .finish as suggested on this doc:
        link http://doc.qt.io/qt-5/qsqlquery.html

        regards
        giorgio

        bkt

        1 Reply Last reply
        1
        • mrjjM mrjj

          well check return value of next() and exec()
          if false, there is no record.

          qDebug << "exec says:" << query2.exec();
          qDebug << "next says:" << query2.next();

          also use
          http://doc.qt.io/qt-5/qsqlquery.html#size

          to see how many u got from the SELECT.

          The code seems to lack all error handling or this is just simplified code to show?

          gfxxG Offline
          gfxxG Offline
          gfxx
          wrote on last edited by
          #16

          @mrjj

          I emphasize this: SQlite Statement is correctly executed .... but I'm unable to make "SELECT MAX(my_id_table)....." ...select the result is relative to the last program session ... INSERT the current session are not seen ...

          My code:

          void mainwindow::xxx()
          {
          
          int frq1, frq2, frq4, tm1, tm2, tm3, tm4, tm5, tm6, tm7, tm8;
          
          QSqlQuery Csetp(db);
                Csetp.prepare("SELECT fq1, fq2, fq4, t1, t2, t3, t4 ,t5, t6, t7, t8 FROM defaultdata WHERE id_default =:id_default");
                Csetp.bindValue(":id_default", programNU);
                Csetp.exec();
                Csetp.next();
                frq1 = Csetp.value(0).toInt();
                frq2 = Csetp.value(1).toInt();
                frq4 = Csetp.value(2).toInt();
                tm1 = Csetp.value(3).toInt();
                tm2 = Csetp.value(4).toInt();
                tm3 = Csetp.value(5).toInt();
                tm4 = Csetp.value(6).toInt();
                tm5 = Csetp.value(7).toInt();
                tm6 = Csetp.value(8).toInt();
                tm7 = Csetp.value(9).toInt();
                tm8 = Csetp.value(10).toInt();
                //db.commit();
                Csetp.finish();
                db.commit();
          
                qDebug() << "Csetp.exec():   " << Csetp.exec();
                qDebug() << "Csetp.next():   " << Csetp.next();
                qDebug() << "error:   " << Csetp.lastError().text();
          
          /*do somethings ... */
          }
          
          void MainWindow::modifyPgr(int NRPGR, int f1M, int f2M, int f4M, int t1M, int t2M, int t3M, int t4M, int t5M, int t6M, int t7M, int t8M)
          {
          
              QSqlDatabase db;
              QSqlQuery queryM(db);
              queryM.prepare("UPDATE defaultdata SET id_default=:id_default, fq1=:fq1, fq2=:fq2, fq4=:fq4, t1=:t1, t2=:t2, t3=:t3, t4=:t4, t5=:t5, t6=:t6, t7=:t7, t8=:t8 WHERE id_default =:id_default");
              queryM.bindValue(":id_default", NRPGR );
              queryM.bindValue(":fq1",f1M);
              queryM.bindValue(":fq2",f2M);
              queryM.bindValue(":fq4",f4M);
              queryM.bindValue(":t1",t1M);
              queryM.bindValue(":t2",t2M);
              queryM.bindValue(":t3",t3M);
              queryM.bindValue(":t4",t4M);
              queryM.bindValue(":t5",t5M);
              queryM.bindValue(":t6",t6M);
              queryM.bindValue(":t7",t7M);
              queryM.bindValue(":t8",t8M);
              queryM.exec();
              queryM.next();
              //db.commit();
              queryM.finish();
              db.commit();
              qDebug() << "queryM:  " << queryM.lastError().text();
              qDebug() <<  "queryM-db: " << db.lastError().text();
          
          /* do somethings ....*/
          }
          
          
          *************************App Output*******************
          
          queryM:   " "
          queryM-db:  "Driver not loaded Driver not loaded"
          Csetp.exec():    true
          Csetp.next():    true
          error:    " "
          Csetp.exec():    true
          Csetp.next():    true
          error:    " "
          queryM:   " "
          queryM-db:  "Driver not loaded Driver not loaded"
          

          not understand -> "Driver not loaded Driver not loaded" ... library .so is loaded ... qtcreator plugin folder contain libqsqlite .so ... in my .pro file I add targhet to plugin folder of GCC64 containing libqsqlite .so .. I've these declaration on head:

          QT       += widgets\
          network widgets \
          qml quick\
          serialbus\
          concurrent widgets\
          sql\
          core\
          xml\
          opengl\
          gui
          

          not see where is the problem ....

          Regards
          Giorgio

          bkt

          1 Reply Last reply
          0
          • mrjjM Offline
            mrjjM Offline
            mrjj
            Lifetime Qt Champion
            wrote on last edited by
            #17

            Ok, but you check in a funny way
            ...
            Csetp.bindValue(":id_default", programNU);
            Csetp.exec(); <<< where is the check here?

            later u do
            qDebug() << "Csetp.exec(): " << Csetp.exec();

            but thats another exec() so its not really solid.

            also you call db.commit(); but where is the start of it ?
            http://doc.qt.io/qt-4.8/qsqldatabase.html#commit

            but maybe it all comes from
            "Driver not loaded Driver not loaded"
            Normally , you do not link to any extern .SO files to use SQL.
            QT += SQL should be enough :)

            gfxxG 1 Reply Last reply
            1
            • mrjjM mrjj

              Ok, but you check in a funny way
              ...
              Csetp.bindValue(":id_default", programNU);
              Csetp.exec(); <<< where is the check here?

              later u do
              qDebug() << "Csetp.exec(): " << Csetp.exec();

              but thats another exec() so its not really solid.

              also you call db.commit(); but where is the start of it ?
              http://doc.qt.io/qt-4.8/qsqldatabase.html#commit

              but maybe it all comes from
              "Driver not loaded Driver not loaded"
              Normally , you do not link to any extern .SO files to use SQL.
              QT += SQL should be enough :)

              gfxxG Offline
              gfxxG Offline
              gfxx
              wrote on last edited by
              #18

              @mrjj ```
              QSqlDatabase db;
              QSqlQuery Csetp(db);
              Csetp.prepare("SELECT fq1, fq2, fq4, t1, t2, t3, t4 ,t5, t6, t7, t8 FROM defaultdata WHERE id_default =:id_default");
              Csetp.bindValue(":id_default", programNU);
              qDebug() << "Csetp.exec(): " << Csetp.exec();
              qDebug() << "Csetp.next(): " << Csetp.next();
              qDebug() << "error: " << Csetp.lastError().text();
              frq1 = Csetp.value(0).toInt();
              frq2 = Csetp.value(1).toInt();
              frq4 = Csetp.value(2).toInt();
              tm1 = Csetp.value(3).toInt();
              tm2 = Csetp.value(4).toInt();
              tm3 = Csetp.value(5).toInt();
              tm4 = Csetp.value(6).toInt();
              tm5 = Csetp.value(7).toInt();
              tm6 = Csetp.value(8).toInt();
              tm7 = Csetp.value(9).toInt();
              tm8 = Csetp.value(10).toInt();
              //db.commit();
              Csetp.finish();
              db.commit();

              *****output

              Csetp.exec(): true
              Csetp.next(): true
              error: " "

              
              you are in right ... but result not change..
              
              Regards
              Giorgio

              bkt

              1 Reply Last reply
              0
              • gfxxG Offline
                gfxxG Offline
                gfxx
                wrote on last edited by gfxx
                #19
                QSqlDatabase db;
                      QSqlQuery Csetp(db);
                      Csetp.prepare("SELECT fq1, fq2, fq4, t1, t2, t3, t4 ,t5, t6, t7, t8 FROM defaultdata WHERE id_default =:id_default");
                      Csetp.bindValue(":id_default", programNU);
                      qDebug() << "Csetp.exec():   " << Csetp.exec();
                      qDebug() << "Csetp.next():   " << Csetp.next();
                      qDebug() << "error:   " << Csetp.lastError().text();
                      frq1 = Csetp.value(0).toInt();
                      frq2 = Csetp.value(1).toInt();
                      frq4 = Csetp.value(2).toInt();
                      tm1 = Csetp.value(3).toInt();
                      tm2 = Csetp.value(4).toInt();
                      tm3 = Csetp.value(5).toInt();
                      tm4 = Csetp.value(6).toInt();
                      tm5 = Csetp.value(7).toInt();
                      tm6 = Csetp.value(8).toInt();
                      tm7 = Csetp.value(9).toInt();
                      tm8 = Csetp.value(10).toInt();
                      //db.commit();
                      Csetp.finish();
                      //db.commit();
                      qDebug() << "error db on Csetp:   " << db.lastError().text();
                
                ***********************output without commit()****************
                Csetp.exec():    true
                Csetp.next():    true
                error:    " "
                error db on Csetp:    "Driver not loaded Driver not loaded"
                

                add INCLUDEPATH at libsqlite.so is my last chance to try to solve to myself the "impossible error": "Driver not loaded Driver not loaded" ....

                but now I try community help...

                Regards
                Giorgio

                bkt

                1 Reply Last reply
                0
                • mrjjM Offline
                  mrjjM Offline
                  mrjj
                  Lifetime Qt Champion
                  wrote on last edited by
                  #20

                  Hi
                  did you also check with

                  qDebug() << "number of rows: " << Csetp.size();

                  just to know?

                  gfxxG 1 Reply Last reply
                  0
                  • mrjjM mrjj

                    Hi
                    did you also check with

                    qDebug() << "number of rows: " << Csetp.size();

                    just to know?

                    gfxxG Offline
                    gfxxG Offline
                    gfxx
                    wrote on last edited by
                    #21

                    @mrjj

                    QSqlDatabase db;
                          QSqlQuery Csetp(db);
                          Csetp.prepare("SELECT fq1, fq2, fq4, t1, t2, t3, t4 ,t5, t6, t7, t8 FROM defaultdata WHERE id_default =:id_default");
                          Csetp.bindValue(":id_default", programNU);
                          qDebug() << "Csetp.exec():   " << Csetp.exec();
                          //qDebug() << "number of rows: " << Csetp.size();
                          qDebug() << "Csetp.next():   " << Csetp.next();
                          qDebug() << "error:   " << Csetp.lastError().text();
                          //qDebug() << "number of rows: " << Csetp.size();
                          frq1 = Csetp.value(0).toInt();
                          frq2 = Csetp.value(1).toInt();
                          frq4 = Csetp.value(2).toInt();
                          tm1 = Csetp.value(3).toInt();
                          tm2 = Csetp.value(4).toInt();
                          tm3 = Csetp.value(5).toInt();
                          tm4 = Csetp.value(6).toInt();
                          tm5 = Csetp.value(7).toInt();
                          tm6 = Csetp.value(8).toInt();
                          tm7 = Csetp.value(9).toInt();
                          tm8 = Csetp.value(10).toInt();
                          qDebug() << "number of rows: " << Csetp.size();
                          //db.commit();
                          Csetp.finish();
                          //db.commit();
                          qDebug() << "error db on Csetp:   " << db.lastError().text();
                    
                    *******************output****************************
                    
                    Csetp.exec():    true
                    Csetp.next():    true
                    error:    " "
                    number of rows:  -1   /*<-------??????*/
                    error db on Csetp:    "Driver not loaded Driver not loaded"
                    

                    from doc: field 0 is forename and field 1 is surname. Using SELECT * is not recommended because the order of the fields in the query is undefined.

                    so I try with other query...

                    QSqlDatabase db;
                        QSqlQuery queryM(db);
                        queryM.prepare("UPDATE defaultdata SET id_default=:id_default, fq1=:fq1, fq2=:fq2, fq4=:fq4, t1=:t1, t2=:t2, t3=:t3, t4=:t4, t5=:t5, t6=:t6, t7=:t7, t8=:t8 WHERE id_default =:id_default");
                        queryM.bindValue(":id_default", NRPGR );
                        queryM.bindValue(":fq1",f1M);
                        queryM.bindValue(":fq2",f2M);
                        queryM.bindValue(":fq4",f4M);
                        queryM.bindValue(":t1",t1M);
                        queryM.bindValue(":t2",t2M);
                        queryM.bindValue(":t3",t3M);
                        queryM.bindValue(":t4",t4M);
                        queryM.bindValue(":t5",t5M);
                        queryM.bindValue(":t6",t6M);
                        queryM.bindValue(":t7",t7M);
                        queryM.bindValue(":t8",t8M);
                        queryM.exec();
                        qDebug() << "queryM size:  " << queryM.size();
                        queryM.next();
                        //db.commit();
                        queryM.finish();
                        db.commit();
                        qDebug() << "queryM:  " << queryM.lastError().text();
                        qDebug() <<  "queryM-db: " << db.lastError().text();
                    *******************output****************************
                    queryM size:   -1
                    queryM:   " "
                    queryM-db:  "Driver not loaded Driver not loaded"
                    

                    So it seems not see valid statement but it exec it (i control the new data in db with sqliteman gui after suthdown of my application. The result is always ok!!!!!).

                    But for me is impossible to use with success SELECT MAX(id_xxx) ...
                    and I have these terrible error "Driver not loaded Driver not loaded" (I seem to live in a film of horrors where the monster always comes out from behind the corner ...)

                    regards
                    Giorgio

                    bkt

                    1 Reply Last reply
                    0
                    • mrjjM Offline
                      mrjjM Offline
                      mrjj
                      Lifetime Qt Champion
                      wrote on last edited by mrjj
                      #22

                      @gfxx said:

                      QSqlDatabase db;

                      Make sure you dont have multiple
                      QSqlDatabase db around in code.
                      Just have 1 in class that last the life time of the application.

                      So your UPDATE works but you SELECT returns nothing.

                      • (I seem to live in a film of horrors where the monster always comes out from behind the corner ...)
                        Good news is that most of the time, the monsters die and hero survives :)
                      gfxxG 1 Reply Last reply
                      0
                      • mrjjM mrjj

                        @gfxx said:

                        QSqlDatabase db;

                        Make sure you dont have multiple
                        QSqlDatabase db around in code.
                        Just have 1 in class that last the life time of the application.

                        So your UPDATE works but you SELECT returns nothing.

                        • (I seem to live in a film of horrors where the monster always comes out from behind the corner ...)
                          Good news is that most of the time, the monsters die and hero survives :)
                        gfxxG Offline
                        gfxxG Offline
                        gfxx
                        wrote on last edited by
                        #23

                        @mrjj

                        news .... query.lasterror.text()

                        "cannot commit - no transaction is active Unable to commit transaction"
                        

                        Make sure you dont have multiple
                        QSqlDatabase db around in code.

                        eureka!!

                        I write only 4 app in qt with database ... The first is managing data from sensors ... What I have many updates and inserts, and many SELECT MAX ... other times it was enough to save the data before the shutdown .... so I never explored the use QSqldatabase ... I always put everything in mainwindows ...

                        thanks a lot

                        edit ... commit command is not necessary .... somewho it suggested my to use it in old app (3 years ago in QT4.x) ... only today I see the real work of these command ...

                        Best Regards
                        Giorgio

                        I would say that the hero is you today .... next time I hope to be me .... thanks :)

                        bkt

                        1 Reply Last reply
                        1
                        • mrjjM Offline
                          mrjjM Offline
                          mrjj
                          Lifetime Qt Champion
                          wrote on last edited by
                          #24

                          Super :)
                          To use commit, one should start with
                          bool QSqlDatabase::transaction()
                          first. Else commit is not valid.

                          Happy coding :)

                          1 Reply Last reply
                          1

                          • Login

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