C++ sqlite commit db after an update



  • QSqlDatabase db;
    
        QSqlQuery query2(db);
        query2.prepare("SELECT MAX(id_stc) FROM stc ");
        query2.exec();
        query2.next();
        index2 = query2.value(0).toInt();
        query2.clear();
    
        QSqlQuery query21(db);
        query21.prepare("UPDATE stc SET id_st=:id_st, h_on=:h_on, h_off=:h_off, err_m=:err_m, t_em=:t_em, t_pause=:t_pause, p_pro=:p_pro WHERE id_st =:id_st");
        query21.bindValue(":id_st", index2 );
        query21.bindValue(":h_on", HOn);
        query21.bindValue(":h_off", HOff);
        query21.bindValue(":err_m", errM);
        query21.bindValue(":t_em", tEme);
        query21.bindValue(":t_pause", tPa);
        query21.bindValue(":p_pro", pPro);
        query21.exec();
        query21.next();
    /*db.commit(); ... if I put the commit command here is the same*/
        query21.clear();
        db.commit();
    

    my piece of code .... it works great but if I make an insert with other void (myINSERTvoid) I can't able to make an UPDATE on real last roid .... If stop my app and run again i can see last roid and make correctly the UPDATE .... obviusly if make an INSERT statement first and an UPDATE second the problem comes out again ....

    I'm on linux ubuntu 14.04 ... with sqlite3 ... I just install all dependancy and if I insert 1 row for control driver ...

    qDebug ( )  <<  QSqlDatabase::drivers();
    

    I obtain the correct response .....

    I have some problem but not see it...

    Thanks for any helps

    Regards
    Giorgio



  • @gfxx

    commit() ends a transaction, but I can't see where you start your transaction.

    //Edit
    What do you get with db.lastError(); ?



  • This post is deleted!


  • @the_

    "Driver not loaded Driver not loaded"
    

    why?????

    Regards
    Giorgio



  • @VRonin

    Thanks for your interest .... but having a personal translator does not help my poor English ....

    Regards
    Giorgio



  • Did you deploy the driver plugin? qsqlite.dll



  • I'm on linux .....

    Regards
    Giorgio



  • Pieces of code when I open my db...

    QPluginLoader loader("/home/myhome/Qt/5.6/gcc_64/plugins/sqldrivers/libqsqlite.so");
                    loader.load();
                    qDebug() << loader.errorString(); /*1 mess*/
    
    
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    //QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE"));
                    db.setDatabaseName( "/home/myhome/Qt/BDB/mydb.sqlite" );
                     
     if( !db.open() )
                        {
                                   /*my ERROR CONNECTION messages*/
                        }
    else{
                            qDebug() << db.lastError().text();/*2 mess*/
                        }
                         qDebug() << db.lastError().text();/*3 mess*/
    
                        qDebug ( )  <<  QSqlDatabase::drivers();/*4 mess*/
    

    debug messages .....

    /*1 mess*/ "Unknown error"
    /*2 mess*/ " "
    /*3 mess*/ " "
    /*4 mess*/ ("QSQLITE", "QMYSQL", "QMYSQL3", "QPSQL", "QPSQL7")
    


  • @gfxx QSqlDatabase("QSQLITE") ist enough. No QPluginloader is needed.

    Of course, yo need to set the name of the database (with sqlite it is the filename)
    setDatabaseName("your-db-name.sqlite");
    and you need to open() the database.



  • /*QPluginLoader loader("/home/myhome/Qt/5.6/gcc_64/plugins/sqldrivers/libqsqlite.so");
                    loader.load();
                    qDebug() << loader.errorString(); */
    
    
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    //QSqlDatabase(QSqlDatabase::addDatabase("QSQLITE"));
                    db.setDatabaseName( "/home/myhome/Qt/BDB/mydb.sqlite" ); /*<---- 
    not work I've created mydb with Sqliteman ... 
    and save it without extension ... so work only if I write "/home/myhome/Qt/BDB/mydb"  */
                     
     if( !db.open() )
                        {
                                   /*my ERROR CONNECTION messages*/
                        }
    else{
                            qDebug() << db.lastError().text();/*2 mess*/
                        }
                         qDebug() << db.lastError().text();/*3 mess*/
    
                        qDebug ( )  <<  QSqlDatabase::drivers();/*4 mess*/
    
    

    with mydb.sqlite obtain these error:

    QSqlQuery::value: not positioned on a valid record
    QSqlQuery::value: not positioned on a valid record ....
    

    regards
    Giorgio



  • no new with suggested way..

    regards
    Giorgio


  • Qt Champions 2016

    @gfxx said:

    QSqlQuery::value: not positioned on a valid record ....

    That could mean you try to read from QSqlQuery before calling next/ check if it is valid.
    Please look at doc and samples how to use it correctly
    http://doc.qt.io/qt-5/qsqlquery.html

      QSqlQuery query("SELECT country FROM artist");
        while (query.next()) {
            QString country = query.value(0).toString();
            doSomething(country);
        }
    


  • 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


  • Qt Champions 2016

    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?



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



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


  • Qt Champions 2016

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



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


  • 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


  • Qt Champions 2016

    Hi
    did you also check with

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

    just to know?



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


  • Qt Champions 2016

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


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


  • Qt Champions 2016

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

    Happy coding :)


Log in to reply
 

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