Saving to SQLite from text edit (help pwetty pwease)



  • Hi guys, didn't really want to have to bow down and ask for help on this one but it's simply doing my head in. I'm fairly new to Qt and C++ in general.. I'm trying to overwrite a current value in an SQLite cell and it is proving to be difficult.

        QString dbname = "sensors";
    
        QSqlDatabase db4 = QSqlDatabase::addDatabase("QSQLITE");
        db4.setDatabaseName("sensors.sqlite");
    
    
      if (db4.open())
        {
            QSqlQuery query(db4);
            int rowcount1 = ui->physicaltable->rowCount();
            int rowcount2 = ui->logicaltable->rowCount() - 1;
            if (rowcount1 > 0)
            {
            QString dp1 = ui->physicaltable->item(0,0)->text();
            QString cp1 = ui->physicaltable->item(0,1)->text();
    
            query.prepare("INSERT INTO sensors (pin1, attached_gpio) VALUES ('"+dp1+"','"+cp1+"') WHERE sensor_name = 'FC-28'  ");
            if (query.exec())
            {
                qDebug() << "saved";
    
            }
            else
            {
                    qDebug() << "error";
            }
    
    etc etc etc etc etc
    }
    }
    

    Any help greatly appreciated



  • @Scottish_Jason
    Hi, and welcome to the DevNet!
    I suggest to firstly test, if dp1 and cp1 contains the data you expect.
    Then you may use binding to prepare your query that way:

    query.prepare("INSERT INTO sensors (pin1, attached_gpio) 
                   VALUES (:dp, :cp);");
            }
            query.bindValue(":dp", dp1);
            query.bindValue(":cp", cp1 );
    

  • Moderators

    @Scottish_Jason Since you are trying to overwrite the existing value shouldn't you use update instead of insert query ?



  • @clochydd Hi there and thanks very much for the input. I have already tried this approach via reading online without any success.

    QString dp1 = ui->physicaltable->item(0,0)->text();
        QString cp1 = ui->physicaltable->item(0,1)->text();
    
        query.prepare("INSERT INTO sensors (pin1, attached_gpio) VALUES (:dp, :cp);");
    
                query.bindValue(":dp", dp1);
                query.bindValue(":cp", cp1 );
    

    if I qDebug() on dp1 and cp1 I do get the correct values. it is just not amending the sql cells. I also only want to input WHERE sensor_name = 'FC-28'

    or well actually whatever device I have selected on another table (but that;s for another head scratching day).. I will settle getting the datainto the FC-28 sensor cells for now :)



  • @Scottish_Jason
    Hi, according to p3c0s suggestion you may update your record that way:

    query.prepare("UPDATE sensors SET pin1 = :dp, attached_gpio = :cp 
                   WHERE sensor_name = 'FC-28';");
    query.bindValue(":dp", dp1);
    query.bindValue(":cp", cp1 );
    


  • @p3c0 Hi p3C0

    I did in fact try using the update command previously and also struggled to achieve what I was after. I did at one point manage to pass "cp1" text rather than the actual variable somehow but I lost the code and couldn't recreate scenario!

    could you possibly give me a little example of it's use under my scenario? I usually try and figure these things out for myself but i'm really scratching my head at this one as lots of material I have read says the things I have tried should work..... It's a Qt 5.5 application and I'm running windows 10.

    I am connecting to the database as so: ( a database connection has already been opened to display this sensor data in tables if that matters any - it just appears to close the exisiting one and create a new one without error )

    QSqlDatabase db4 = QSqlDatabase::addDatabase("QSQLITE");
    db4.setDatabaseName("sensors.sqlite");

    Perhaps I should try another SQL driver? not that I know much about that.



  • @clochydd Hi

    I have actually already tried this approach also without success.
    I just tried again with your code and it still never worked.

    I'm really scratching my head. qDebug show's the appropriate values that I have typed into the table cells but it just simply does not split them out to the database. I am running DB Browser for SQLite on my 2nd monitor to monitor for any changes and none are happening. It's a mystery!

    [if (db4.open())
    {
    QSqlQuery query(db4);
    int rowcount1 = ui->physicaltable->rowCount();
    int rowcount2 = ui->logicaltable->rowCount() - 1;
    if (rowcount1 > 0)
    {
    QString dp1 = ui->physicaltable->item(0,0)->text();
    QString cp1 = ui->physicaltable->item(0,1)->text();
    query.prepare("UPDATE sensors SET pin1 = :dp, attached_gpio = :cp WHERE sensor_name = 'FC-28';");
    query.bindValue(":dp", dp1);
    query.bindValue(":cp", cp1 );
    query.exec();
    qDebug() << dp1 << " " << cp1;
    }
    }


  • Moderators

    Is there already an entry in sensors table with sensor_name = 'FC-28'?
    If not the update will fail.


  • Moderators

    @Scottish_Jason Did you try the query suggested by @clochydd ? It should work.

    I am connecting to the database as so: ( a database connection has already been opened to display this sensor data in tables if that matters any - it just appears to close the exisiting one and create a new one without error )

    Well in that case just don't add new database connection. Let it use the default one.



  • @jsulm

    there is only one entry called "FC-28" under the "sensor_name" column
    I am attempting to update another column connected to that sensor (pin1) and update it


  • Moderators

    @Scottish_Jason Use QSqlQuery::lastError to check for errors if any.



  • @Scottish_Jason You can open your database with another connection:

    db4 = QSqlDatabase::addDatabase("QSQLITE","xsql"); // adds connection xsql
    db4.setDatabaseName (fullpath + "sensors.sqlite");
    if(!db4.open ()){
        qDebug() << db4.lastError().text();
    }
    

    Provide the full path to your .sqlite to make sure it's accessable



  • @p3c0 - p3c0 the error returns "true" ??? The application also locks up and / or crashes

    if (db4.open())
    {
        QSqlQuery query(db4);
        int rowcount1 = ui->physicaltable->rowCount();
        int rowcount2 = ui->logicaltable->rowCount() - 1;
        if (rowcount1 > 0)
        {
        QString dp1 = ui->physicaltable->item(0,0)->text();
        QString cp1 = ui->physicaltable->item(0,1)->text();
    
        query.prepare("UPDATE sensors SET pin1 = :dp, attached_gpio = :cp WHERE sensor_name = 'FC-28';");
        query.bindValue(":dp", dp1);
        query.bindValue(":cp", cp1 );
        if (query.exec())
             {
            qDebug() << "inserted";
             }
             else
            {
            qDebug() << QSqlQuery::lastError;
            }
    
        }
    

    }


  • Moderators

    @Scottish_Jason No. You didn't use it properly. I posted it like that just for the sake of that link. Try :

    query.exec()
    ...
    qDebug() << query.lastError() << query.lastError().text();
    //Also some other useful methods
    qDebug() << query.lastQuery();
    qDebug() << query.executedQuery();
    


  • @p3c0

    QSqlError("5", "Unable to fetch row", "database is locked") "database is locked Unable to fetch row"

    I am unsure how to handle databases... so far I have been opening and closing them on each function since I can't call db.whatever if I have not initialized it, or the lack of it being a global variable. Is this my issue?


  • Moderators

    @Scottish_Jason Don't do that. Instead open the connection only once. QSqlQuery will always use this default connection.



  • @p3c0

    When I take out the secondary QSqlQuery query(db) lines I always end up with query was not declared. How do I initialize the database per function? it has always confused me until now


  • Moderators

    @Scottish_Jason Check this example specifically the createConnection method. Modify it as per your need.



  • @p3c0 Thanks, but what if I want to use the same database throughout many functions?
    how do I pass the db variable to open?


  • Moderators

    @Scottish_Jason That is what is default connection. It is available across all functions or classes. Just use QSqlQuery directly or you can also try

    QSqlQuery query(QSqlDatabase::database())
    

    in this case it will return the default one.



  • @p3c0

    well guys, thanks for the help but I think I really need to hang up the hat on this one... no matter how I try to initialize the database on a second function ( or lack of initilization) I either get database not open or duplicate database open that causes instability and crashes.

    I have been up all night trying to get it working so maybe I'm just tired but I'm starting to think this might be a bit over my head. Thanks for the help guys


  • Moderators

    @Scottish_Jason I would suggest you to start from some simple examples keeping the earlier example code in mind ofcourse after some sleep. It works :)



  • @p3c0

    The strange thing is I am only implementing this block once in my loadsensors() function

    QString dbname = "sensors";
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("sensors.sqlite");
    QSqlQuery query(QSqlDatabase::database());
    

    and then just QSqlQuery query; in the following functions but it still insists that the database is locked.
    ( I need query defined so I can do the prepare's etc on each function )

    I probably sound awfully silly right now but I'm kinda struggling coming from a php mainly background to this. Any hints down the correct path would be appreciated



  • @Scottish_Jason
    Hi, what happens, if you declare your query that way:

    QSqlQuery query;
    ...
    query = QSqlQuery(db);
    


  • @Scottish_Jason

    You are using DB Browser for SQLite parallel - is there a chance that it locks your database?



  • @clochydd said:

    @Scottish_Jason

    You are using DB Browser for SQLite parallel - is there a chance that it locks your database?

    YAYYY thanks so much.
    the browser itself was the culprit. Values are being updated now.

    Thank you all +reps

    one last question if I may:

    instead of dropping the data into the 'FC-28' column I would like it to drop it under the name of whatever is selected on another table, any help in that direction?

    query.prepare("UPDATE sensors SET pin1 = :dp, attached_gpio = :cp WHERE sensor_name = "selected item on sensor_table");
    query.bindValue(":dp", dp1);
    query.bindValue(":cp", cp1 );


  • Moderators

    @Scottish_Jason Do it in the same way which you did for the other placeholders viz. for eg. :dp and :cp in your example.



  • @Scottish_Jason
    Hi Jason, may look like:

    QString mySearch;
    ...
    mySearch = "FC-28";
    query.prepare("UPDATE sensors SET pin1 = :dp, attached_gpio = :cp WHERE sensor_name = :search;");
    query.bindValue(":dp", dp1);
    query.bindValue(":cp", cp1 );
    query.bindValue(":search", mySearch );
    

Log in to reply
 

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