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
