I'm still unclear as to what configuration settings Derby is using during your test.
Note that the default timeout setting for Derby is one minute: https://db.apache.org/derby/docs/10.15/devguide/cdevconcepts16400.html If you are expecting a timeout after 10 seconds, you have to figure out a way to set the Derby timeout configuration to 10 seconds. If your JPA approach is not able to do that (as Rick indicated, this could be a bug in the JPA library you are using?), then you will have to configure the Derby timeout setting using an alternate approach. There are various ways you can configure Derby, start here: https://db.apache.org/derby/docs/10.15/devguide/cdevsetprop34818.html thanks, bryan On Thu, Apr 21, 2022 at 7:37 AM Marco Ferretti <marco.ferre...@gmail.com> wrote: > > Hi all, > > another test another "success". I tried with an Oracle (XE) database and JDBC > driver 8 : > > em.createStoredProcedureQuery("DBMS_SESSION.SLEEP(5)") > .setHint("javax.persistence.query.timeout", 1) > .execute(); > > Apr 21, 2022 4:35:05 PM org.jboss.weld.bootstrap.WeldStartup <clinit> > INFO: WELD-000900: 2.4.4 (Final) > Apr 21, 2022 4:35:05 PM org.jboss.weld.bootstrap.WeldStartup startContainer > INFO: WELD-000101: Transactional services not available. Injection of @Inject > UserTransaction not available. Transactional observers will be invoked > synchronously. > WARNING: An illegal reflective access operation has occurred > WARNING: Illegal reflective access by org.jboss.classfilewriter.ClassFile$1 > (file:/home/marco/.m2/repository/org/jboss/weld/se/weld-se/2.4.4.Final/weld-se-2.4.4.Final.jar) > to method java.lang.ClassLoader.defineClass(java.lang.String,byte[],int,int) > WARNING: Please consider reporting this to the maintainers of > org.jboss.classfilewriter.ClassFile$1 > WARNING: Use --illegal-access=warn to enable warnings of further illegal > reflective access operations > WARNING: All illegal access operations will be denied in a future release > Apr 21, 2022 4:35:06 PM org.jboss.weld.environment.se.WeldContainer > fireContainerInitializedEvent > INFO: WELD-ENV-002003: Weld SE container d725b64c-e888-4a45-845c-38cfe0b1bb9b > initialized > [EL Info]: 2022-04-21 16:35:06.4--ServerSession(1123236701)--EclipseLink, > version: Eclipse Persistence Services - 2.7.3.v20180807-4be1041 > [EL Info]: connection: 2022-04-21 > 16:35:06.804--ServerSession(1123236701)--/file:/home/marco/devel/test_jpa/target/classes/_test-pu > login successful > [EL Warning]: 2022-04-21 16:35:07.491--UnitOfWork(808653065)--Exception > [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.v20180807-4be1041): > org.eclipse.persistence.exceptions.DatabaseException > Internal Exception: java.sql.SQLException: ORA-06550: line 1, column 7: > PLS-00801: internal error [22503] > ORA-06550: line 1, column 7: > PL/SQL: Statement ignored > > Error Code: 6550 > Call: BEGIN DBMS_SESSION.SLEEP(5)(); END; > Query: ResultSetMappingQuery() > [EL Info]: connection: 2022-04-21 > 16:35:07.497--ServerSession(1123236701)--/file:/home/marco/devel/test_jpa/target/classes/_test-pu > logout successful > Apr 21, 2022 4:35:07 PM org.jboss.weld.environment.se.WeldContainer shutdown > INFO: WELD-ENV-002001: Weld SE container d725b64c-e888-4a45-845c-38cfe0b1bb9b > shut down > > > > On Apr 20 2022, at 6:28 pm, Marco Ferretti <marco.ferre...@gmail.com> wrote: > > I didn’t because it works with Postgres jdbc. I’ll try with other > databases/jdbc drivers not sure if that can help > > > Il giorno mer 20 apr 2022 alle 18:08 Rick Hillegas <rick.hille...@gmail.com> > ha scritto: > > That suggests to me that the problem is not in the Derby layer. The > problem is in JPA's support for Derby. JPA should be able to take > advantage of java.sql.Statement.setQueryTimeout(). Have you brought this > issue to the JPA community? > > On 4/20/22 7:52 AM, Marco Ferretti wrote: > > Hi Rick, > > > > thanks for taking the time to reply. > > I have looked at the link you provide: the method that sets the values in > > persistence.xml should affect all queries attached to that persistence > > unit; the second ("Setting the Query timeout on the single Query") method > > is the one I am using, while the third option is, AFAIK, out of scope in my > > case. > > > > Marco. > > > > On Apr 20 2022, at 4:46 pm, Rick Hillegas <rick.hille...@gmail.com> wrote: > >> I'm not an expert on using JPA. The following link suggests that there > >> is a way to configure query timeout in an xml-formatted JPA > >> configuration file: > >> http://www.mastertheboss.com/hibernate-jpa/jpa-configuration/3-ways-to-set-a-query-timeout-for-jpa-hibernate-applications/ > >> > >> On 4/20/22 5:59 AM, Marco Ferretti wrote: > >>> 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 > >>>>>>>> > > > > -- > Sent from Gmail Mobile
