How to stop ongoing java.sql.statement.executeQuery()?

Question

I'm using latest derby10.11.1.1. Doing something like this:

DriverManager.registerDriver(new org.apache.derby.jdbc.EmbeddedDriver())
java.sql.Connection connection = DriverManager.getConnection("jdbc:derby:filePath", ...)
Statement stmt = connection.createStatement();
stmt.setQueryTimeout(2); // shall stop query after 2 seconds but id does nothing
stmt.executeQuery(strSql);
stmt.cancel(); // this in fact would run in other thread

I get exception "java.sql.SQLFeatureNotSupportedException: Caused by: ERROR 0A000: Feature not implemented: cancel"

Do you know if there is way how to make it work? Or is it really not implemented in Derby and I would need to use different embedded database? Any tip for some free DB, which I can use instead of derby and which would support SQL timeout?

Please show your actual code, rather than "something like" it. It's difficult to diagnose the issue without the complete context. Please read "How to Ask" for additional details. — Deacon
– Deacon, Commented May 19, 2015 at 17:17
h2 supports a query timeout: stackoverflow.com/q/21984515/217324 — Nathan Hughes
– Nathan Hughes, Commented May 19, 2015 at 18:20
@DougR. my actual code is bit longer. What information are you missing? I'm afraid that Derby just does not support the cancel() at all. I found reference manual page where is written cancel() not supported by the server. So the questions remaining is how to un-lock derby query without cancel() or what else embedded database to use which can be cancel(). — Vit Bernatik
– Vit Bernatik, Commented May 19, 2015 at 18:25
So if it doesn't violate any NDA's, post your actual code, or a minimal, complete, and verifiable example that causes the error. — Deacon
– Deacon, Commented May 19, 2015 at 18:26

Prashant · Accepted Answer · 2015-05-19 18:13:07Z

1

As i got in java docs

void cancel() throws SQLException
Cancels this Statement object if both the DBMS and driver support aborting an SQL statement. This method can be used by one thread to cancel a statement that is being executed by another thread.

and it will throws

SQLFeatureNotSupportedException - if the JDBC driver does not support this method

you can go with mysql. there are so many embedded database available you can go through embedded database

edited May 19, 2015 at 18:13

answered May 19, 2015 at 17:14

Prashant

2,6122 gold badges23 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vit Bernatik Over a year ago

it seems that mysql would require to install mysql server separately. There seems to be no free embedded mysql. Am I right? My goal is to have one jar including DB itself.

Prashant Over a year ago

yes you need to install server for mysql then you need different database as in wikipedia en.wikipedia.org/wiki/Embedded_database

Vit Bernatik Over a year ago

But they don't say whether cancel() would be supported or not :(. BTW is there other way than cancel() to kill the dead-locked transaction? Or am I doomed forever when this happens and I need to close whole app?

Prashant Over a year ago

as nathan tolds h2 supports. you can go with that.

Community · Accepted Answer · 2017-05-23 11:51:05Z

1

If you get Feature not implemented: cancel then that is definite, cancel is not supported.

From this post by H2's author it looks like H2 supports two ways to timeout your queries, both through the JDBC API and through a setting on the JDBC URL.

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered May 19, 2015 at 18:31

Nathan Hughes

96.7k21 gold badges193 silver badges288 bronze badges

1 Comment

Vit Bernatik Over a year ago

Yup works - in H2 table lock timeout is by default and changable by statement.execute("SET LOCK_TIMEOUT 5000");

Vit Bernatik · Accepted Answer · 2015-05-20 19:20:42Z

0

Actually I found that there is deadlock timeout in derby as well only set to 60 seconds by default and I never have patience to reach it :).

So the correct answer would be:

stmt.setQueryTimeout(2); truly seems not working

stmt.cancel(); truly seems not implemented

But luckily timeout in database manager exists. And it is set to 60 seconds. See derby dead-locks.

Time can be changed using command:

statement.executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
                "'derby.locks.waitTimeout', '5')");

And it works :)

answered May 20, 2015 at 19:20

Vit Bernatik

3,8524 gold badges39 silver badges46 bronze badges

Collectives™ on Stack Overflow

How to stop ongoing java.sql.statement.executeQuery()?

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related