SQLITE - UPDATE query problem

Hello!

I've got piece of code:

Code:

---

QString zapytanie;
    QTextStream out(stdout);
    bool ok;
 
    zapytanie="update sensors set localization='";
    zapytanie.append(ui->localizationLine->text());
    zapytanie.append("'");
    zapytanie.append(" where devices_id='");
    zapytanie.append(QString::number(IDLicznika[ui->dostepneLiczniki->currentIndex()]));
    zapytanie.append("'");
 
    QSqlDatabase bdb = QSqlDatabase::addDatabase("QSQLITE");
 
    bdb.setDatabaseName("mybase.db");
    ok = bdb.open();
 
    if (ok)
    {
        out << endl << "Otworzylem baze!" << endl ;
    } else {
        out << "Nie udalo sie otworzyc bazy!" << endl;
    }
 
    QSqlQueryModel *queryModel = new QSqlQueryModel;
    queryModel->setQuery(zapytanie, bdb);
 
    bdb.close();
 
out << zapytanie << endl;

---

and it doesn't work! Any idea why? Every tabel/colum exists. Last line show in console correct query.

thanks in advance
best regards
Tomasz

First check if you sql is correct and you might want to use QSqlQuery::prepare() since it is saver. Also a QSqlQueryModel is a absolutely overkill for your task. Just use a simple QSqlQuery and execute it.

Ok. I've made QSqlQuery:

Code:

---

QSqlQuery query;
    query.prepare("update sensors set localization='abc' where devices_id='1'");
    query.exec();

---

and it doesn't work too. I've executed that query in command line and it works fine. Any idea? I always close database in all functions I use in my application, bu I get warnings when I use database (maybe it's important):

Code:

---

QSqlDatabasePrivate::addDatabase: duplicate connection name 'qt_sql_default_connection', old connection removed.

---

When I do SELECT instead UPDATE it works fine too - I've checked execution with 'ok' bool variable.

thanks in advance
best regards
Tomasz

There is no need to close them. On startup open the database one time and everywhere you need it get the connection via the static QSqlDatabase::database(). If you wish give a name to the connection so you can have connections the different databases.

And what does query.lastError() return after the unsuccessful execution?

lastError() returns:

Code:

---

attempt to write a readonly database Unable to fetch row

---

but in command line everything works fine.

maybe you set a write lock somewhere or initiate it double times or your application has not the right to write to the file or or or.

My stupid mistake - database was readonly for all users except root. I've changed permissions.

best regards
Tomasz

Quote:

---

Originally Posted by Lykurg

On startup open the database one time and everywhere you need it get the connection via the static QSqlDatabase::database(). If you wish give a name to the connection so you can have connections the different databases.

---

What do You mean? I always do:

Code:

---

QSqlDatabase bdb = QSqlDatabase::addDatabase("QSQLITE");
 
bdb.setDatabaseName(_PATH_TO_MY_DB_);
ok = bdb.open();

---

should I bdb.open() at the begining? Won't this lock up my database?

thanks in advance
best regards
Tomasz

Just as a small demonstration:

Code:

---

main() {
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "MySettings");
db.setDatabaseName("settings.db");
db.open(); // assuming it opens
 
db = QSqlDatabase::addDatabase("QSQLITE", "MyValues");
db.setDatabaseName("values.db");
db.open(); // assuming it opens
 
 
MainWindow w;
w.show();
 
//...
}
 
void Foo::bar() {
// access your settings db
QSqlDatabase db = QSqlDatabase::database("MySettings");
QSqlQuery q(db);
//...
}
 
void Foo::barbar() {
// access your values db
QSqlDatabase db = QSqlDatabase::database("MyValues");
QSqlQuery q(db);
//...
}

---

No open() nor anything else.

I've got UPDATE problem again. This time I'm doing in one of my slots something like this:

Code:

---

ok = bdb.open();
 
if(ok)
    {
           zapytanie.clear();
           zapytanie="UPDATE mesg SET cmp='1'";
           zapytanie.append(" WHERE id='");
           zapytanie.append(QString::number(rec.value(0).toInt()));
           zapytanie.append("'");
 
           query.clear();
           query.prepare(zapytanie);
           ok = query.exec();
           query.clear();
 
           bdb.close();
}

---

At the beginning of the program I've got:

Code:

---

databaseName="mybase.db";
bdb = QSqlDatabase::addDatabase("QSQLITE");
bdb.setDatabaseName(databaseName);

---

If I give a name to database:

Code:

---

bdb = QSqlDatabase::addDatabase("QSQLITE","databaseName");

---

I can read from database but the update code above gives:

Code:

---

QSqlQuery::prepare: database not open

---

When I remove name everything is OK. What's wrong? I know You've given me code and said that I can use It without open() but I need to close base after use because other programs uses the same database. And I want to give that database name.

thanks in advance
best regards
Tomasz

with your posted code examples it is hard to say. One general advice: don't store queries as member variables. Declare them always on need in place.

You likely put a wrong database to query.

Quote:

---

Originally Posted by Lykurg

Declare them always on need in place.
You likely put a wrong database to query.

---

You are right. I declared it in place where I needed it and gived as an argument my database and it works. Now I'm just curious about something. I've wrote something like this, just to see what will happen (function that should read one thing from database and then close it):

Code:

---

bool MainWindow::readFromDB()
{
    bool ok;
    QTextStream out(stdout);
 
    QSqlRecord rec;
    QSqlQueryModel queryModel;
 
    bdb = QSqlDatabase::addDatabase("QSQLITE","vb");
    bdb.setDatabaseName(databaseName);
 
    ok = bdb.open();
 
    if (ok)
    {
        queryModel.clear();
        queryModel.setQuery("SELECT * FROM table WHERE name='var1'", bdb);
 
        rec = queryModel.record(0);
        notificationInterval = (rec.value(1).toInt())*1000;
 
        rec.clear();
        queryModel.clear();
        bdb.close();
 
    } else {
        out << "Nie udalo sie otworzyc bazy!" << endl;
    }
 
    QSqlDatabase::removeDatabase("vb");
 
    return ok;
}

---

And it does what it should but gives in console:

Code:

---

QSqlDatabasePrivate::removeDatabase: connection 'vb' is still in use, all queries will cease to work.

---

Is is normal behavior?

thanks in advance
best regards
Tomasz

Quote:

---

Originally Posted by Tomasz

Is is normal behavior?

---

Yes it is. It is also described in the docs. And once again: Do not use QSqlQueryModel for such a simple task, just use QSqlQuery!

Quote:

---

Code:

---

// WRONG
 QSqlDatabase db = QSqlDatabase::database("sales");
 QSqlQuery query("SELECT NAME, DOB FROM EMPLOYEES", db);
 QSqlDatabase::removeDatabase("sales"); // will output a warning
 
 // "db" is now a dangling invalid database connection,
 // "query" contains an invalid result set

---

The correct way to do it:

Code:

---

{
     QSqlDatabase db = QSqlDatabase::database("sales");
     QSqlQuery query("SELECT NAME, DOB FROM EMPLOYEES", db);
 }
 // Both "db" and "query" are destroyed because they are out of scope
 QSqlDatabase::removeDatabase("sales"); // correct

---

---