SQLITE - UPDATE query problem
Hello!
I've got piece of code:
Code:
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("'");
bdb.setDatabaseName("mybase.db");
ok = bdb.open();
if (ok)
{
out << endl << "Otworzylem baze!" << endl ;
} else {
out << "Nie udalo sie otworzyc bazy!" << endl;
}
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
Re: SQLITE - UPDATE query problem
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.
Re: SQLITE - UPDATE query problem
Ok. I've made QSqlQuery:
Code:
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
Re: SQLITE - UPDATE query problem
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?
Re: SQLITE - UPDATE query problem
lastError() returns:
Code:
attempt to write a readonly database Unable to fetch row
but in command line everything works fine.
Re: SQLITE - UPDATE query problem
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.
Re: SQLITE - UPDATE query problem
My stupid mistake - database was readonly for all users except root. I've changed permissions.
best regards
Tomasz
Re: SQLITE - UPDATE query problem
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:
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
Re: SQLITE - UPDATE query problem
Just as a small demonstration:
Code:
main() {
db.setDatabaseName("settings.db");
db.open(); // assuming it opens
db.setDatabaseName("values.db");
db.open(); // assuming it opens
MainWindow w;
w.show();
//...
}
void Foo::bar() {
// access your settings db
//...
}
void Foo::barbar() {
// access your values db
//...
}
No open() nor anything else.
Re: SQLITE - UPDATE query problem
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.setDatabaseName(databaseName);
If I give a name to database:
I can read from database but the update code above gives:
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
Re: SQLITE - UPDATE query problem
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.
Re: SQLITE - UPDATE query problem
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;
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;
}
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
Re: SQLITE - UPDATE query problem
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
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:
{
QSqlQuery query
("SELECT NAME, DOB FROM EMPLOYEES", db
);
}
// Both "db" and "query" are destroyed because they are out of scope