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