Saving to SQLite from text edit (help pwetty pwease)

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

[Scottish_Jason]

@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