How to cancel long-running QSqlQuery?

Question

How to cancel long running QSqlQuery?

Database is returning 3M+ rows and it's shown in QTableView control. I'd like to be able to force stop both long operations:

1. when database is running a long operation
2. if database is fast, but there is a huge number of rows to be returned and processing/copying/showing those takes a lot of time

2nd bullet, can be solved by not using QSqlQueryModel. In this case, parsing query results manually can be done in stages and this will be implemented, but i'd also like to know if the process of moving data DB->QTableView can be interrupted and cancelled.

I've tried following without success:

• QSqlQuery::finish()
• QFuture::cancel()
• QSqlDatabase.close() -- this one crashes application

If full context is needed, it's here. Method in question is

on_button_stopQuery_released

Answer 1

Aborting queries during execution (not fetching, which is what QSqlQuery::finish does) is hit-and-miss in all databases. Qt itself doesn't support this; workarounds will be backend-specific.

For example, with PostgreSQL you can do the following:

• In your original connection, retrieve the connection ID (SELECT pg_backend_pid();) and save it
• When you want to abort your query, open a second connection and kill the query by issuing SELECT pg_cancel_backend(saved_id);

SQLite has sqlite3_interrupt(sqlite3*). This interrupts queries and does not close the connection.

MySQL is similar to PostgreSQL:

• First retrieve the connection ID (SELECT CONNECTION_ID();)
• Then kill it through another connection (KILL [CONNECTION|QUERY] $connection_id).

As you can see, even the capabilities provided are backend-specific. Postgres can only abort connections, while SQLite can only abort queries. The easiest way to implement this would thus be to discard the connection if the query was aborted and the connection is still valid. Then you can have a simple two-API interface for a cancellation management (pseudo code, i.e. Python):

class IConnectionCancellation:
    def register(connection):
        # save/retrieve connection ID

    def cancel():
        # open second connection, send backend-specific query

---

For large result sets, consider using canFetchMore and fetchMore in your model. That way you don't have to process the entire result set before showing some results to the user; might feel smoother to use. Doesn't help with inherent query execution latency due to e.g. order by or grouping clauses, of course.