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 <[email protected]> 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 <[email protected]>
> > 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 <[email protected]>
> >> 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 <[email protected]
> >>> (mailto:[email protected])> 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/[email protected]/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 <[email protected]
> >>>> (mailto:[email protected])> 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
> >>>>>>
> >>>
> >
>
