CODEWITHSUNDEEP
javadatabasejdbcderby
Vit Bernatik
Vit Bernatik

Reputation: 3802

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

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?

Upvotes: 2

Views: 2764

Answers (3)

Vit Bernatik
Vit Bernatik

Reputation: 3802

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 :)

Upvotes: 0

Nathan Hughes
Nathan Hughes

Reputation: 96424

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.

Upvotes: 1

Prashant
Prashant

Reputation: 2614

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

Upvotes: 1

Related Questions