Saving to SQLite from text edit (help pwetty pwease)

Scottish_Jason

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

clochydd

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

p3c0

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

Scottish_Jason

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

clochydd

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

Scottish_Jason

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

Scottish_Jason

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

jsulm

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

p3c0

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

Scottish_Jason

@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

p3c0

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

clochydd

@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

Scottish_Jason

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

    }

}

p3c0

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

Scottish_Jason

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

p3c0

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

Scottish_Jason

@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

p3c0

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

Scottish_Jason

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

p3c0

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