Ok I have an update. I have tested on PostgreSQL and I do get the timeout. In order to create a simple case I have created a simple stored procedure on pg :
CREATE OR REPLACE PROCEDURE test_timeout("test" integer) LANGUAGE SQL AS $$ select count(*) from pg_sleep("test") $$; and the call em.createStoredProcedureQuery("test_timeout") .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN) .setParameter(1, 5) .setHint("javax.persistence.query.timeout", 1) .execute(); actually throws the exception. I have then created a simple Derby database (empty) in which I have created my procedure CREATE SCHEMA TEST; CALL SQLJ.INSTALL_JAR('file:///home/marco/devel/stored_procedures/target/storedprocedure-1.0.jar', 'TEST.test', 0); CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath', 'TEST.test'); DROP PROCEDURE APP.test_timeout; CREATE PROCEDURE TEST.test_timeout (IN WAIT BIGINT) PARAMETER STYLE JAVA NO SQL LANGUAGE JAVA EXTERNAL NAME 'org.me.storedprocedure.Procedures.db_wait'; Here's the super dummy procedure if you want to try: public static void db_wait(long wait) throws Exception { Thread.sleep(wait*1000); } Running this code : public boolean testStoredProcedure(EntityManager em ) throws Exception { em.createStoredProcedureQuery("test.test_timeout") .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN) .setParameter(1, 5) .setHint("javax.persistence.query.timeout", 1) .execute(); return false; } Against Postgresql : [EL Warning]: 2022-04-20 14:52:29.152--UnitOfWork(392289808)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.v20180807-4be1041): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.postgresql.util.PSQLException: ERROR: canceling statement due to user request Where: SQL statement "select count(*) from pg_sleep("test")" PL/pgSQL function test.test_timeout(integer) line 5 at SQL statement Error Code: 0 Call: SELECT * FROM test.test_timeout(?) bind => [1 parameter bound] Query: ResultSetMappingQuery() javax.persistence.PersistenceException:Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.v20180807-4be1041): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.postgresql.util.PSQLException: ERROR: canceling statement due to user request Where: SQL statement "select count(*) from pg_sleep("test")" PL/pgSQL function test.test_timeout(integer) line 5 at SQL statement Error Code: 0 Call: SELECT * FROM test.test_timeout(?) bind => [1 parameter bound] Query: ResultSetMappingQuery() [EL Info]: connection: 2022-04-20 14:52:29.162--ServerSession(1406848276)--/file:/home/marco/devel/test_jpa/target/classes/_test-pu logout successful Against Derby (Embedded): [EL Info]: 2022-04-20 14:48:40.742--ServerSession(256346753)--EclipseLink, version: Eclipse Persistence Services - 2.7.3.v20180807-4be1041 [EL Info]: connection: 2022-04-20 14:48:41.028--ServerSession(256346753)--/file:/home/marco/devel/test_jpa/target/classes/_test-pu login successful [EL Info]: connection: 2022-04-20 14:48:46.144--ServerSession(256346753)--/file:/home/marco/devel/test_jpa/target/classes/_test-pu logout successful As you can see the call is cancelled after 1 millisecond when run against postgresql while it is not while run against derby The two jdbc drivers I used : <dependency> <groupId>org.apache.derby</groupId> <artifactId>derby</artifactId> <version>10.14.2.0</version> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.3.4</version> </dependency> Am I hitting a derby/derby-jdbc limitation or am I missing some configuration ? Thanks in advance for any help you can provide On Apr 19 2022, at 11:57 pm, Marco Ferretti <marco.ferre...@gmail.com> wrote: > Hi Brian, > Thanks for your reply and attempt to help. > > Here's what I do: > Within the database (preparation of the test) > CALL > SQLJ.replace_jar('file:///home/marco/devel/stored_procedures/target/storedprocedure-1.0.jar', > 'APP.STORED_PROCS'); > > CREATE PROCEDURE APP.P_MUI_EXPORT_PIANIFICAZIONE (IN ID_PROMOZIONE BIGINT , > IN ID_COMPRATORE VARCHAR(255), IN CODICE_UTENTE VARCHAR(50) ) > PARAMETER STYLE JAVA > MODIFIES SQL DATA > LANGUAGE JAVA > EXTERNAL NAME 'com.foo.Procedures.exportPianificazione'; > > Here's the relevant parts of the (dummy) stored procedure: > public static void exportPianificazione(long idPromozione, String > idCompratori, String codiceUtente) > throws DbPromoException { > try (Connection conn = > DriverManager.getConnection("jdbc:default:connection");) { > new Utils().dummyExportPianificazione("APP", conn, idPromozione, > idCompratori, codiceUtente); > } catch (Exception e) { > log.log(Level.SEVERE, "Error writing values in mui_check_testata", e); > throw new DbPromoException("Error writing values in mui_check_testata : " + > e.getMessage(), e); > } > } > > within the Utils class: > public void dummyExportPianificazione(String schema, Connection conn, long > idPromozione, String idCompratori, > String codiceUtente) throws SQLException, InterruptedException { > String query = "insert into "+schema+".MUI_CHECK_COMPRATORI (ID, > ID_PROMOZIONE, ID_COMPRATORE, ESITO, CODICE_UTENTE_INSERIMENTO, > CODICE_UTENTE_AGGIORNAMENTO, DATA_INSERIMENTO, DATA_AGGIORNAMENTO ) values (" > + "NEXT VALUE FOR MUI_CHECK_COMPRATORI_ID_SEQ, ?, ?, ?, ?, ?, ?, ?)"; > String delete = "delete from " + schema > + ".MUI_CHECK_COMPRATORI where id_promozione = ? and id_compratore = ?"; > String esito=getEsito(); //random generation of a result > String[] compratori = idCompratori.split(","); > TimeUnit.SECONDS.wait(10); > .... > } > > > > In my application call : > ... > milliseconds = 1; > > ... > getEm().createStoredProcedureQuery(Constants.SP_EXPORT_PIANIFICAZIONE) > .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN) > .registerStoredProcedureParameter(2, String.class, ParameterMode.IN) > .registerStoredProcedureParameter(3, String.class, > ParameterMode.IN).setParameter(1, myPromo) > .setParameter(2, idCompratori).setParameter(3, username) > .setHint("javax.persistence.query.timeout", milliseconds) > .execute(); > .... > > Activating the JPA logs (EclipseLink) to level FINEST I can see that it takes > 10 seconds between the call to the stored procedure and the the next step of > my application. > I have also checked that the version of EclipseLink that I am using supports > this hint and that the default unit of the hint is millisecons (but even if > it was seconds, it should throw the timeout as 1<10). > > What is a real puzzle to me is the fact that I am not getting an exception > when I call the stored procedure from the application thus my doubt: do I > have to "activate" something in the jdbc driver/database in order to actually > fire the timeout ? > The other option is that I did not understand at all how this hint should > work (which, at this point, would make sense) > > Thanks in advance > On Apr 19 2022, at 10:05 pm, Bryan Pendleton <bpendleton.de...@gmail.com> > wrote: > > I think that Marco is trying to *test* how his code handles a timeout > > exception, but he doesn't know a way to force a timeout exception to occur > > during his test. > > > > I don't know a trivial way to do this, either. > > > > I think you might have to write a more complex test program, for example > > you could have two threads, and two transactions, and in the first thread > > you could begin a transaction and update a record BUT NOT COMMIT. > > > > Then, in the second thread, you could begin a transaction and call your > > stored procedure to run a query against that record, but the query should > > block because the record is updated by the first transaction in the first > > thread. > > > > Then, after some time expires, you should get the timeout exception in your > > stored procedure. > > > > thanks, > > > > bryan > > > > > > On Tue, Apr 19, 2022 at 9:30 AM Marco Ferretti <marco.ferre...@gmail.com > > (mailto:marco.ferre...@gmail.com)> wrote: > > > Hi Rick, > > > > > > thanks for taking the time to reply. > > > I am not 100% sure what you mean. My application uses JPA and the Entity > > > Manager is getting the connection from the container's datasource: I do > > > not have (direct) access to the java.sql.Statement. > > > What I (would like to) do is calling the a stored procedure in this way: > > > > > > getEm().createStoredProcedureQuery(Constants.SP_EXPORT_PIANIFICAZIONE) > > > .registerStoredProcedureParameter(1, Long.class, ParameterMode.IN) > > > .registerStoredProcedureParameter(2, String.class, ParameterMode.IN) > > > .registerStoredProcedureParameter(3, String.class, > > > ParameterMode.IN).setParameter(1, myPromo) > > > .setParameter(2, idCompratori).setParameter(3, username) > > > .setHint("javax.persistence.query.timeout", milliseconds) > > > .execute(); > > > > > > As far as the JPA API specification (and EclipseLink implementation) > > > goes, the ".setHint("javax.persistence.query.timeout", milliseconds)" > > > should be equivalent to what you suggest. > > > The problem is that I cannot make the stored procedure call to throw a > > > QueryTimeoutException even if my dummy procedure (in derby) waits for 10 > > > seconds and I set the timeout to 1 millisecond. > > > I am using, in my test environment, Apache Tomee (java 8) , derby client > > > 10.14 and a docker image with a derby network server > > > (https://github.com/az82/docker-derby/blob/master/Dockerfile > > > (https://link.getmailspring.com/link/3174774e-c496-42ab-83b1-dfe1a95ae...@getmailspring.com/0?redirect=https%3A%2F%2Fgithub.com%2Faz82%2Fdocker-derby%2Fblob%2Fmaster%2FDockerfile&recipient=ZGVyYnktdXNlckBkYi5hcGFjaGUub3Jn)) > > > . > > > > > > As far as my knowledge goes, and it's not that far, the reasons I am not > > > getting a timeout are : > > > I am not correctly using the hint > > > > > > I am not correctly setting up the datasource > > > > > > There is no timeout because the stored procedure terminates within the > > > given timeout. > > > > > > > > > On point 3: in my (dummy) implementation of the stored procedure i do, > > > before doing anything else, a TimeUnit.SECONDS.wait(10); > > > On point 1 : I am lost. I think I am using the hint correctly (at least > > > the API docs say so) > > > On point 2: I am setting up the datasource with the minimum configuration > > > possible : > > > <Resource id="jdbc/myDatasource" type="DataSource"> JdbcDriver > > > org.apache.derby.jdbc.ClientDriver JdbcUrl > > > jdbc:derby://database:1527/dbpromo;create=false UserName DBPROMO Password > > > dbpromo </Resource> > > > Could it be that I am failing to activate something on the Derby side ? > > > > > > Thanks in advance > > > Marco > > > > > > On Apr 19 2022, at 4:57 pm, Rick Hillegas <rick.hille...@gmail.com > > > (mailto:rick.hille...@gmail.com)> wrote: > > > > java.sql.Statement.setQueryTimeout(int) should do the trick. > > > > > > > > On 4/19/22 3:30 AM, Marco Ferretti wrote: > > > > > Hi all, > > > > > I am trying to simulate a query timeout in a stored procedure by > > > > > simply adding a delay in my (test) jar. > > > > > I then am launching the stored procedure in my java code via JPA and > > > > > try to set a timeout hint by adding > > > > > .setHint("javax.persistence.query.timeout", milliseconds) > > > > > but I am not able to register a timeout. > > > > > I am wondering if there is some derby property or jdbc property I > > > > > should use to activate such behavior. I have tried to google for it > > > > > but I am having extremely bad results... but according to this > > > > > (https://docs.oracle.com/cd/E25178_01/apirefs.1111/e13952/pagehelp/J2EEkodojdbcconfdescriptorDerbyDictionaryBeantitle.html) > > > > > I have to activate it somehow. > > > > > > > > > > Does Derby supports query timeout at all? If so, can you please point > > > > > me to some references ? > > > > > Thanks in advance for any help, > > > > > Marco > > > > > > > > > > > > > > > > >