2

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?

Vit Bernatik
  • 3,566
  • 2
  • 34
  • 40
  • Please show your actual code, rather than "something like" it. It's difficult to diagnose the issue without the complete context. Please read "[ask]" for additional details. – Deacon May 19 '15 at 17:17
  • 2
    h2 supports a query timeout: http://stackoverflow.com/q/21984515/217324 – Nathan Hughes May 19 '15 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](http://db.apache.org/derby/docs/10.11/ref/rrefexcept71493.html) 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 May 19 '15 at 18:25
  • So if it doesn't violate any NDA's, post your actual code, or a [minimal, complete, and verifiable example](http://stackoverflow.com/help/mcve) that causes the error. – Deacon May 19 '15 at 18:26

3 Answers3

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

Prashant
  • 2,556
  • 2
  • 20
  • 26
  • 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. – Vit Bernatik May 19 '15 at 18:00
  • 1
    yes you need to install server for mysql then you need different database as in wikipedia http://en.wikipedia.org/wiki/Embedded_database – Prashant May 19 '15 at 18:09
  • 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? – Vit Bernatik May 19 '15 at 18:18
  • 1
    as nathan tolds h2 supports. you can go with that. – Prashant May 19 '15 at 18:31
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.

Community
  • 1
  • 1
Nathan Hughes
  • 94,330
  • 19
  • 181
  • 276
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 :)

Vit Bernatik
  • 3,566
  • 2
  • 34
  • 40