PDA

View Full Version : SQLITE - UPDATE query problem



Tomasz
24th August 2010, 15:18
Hello!

I've got piece of 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

Lykurg
24th August 2010, 15:43
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.

Tomasz
24th August 2010, 15:52
Ok. I've made QSqlQuery:



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



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

Lykurg
24th August 2010, 15:56
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?

Tomasz
24th August 2010, 16:00
lastError() returns:



attempt to write a readonly database Unable to fetch row


but in command line everything works fine.

Lykurg
24th August 2010, 16:15
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.

Tomasz
25th August 2010, 22:22
My stupid mistake - database was readonly for all users except root. I've changed permissions.

best regards
Tomasz

Tomasz
26th August 2010, 14:18
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:



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

Lykurg
26th August 2010, 14:45
Just as a small demonstration:
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.

Tomasz
5th September 2010, 16:09
I've got UPDATE problem again. This time I'm doing in one of my slots something like this:



ok = bdb.open();

if(ok)
{
zapytanie.clear();
zapytanie="UPDATE mesg SET cmp='1'";
zapytanie.append(" WHERE id='");
zapytanie.append(QString::number(rec.value(0).toIn t()));
zapytanie.append("'");

query.clear();
query.prepare(zapytanie);
ok = query.exec();
query.clear();

bdb.close();
}


At the beginning of the program I've got:



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


If I give a name to database:


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


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


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

Lykurg
5th September 2010, 17:06
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.

Tomasz
5th September 2010, 20:57
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):



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:



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


Is is normal behavior?

thanks in advance
best regards
Tomasz

Lykurg
5th September 2010, 21:27
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!



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


{
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