Hi Brian,
Thanks for your reply and attempt to help.
Here's what I do:
Within the database (preparation of the test)
CALL
SQLJ.replace_jar('file:///home/marco/devel/stored_procedures/target/storedprocedure-1.0.jar',
'APP.STORED_PROCS');
CREATE PROCEDURE APP.P_MUI_EXPORT_PIANIFICAZIONE (IN ID_PROMOZIONE BIGINT , IN
ID_COMPRATORE VARCHAR(255), IN CODICE_UTENTE VARCHAR(50) )
PARAMETER STYLE JAVA
MODIFIES SQL DATA
LANGUAGE JAVA
EXTERNAL NAME 'com.foo.Procedures.exportPianificazione';
Here's the relevant parts of the (dummy) stored procedure:
public static void exportPianificazione(long idPromozione, String idCompratori,
String codiceUtente)
throws DbPromoException {
try (Connection conn = DriverManager.getConnection("jdbc:default:connection");)
{
new Utils().dummyExportPianificazione("APP", conn, idPromozione, idCompratori,
codiceUtente);
} catch (Exception e) {
log.log(Level.SEVERE, "Error writing values in mui_check_testata", e);
throw new DbPromoException("Error writing values in mui_check_testata : " +
e.getMessage(), e);
}
}
within the Utils class:
public void dummyExportPianificazione(String schema, Connection conn, long
idPromozione, String idCompratori,
String codiceUtente) throws SQLException, InterruptedException {
String query = "insert into "+schema+".MUI_CHECK_COMPRATORI (ID, ID_PROMOZIONE,
ID_COMPRATORE, ESITO, CODICE_UTENTE_INSERIMENTO, CODICE_UTENTE_AGGIORNAMENTO, DATA_INSERIMENTO,
DATA_AGGIORNAMENTO ) values ("
+ "NEXT VALUE FOR MUI_CHECK_COMPRATORI_ID_SEQ, ?, ?, ?, ?, ?, ?, ?)";
String delete = "delete from " + schema
+ ".MUI_CHECK_COMPRATORI where id_promozione = ? and id_compratore = ?";
String esito=getEsito(); //random generation of a result
String[] compratori = idCompratori.split(",");
TimeUnit.SECONDS.wait(10);
....
}
In my application call :
...
milliseconds = 1;
...
getEm().createStoredProcedureQuery(Constants.SP_EXPORT_PIANIFICAZIONE)
.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
.registerStoredProcedureParameter(2, String.class, ParameterMode.IN)
.registerStoredProcedureParameter(3, String.class,
ParameterMode.IN).setParameter(1, myPromo)
.setParameter(2, idCompratori).setParameter(3, username)
.setHint("javax.persistence.query.timeout", milliseconds)
.execute();
....
Activating the JPA logs (EclipseLink) to level FINEST I can see that it takes
10 seconds between the call to the stored procedure and the the next step of my
application.
I have also checked that the version of EclipseLink that I am using supports this
hint and that the default unit of the hint is millisecons (but even if it was
seconds, it should throw the timeout as 1<10).
What is a real puzzle to me is the fact that I am not getting an exception when I call
the stored procedure from the application thus my doubt: do I have to
"activate" something in the jdbc driver/database in order to actually fire the
timeout ?
The other option is that I did not understand at all how this hint should work
(which, at this point, would make sense)
Thanks in advance
On Apr 19 2022, at 10:05 pm, Bryan Pendleton <bpendleton.de...@gmail.com> wrote:
I think that Marco is trying to *test* how his code handles a timeout
exception, but he doesn't know a way to force a timeout exception to occur
during his test.
I don't know a trivial way to do this, either.
I think you might have to write a more complex test program, for example you
could have two threads, and two transactions, and in the first thread you could
begin a transaction and update a record BUT NOT COMMIT.
Then, in the second thread, you could begin a transaction and call your stored
procedure to run a query against that record, but the query should block
because the record is updated by the first transaction in the first thread.
Then, after some time expires, you should get the timeout exception in your
stored procedure.
thanks,
bryan
On Tue, Apr 19, 2022 at 9:30 AM Marco Ferretti <marco.ferre...@gmail.com
(mailto:marco.ferre...@gmail.com)> wrote:
Hi Rick,
thanks for taking the time to reply.
I am not 100% sure what you mean. My application uses JPA and the Entity
Manager is getting the connection from the container's datasource: I do not
have (direct) access to the java.sql.Statement.
What I (would like to) do is calling the a stored procedure in this way:
getEm().createStoredProcedureQuery(Constants.SP_EXPORT_PIANIFICAZIONE)
.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN)
.registerStoredProcedureParameter(2, String.class, ParameterMode.IN)
.registerStoredProcedureParameter(3, String.class,
ParameterMode.IN).setParameter(1, myPromo)
.setParameter(2, idCompratori).setParameter(3, username)
.setHint("javax.persistence.query.timeout", milliseconds)
.execute();
As far as the JPA API specification (and EclipseLink implementation) goes, the
".setHint("javax.persistence.query.timeout", milliseconds)" should be
equivalent to what you suggest.
The problem is that I cannot make the stored procedure call to throw a
QueryTimeoutException even if my dummy procedure (in derby) waits for 10
seconds and I set the timeout to 1 millisecond.
I am using, in my test environment, Apache Tomee (java 8) , derby client 10.14 and
a docker image with a derby network server
(https://github.com/az82/docker-derby/blob/master/Dockerfile
(https://link.getmailspring.com/link/3174774e-c496-42ab-83b1-dfe1a95ae...@getmailspring.com/0?redirect=https%3A%2F%2Fgithub.com%2Faz82%2Fdocker-derby%2Fblob%2Fmaster%2FDockerfile&recipient=ZGVyYnktdXNlckBkYi5hcGFjaGUub3Jn))
.
As far as my knowledge goes, and it's not that far, the reasons I am not
getting a timeout are :
I am not correctly using the hint
I am not correctly setting up the datasource
There is no timeout because the stored procedure terminates within the given
timeout.
On point 3: in my (dummy) implementation of the stored procedure i do, before
doing anything else, a TimeUnit.SECONDS.wait(10);
On point 1 : I am lost. I think I am using the hint correctly (at least the API
docs say so)
On point 2: I am setting up the datasource with the minimum configuration
possible :
<Resource id="jdbc/myDatasource" type="DataSource"> JdbcDriver
org.apache.derby.jdbc.ClientDriver JdbcUrl jdbc:derby://database:1527/dbpromo;create=false UserName DBPROMO
Password dbpromo </Resource>
Could it be that I am failing to activate something on the Derby side ?
Thanks in advance
Marco
On Apr 19 2022, at 4:57 pm, Rick Hillegas <rick.hille...@gmail.com
(mailto:rick.hille...@gmail.com)> wrote:
java.sql.Statement.setQueryTimeout(int) should do the trick.
On 4/19/22 3:30 AM, Marco Ferretti wrote:
Hi all,
I am trying to simulate a query timeout in a stored procedure by simply adding
a delay in my (test) jar.
I then am launching the stored procedure in my java code via JPA and try to set
a timeout hint by adding
.setHint("javax.persistence.query.timeout", milliseconds)
but I am not able to register a timeout.
I am wondering if there is some derby property or jdbc property I should use to
activate such behavior. I have tried to google for it but I am having extremely
bad results... but according to this
(https://docs.oracle.com/cd/E25178_01/apirefs.1111/e13952/pagehelp/J2EEkodojdbcconfdescriptorDerbyDictionaryBeantitle.html)
I have to activate it somehow.
Does Derby supports query timeout at all? If so, can you please point me to
some references ?
Thanks in advance for any help,
Marco
