Canceling long queries in HSQLDB / JDBC
We have received a lot of long SQL queries and would like to cancel execution. So far I have been using ExecutorCompletionService
to run requests, which are good for canceling requests that have not yet started.
My problem: I would like to cancel the currently running requests as well. The java.sql.Statement.cancel () method doesn't seem to work with HSQLDB
. From the java doc : "Cancels this Statement object if both the DBMS and driver support interrupt the SQL statement." I think HSQLDB doesn't support undo.
Does anyone know how to reverse the expression anyway (in an ugly, but still acceptable way)?
source to share
See https://sourceforge.net/p/hsqldb/bugs/1436/ for a working solution.
Short version:
Use the latest SVN for HSQLDB or 2.3.4 Final for the server.
If the current request you want to cancel is using an hsqldb connection, create a new connection with administrator rights on the same server.
Use
select * from information_schema.system_sessions
to find the session with the query you are looking for.
Use
ALTER SESSION <SESSIONNUMBER> RELEASE
to cancel the expression.
Close the connection.
source to share