QSqlQuery DELETE LIMIT

Gary Metalle

I'm using QT 5.15.2 and am having trouble running a SQL query to delete x rows from a table using LIMIT from a SQLITE database.

The query code is:

if (query.exec("DELETE FROM file_database.resend_message LIMIT 10"))
 {
   resendMessageDeletedTotal = query.numRowsAffected();
   query.finish();
}
else
 {
   qWarning() << "Couldn't delete from external file database (retry) because" << query.lastError();
 }

Comes back with the following error:

Couldn't delete from external file database (retry) because QSqlError("1", "Unable to execute statement", "near "LIMIT": syntax error")

I have verified that using the sqlite3 command line I can use the same command OK and also running the identical query through 'DB Browser for SQLite' tool.

Maybe there's a pragma or something else I'm missing to be able to use LIMIT with a DELETE? I have used other SELECT queries with a LIMIT that work fine, just not DELETE.

Gary Metalle

@Christian-Ehrlicher Yeah I found that shortly after I created the post. I have dumped out the compiler options from within the 'DB Browser for SQLite' tool and can confirm it is enabled there.

Does this mean that Qt (or rather the QSql lib) has to also be built with this option too? I'm assuming that when I ran that dump of compiler options from DB Browser that it was giving me the compiler options for the underlying sqlite3 lib that I have on the system?

Christian Ehrlicher

See https://www.sqlite.org/compile.html#enable_update_delete_limit

Gary Metalle

@Christian-Ehrlicher Yeah I found that shortly after I created the post. I have dumped out the compiler options from within the 'DB Browser for SQLite' tool and can confirm it is enabled there.

Does this mean that Qt (or rather the QSql lib) has to also be built with this option too? I'm assuming that when I ran that dump of compiler options from DB Browser that it was giving me the compiler options for the underlying sqlite3 lib that I have on the system?

Christian Ehrlicher

@Gary-Metalle said in QSqlQuery DELETE LIMIT:

also be built with this option too?

Since the Qt sqlite plugin is using the amalgation version, yes. You have to recompile it by yourself to use the sqlite provided by system (or by you).

Gary Metalle

@Christian-Ehrlicher said in QSqlQuery DELETE LIMIT:

i

Thanks for that advice @Christian-Ehrlicher it's all good now after rebuilding the sqlite plugin. Here's what I did if someone else has to do this...

add -system-sqlite into your config.opt file in your QT build directory.
run config.status
check that in config.summary you see 'yes' for Using system provided SQLite as shown below:

Qt Sql Drivers:
DB2 (IBM) .............................. no
InterBase .............................. no
MySql .................................. yes
OCI (Oracle) ........................... no
ODBC ................................... no
PostgreSQL ............................. yes
SQLite2 ................................ no
SQLite ................................. yes
Using system provided SQLite ......... yes

Then build and install the plugin:

make sub-sqlite
sudo make install sub-sqlite

That's obviously specific to my Linux platform (Ubuntu) so you'd have to adapt as necessary.

I think maybe the reason had to do this manually, was that the sqlite3-dev package wasn't installed on my machine at the time QT was built, even though the sqlite3 binaries were there on the system. Before rebuilding QT as shown above, I installed the dev package thus:

sudo apt-get install libsqlite3-dev