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