How to interrupt / cancel an HSQLDB query
I have a Java SE application using JPA provided by Hibernate and an HSQL database. The application queries the database with EntityManagers for information to draw diagrams on the screen. While the query is running, the chart area has a typical rotation icon to let the user know that he is working. Some requests take a long time and I want the function to abort / cancel the request. I know I can expand the EntityManager and get the Hibernate session and call cancelQuery (), but that doesn't do anything because the HSQLDB driver doesn't support the cancel () method in the Statement interface ( http://hsqldb.org/doc/2.0/apidocs /org/hsqldb/jdbc/JDBCStatement.html#cancel ()). Is there any other way, either through the API or redesign my application? The inability to cancel a running request is a nuisance.
source to share
In the moment of Moment, there is no end to the completion of a long statement on the HSQLDB server. The ALTER SESSION method provided by Fredt doesn't seem to work in the current RC2 for version 2.3.4 (as well as svn revision 5511), it just rolls back the transaction: serialization error AFTER the select statement completes. So the only way to deal with this problem is to set Timeout. However, this is not a real solution to the problem.
Update: In the newest version of HSQLDB in the SVN repository (Rev. 5627) and in the upcoming release 2.3.4, it is now possible to immediately revoke an approval. There is also a new ALTER SESSSION END STATEMENT method that can abort a transaction without rolling back. See https://sourceforge.net/p/hsqldb/bugs/1436/ for details .
source to share
You can use a separate connection with administrator rights and terminate the transaction in another session:
ALTER SESSION <session number> RELEASE
http://hsqldb.org/doc/2.0/guide/sessions-chapt.html#snc_statements
In recent versions of SubQuery HSQLDB, this statement also terminates any SELECT statement that is executed in the target session.
You can use SESSION_ID () to find the session ID and use it to release the session.
Alternatively, you can use JDBCStatement.setQueryTimeout (n) before executing your query.
The cancel () method may be supported in the near future.
source to share