We have got a large number of long running SQL queries and would like to be able to cancel the execution. So far, I am using an ExecutorCompletionService to run the queries, which works well for cancelling queries which are not yet run.
My problem is: I would like to hard-cancel the currently running queries too. The method java.sql.Statement.cancel() does not seem to work with HSQLDB. From the java doc: "Cancels this Statement object if both the DBMS and driver support aborting an SQL statement." I guess that HSQLDB does not support cancellation.
Does anyone have any idea how to cancel the statement anyways (in a possibly ugly, but still acceptable way)?
See https://sourceforge.net/p/hsqldb/bugs/1436/ for a working solution.
The short variant:
Use the newest SVN Version of HSQLDB or the 2.3.4 Final for the Server.
If the current query that you want to cancel is using a hsqldb connection, create a new connection with admin rights to the same server.
Use
select * from information_schema.system_sessions
to find the session with the statement you are looking for.
Use
ALTER SESSION <SESSIONNUMBER> RELEASE
to cancel the Statement.
Close the Connection.