Re: query timeout
Hi all,
the issue was not with the database nor with the jdbc driver but with the
implementation of call itself :
The implementation of execute does not close the connection
@Override
public boolean execute() {
try {
...
} catch (LockTimeoutException exception) {
throw exception;
} catch (PersistenceException exception) {
setRollbackOnly();
throw exception;
} catch (IllegalStateException e){
setRollbackOnly();
throw e;
} catch (RuntimeException exception) {
setRollbackOnly();
throw new PersistenceException(exception);
}
}
instead, using executeUpdate ensures that the connection is closed :
@Override
public int executeUpdate() {
try {
} catch (LockTimeoutException exception) {
throw exception;
} catch (PersistenceException e) {
setRollbackOnly();
throw e;
} catch (IllegalStateException e){
setRollbackOnly();
throw e;
} catch (RuntimeException exception) {
setRollbackOnly();
throw new PersistenceException(exception);
} finally {
close(); // Close the connection once we're done.
}
}
Hope this helps someone ... i've banged my head on the wall way too much time
on this.
Regards
On Apr 21 2022, at 5:30 pm, Marco Ferretti wrote:
> Hi Brian,
> indeed: next steps are testing with alternative jpa implementations.
> Will keep you posted :D
>
> On Apr 21 2022, at 5:18 pm, Bryan Pendleton
> wrote:
> > 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
> > 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
> > > 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
> > > wrote:
> > >
> > > I didn’t
Re: query timeout
Hi Brian,
indeed: next steps are testing with alternative jpa implementations.
Will keep you posted :D
On Apr 21 2022, at 5:18 pm, Bryan Pendleton wrote:
> 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
> 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
> > 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 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
> > 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 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
Re: query timeout
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 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
> 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 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
> 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 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
> >>>
Re: query timeout
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
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 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 (mailto: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 > > (mailto: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
Re: query timeout
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
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
> 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
Re: query timeout
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 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 :
org.apache.derby
derby
10.14.2.0
org.postgresql
postgresql
42.3.4
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 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) )
Re: query timeout
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 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 :
> >
> > org.apache.derby
> > derby
> > 10.14.2.0
> >
> >
> > org.postgresql
> > postgresql
> > 42.3.4
> >
> >
> > 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
> > wrote:
> >> Hi Brian,
> >> Thanks for your reply and attempt to help.
> >>
> >> Here's what I do:
> >> Within the database (preparation of the test)
> >> CALL
> >>
Re: query timeout
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 :
org.apache.derby
derby
10.14.2.0
org.postgresql
postgresql
42.3.4
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 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:
Re: query timeout
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 :
org.apache.derby
derby
10.14.2.0
org.postgresql
postgresql
42.3.4
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 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,
>
Re: query timeout
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 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 (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
Re: query timeout
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
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
> )
> .
>
> As far as my knowledge goes, and it's not that far, the reasons I am not
> getting a timeout are :
>
>1. I am not correctly using the hint
>2. I am not correctly setting up the datasource
>3. 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 :
>
> JdbcDriver
> org.apache.derby.jdbc.ClientDriver JdbcUrl
> jdbc:derby://database:1527/dbpromo;create=false UserName DBPROMO Password
> dbpromo
>
> 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 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./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
> >
>
> [image: Sent from Mailspring]
Re: query timeout
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=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 :
JdbcDriver
org.apache.derby.jdbc.ClientDriver JdbcUrl
jdbc:derby://database:1527/dbpromo;create=false UserName DBPROMO Password
dbpromo
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 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./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
> >
>
Re: query timeout
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./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
