How to correctly drop all tables from an sqlite database using `QSqlQuery::execBatch()`?
-
I do successfully insert values appended to
QVariantLists
to my sql database usingQSqlQuery::addBindValue(QVariantList)
in several places using positional placeholders and callingQSqlQuery::execBatch()
. This usually works flawlessly.However I wanted to do the same for a
DROP TABLE IF EXISTS
-statement as well where I dynamically get all tables from my database and drop them. After lots of debugging, I can still not figure out what I am doing wrong.This is what I am trying to do:
QSqlQuery dropQuery(db); dropQuery.prepare("DROP TABLE IF EXISTS ?"); QVariantList tables; auto tableNames = db.tables(); tableNames.removeAll("sqlite_sequence"); for (const auto& tableName : qAsConst(tableNames)) { tables << tableName; } dropQuery.addBindValue(tables); if (!dropQuery.execBatch()) { qDebug() << "Unable to drop Tables: " % dropQuery.lastError().text() % " at: " % dropQuery.lastQuery(); }
Which always fails with:
Unable to drop Tables: Parameter count mismatch at: DROP TABLE IF EXISTS ?
When I simply do the same thing using multiple calls to
QSqlQuery::exec()
it all works fine:QSqlQuery dropQuery(db); QString dropQueryString = "DROP TABLE IF EXISTS %1"; auto tableNames = db.tables(); tableNames.removeAll("sqlite_sequence"); bool allTablesDropped = true; for (const auto& tableName : qAsConst(tableNames)) { if (!dropQuery.exec(dropQueryString.arg(tableName))) { allTablesDropped = false; break; } } if (!allTablesDropped) { [...] }
Any ideas what could cause this?
Thanks in advance
-
@devjb
You seem to be doing two things wrong (if I understand your code right):-
In the first case you are producing a single
DROP TABLE IF EXISTS table1 table2 ... tablen
statement. Only you know SQLite, but I would not have thought that is acceptable, surely you have to drop one table at a time? -
In the first case you are using "bound variables" for the SQL statement. Only certain SQLite statements accept bound values.
INSERT VALUES
statements do.DROP TABLE
likely do not.
-
-
@JonB said in How to correctly drop all tables from an sqlite database using `QSqlQuery::execBatch()`?:
@devjb
You seem to be doing two things wrong (if I understand your code right):- In the first case you are producing a single
DROP TABLE IF EXISTS table1 table2 ... tablen
statement. Only you know SQLite, but I would not have thought that is acceptable, surely you have to drop one table at a time?
Not exactly. Placeholders will be respected by
execBatch()
in a way that the queries are executed for each member of theQVariantList
. You only have to make sure to contain the same number ofQVariant
s for eachQVariantList
.
For anINSERT
statement, this does work fine.However, your assumption
- In the first case you are using "bound variables" for the SQL statement. Only certain SQLite statements accept bound values.
INSERT VALUES
statements do.DROP TABLE
likely do not.
is correct. It is even documented in the sqlite c library.
DROP
statements do not support binding.I see a lot of improvement potential both in the Qt documentation as well as in the internal query error management though.
- In the first case you are producing a single