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