Qt-interest Archive, April 2007
Weird behavior of sqlite integration
Message 1 in thread
Hello all,
i must have missed something, because accessing sqlite from Qt is "not
working" for me, politely said. Initially, i wanted to use prepared
statements, but gradually have drilled down to this code ( run simply
in bare-bones main() ):
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("./test.db");
Q_ASSERT( db.open() );
QSqlQuery sqlSelect(db);
Q_ASSERT(sqlSelect.exec("SELECT WHATEVER FROM TESTTBL WHERE PK_ID=1"));
Q_ASSERT(sqlSelect.next());
qDebug() << sqlSelect.value(0).toString();
QSqlQuery sqlDelete(db);
Q_ASSERT(sqlDelete.exec("DELETE FROM TESTTBL WHERE PK_ID=1"));
qDebug() << sqlDelete.lastError();
The database is
CREATE TABLE testtbl (pk_id INTEGER PRIMARY KEY, whatever TEXT);
The last assert on sqlDelete.exec() fails. The following sql error is:
QSqlError(1, "Unable to fetch row", "SQL logic error or missing database")
IT DOES NOT FAIL IF:
1. i do not run the sqlSelect statement, only the second sqlDelete
(being then the only statement executed)
2. i do not use separate statement (sqlDelete) and re-use sqlSelect
object instead.
Both solutions are somewhat not applicable to any decent application
using prepared statements.
Please, someone, tell me what my sins are.
Thank you,
Pavel Zdenek
--
[ signature omitted ]
Message 2 in thread
Hi Pavel!
Pavel Zdenek schrieb:
> i must have missed something, because accessing sqlite from Qt is "not
> working" for me, politely said. Initially, i wanted to use prepared
> statements, but gradually have drilled down to this code ( run simply
> in bare-bones main() ):
>
> QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
> db.setDatabaseName("./test.db");
> Q_ASSERT( db.open() );
> QSqlQuery sqlSelect(db);
> Q_ASSERT(sqlSelect.exec("SELECT WHATEVER FROM TESTTBL WHERE PK_ID=1"));
> Q_ASSERT(sqlSelect.next());
> qDebug() << sqlSelect.value(0).toString();
> QSqlQuery sqlDelete(db);
> Q_ASSERT(sqlDelete.exec("DELETE FROM TESTTBL WHERE PK_ID=1"));
> qDebug() << sqlDelete.lastError();
>
> The database is
> CREATE TABLE testtbl (pk_id INTEGER PRIMARY KEY, whatever TEXT);
Tried that with Qt 4.1.4. On delete I get a "DB is locked" error; doing
sqlSelect.clear();
solves this and the delete statement is executed properly.
HTH, Martin
--
[ signature omitted ]
Message 3 in thread
Ok, that really helps, thanks a lot. I know about this method but did
not consider it necessary for my rather simple application. My Qt is
4.2.3 which may mean an sqlite version different enough to explain the
difference in error messages. Yours is more understandable anyway.
<unsatisfied-mumble target="trolltech">
The description of QSqlQuery::clear() method says
"You should rarely if ever need to call this function".
To guarantee stable operation when using multiple prepared statements
on one database (sequentially, not concurrently!), i must use clear()
after each single exec(). Which is not exactly what i would call
"rare". Am i still doing something incorrectly?
</unsatisfied-mumble>
2007/4/3, Martin Gebert <Martin.Gebert@xxxxxxxxxxxxxxxxxxxxxxxxxxx>:
> Hi Pavel!
>
> Pavel Zdenek schrieb:
> > i must have missed something, because accessing sqlite from Qt is "not
> > working" for me, politely said. Initially, i wanted to use prepared
> > statements, but gradually have drilled down to this code ( run simply
> > in bare-bones main() ):
> >
> > QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
> > db.setDatabaseName("./test.db");
> > Q_ASSERT( db.open() );
> > QSqlQuery sqlSelect(db);
> > Q_ASSERT(sqlSelect.exec("SELECT WHATEVER FROM TESTTBL WHERE PK_ID=1"));
> > Q_ASSERT(sqlSelect.next());
> > qDebug() << sqlSelect.value(0).toString();
> > QSqlQuery sqlDelete(db);
> > Q_ASSERT(sqlDelete.exec("DELETE FROM TESTTBL WHERE PK_ID=1"));
> > qDebug() << sqlDelete.lastError();
> >
> > The database is
> > CREATE TABLE testtbl (pk_id INTEGER PRIMARY KEY, whatever TEXT);
>
> Tried that with Qt 4.1.4. On delete I get a "DB is locked" error; doing
>
> sqlSelect.clear();
>
> solves this and the delete statement is executed properly.
>
> HTH, Martin
>
> --
> To mail me in private, please remove the -NOSPAM- part from the address
> above.
>
> Ultimate Truths in software development # 7:
> If your program is idiot-proof, they'll find a better idiot.
>
> --
> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx with "unsubscribe" in the subject or the body.
> List archive and information: http://lists.trolltech.com/qt-interest/
>
>
--
[ signature omitted ]
Message 4 in thread
Pavel Zdenek schrieb:
> <unsatisfied-mumble target="trolltech">
> The description of QSqlQuery::clear() method says
> "You should rarely if ever need to call this function".
> To guarantee stable operation when using multiple prepared statements
> on one database (sequentially, not concurrently!), i must use clear()
> after each single exec(). Which is not exactly what i would call
> "rare". Am i still doing something incorrectly?
> </unsatisfied-mumble>
Unfortunately not. This seems to be induced by the locking mechanisms of
SQLite for concurrent access. Meanwhile I'm accustomed to clear(),
destroy or perhaps recycle *every* QSqlQuery instance instantly after
I'm done with it, else there could arise problems with updates on the DB :-(
And yes, for SQLite are both of your queries concurrent, because the
first is still assumed to be open... BTW, using transactions may
sometimes clear things up a bit. So far my understanding of that issue;
you may want to ask the SQLite folks for details.
Martin
--
[ signature omitted ]
Message 5 in thread
I've found a problem, already reported here,
http://lists.trolltech.com/qt-interest/2004-01/thread00399-0.html
but that doesn't seem to be solved.
In the project file I include a script in INSTALLS. The generated makefile
strips this file also, together with the application binary. This results in
an install error message (though installation is fine) that is confusing. Is
there any way to avoid this?
Thanks
--
[ signature omitted ]
Message 6 in thread
Pavel Zdenek wrote:
> Please, someone, tell me what my sins are.
I've got similar errors in Qt 4.2 and older. Since I use Qt4.3
(beta/snapshots) it all has gone.
There was an error in QSqlError (if I remember right).
cheers
Petr
--
[ signature omitted ]
