How to correctly drop all tables from an sqlite database using `QSqlQuery::execBatch()`?

[devjb]

I do successfully insert values appended to QVariantLists to my sql database using QSqlQuery::addBindValue(QVariantList) in several places using positional placeholders and calling QSqlQuery::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

[JonB]

@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.

[devjb]

@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 the QVariantList. You only have to make sure to contain the same number of QVariants for each QVariantList.
For an INSERT 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.