Hi,
my system : AAOD 6.00.11.03 - java 6 - EPM 6.0.1.0.0801


package connectivity.db.operations;

import gen.utility.QueryTimeoutSec;

import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
* This class is generated automatically. Manual edits must be outside of the tagged
* areas (for example, "START:CLASS:..." and "END:CLASS:..."). Changes within the
* tag areas will be overwritten when the database operation is regenerated.
* Last generated by Orchestration Designer at: 3 GENNAIO 2013 18.29.25 CET
*/
public class PROCEDURA_PROD extends com.avaya.sce.runtime.connectivity.db.DbQuery {

//{{START:CLASS:FIELDS
//}}END:CLASS:FIELDS

/**
* Constructor for PROCEDURA_PROD.
* Last generated by Orchestration Designer at: 3 GENNAIO 2013 18.29.25 CET
*/
public PROCEDURA_PROD( com.avaya.sce.runtimecommon.IRuntimeSession mySession ) {
//{{START:CLASS:CONSTRUCTOR
super(mySession);

setDataSourceName("jdbc/db_ctiprod_ulluser");
setDbObjectName("CTI_IVRM.GET_INFO_POSTIT");
setOperation(4);
setReturnType(1);
setSQLQuery("{ call CTI_IVRM.GET_INFO_POSTIT ( ?, ?, ?, ?, ?) }");

// List variable that may be used
java.util.List list;

// Parameters
com.avaya.sce.runtime.connectivity.db.DbQueryParam parameter;
addParameter(new com.avaya.sce.runtime.connectivity.db.DbQueryParam("IN_TEL",1,12, "DB_PROCEDURE_DATA:IN_TEL"));
addParameter(new com.avaya.sce.runtime.connectivity.db.DbQueryParam("IN_TIPO_CLIENTE",1,12, "DB_PROCEDURE_DATA:IN_TIPO_CLIENTE"));
addParameter(new com.avaya.sce.runtime.connectivity.db.DbQueryParam("IN_PROFILO",1,12, "DB_PROCEDURE_DATA:IN_PROFILO"));
addParameter(new com.avaya.sce.runtime.connectivity.db.DbQueryParam("IN_RIQUALIFICATO",1,12, "DB_PROCEDURE_DATA:IN_RIQUALIFICATO"));
addParameter(new com.avaya.sce.runtime.connectivity.db.DbQueryParam("OUT_RES",4,12, "DB_PROCEDURE_DATA:OUT_RES"));

//}}END:CLASS:CONSTRUCTOR
}
I need to use a query timeout and I add the following code as suggested in this forum

protected void updateBeforeExecute(PreparedStatement statement) throws SQLException
{
int iQueryTimeout = 4;


statement.setQueryTimeout(iQueryTimeout);

System.out.println( "QueryTimeout = " + iQueryTimeout + " sec");


super.updateBeforeExecute(statement);

}

To test if it works fine, I used a stored procedure that waits a few seconds ( ... dbms_lock.sleep(35)... )
Unfortunately, if I call the procedure, the system waits 35 seconds ... ( on catalina.out I am able to see System.out...)
Could you help me ?
Regards

It's between Oracle and the JDBC driver. You may want to google for the same experience other people have. You may want to try other ways to simulate the delay.

Doing other tests, I noticed that the timeout is respected for what concerns the execution of the procedure. What is not respected is the waiting time of the call to the DB. I did this test. In the procedure I put a sleep of 5 seconds. If I put the query timeout to 6 seconds, everything ok, the procedure is called and the code runs successfully. If I put the query timeout to 2 seconds, the procedure is not done correctly, but I will leave anyway waiting for the 5 seconds of sleep ..

Hi,
please look at the log

Procedure wait 5 sec. before you do anything

I have setted 2 sec for query statment ( infact you can see timeout exception )

DD Runtime always wait for 5 sec ( sleep in the stored ) and then catch query timeout ( start 10:47:11 end 10:47:16 ) and not after 2 sec.

I tried to set query timeout to 6 sec and it works fine.

So, in summary, setting the query timeout works fine but the connection remains always waiting for the procedure to end the ongoing operations.
Are you sure that the problem can not be bound at runtime DD ?
I could not find anything about this problem because even setting the query timeout is working properly.
Regards


05/02/2014 10:47:11:842 DEBUG - 6E929F9ADEB504DE51AB1E83D421CFF2:/avp_155_main_vetrina : Executing DB statement : [{ call CTI_IVRM.GET_INFO_POSTIT ( ?, ?, ?, ?, ?) }]
QueryTimeout before = 0
Setting Timeout = 2
QueryTimeout after = 2
05/02/2014 10:47:16:867 INFO - 6E929F9ADEB504DE51AB1E83D421CFF2:/avp_155_main_vetrina : Capturing exception [java.sql.SQLTimeoutException]. Message [ORA-03111: break received on communication channel
]

So you've gotten an exception due to setting the timeout. That's where the operation ends. I am not sure what you mean by connection remains. You would need to handle the exception by using try/catch surrounding the dbop call in the data node to move forward to whatever next in the flow.

Hi,
there is a try/catch in data node but the exception is handled only after the stored procedure has actually finished the sleep 5 seconds.

it is as if the execution of the procedure is properly terminated after 2 seconds imposed, but the 'application is able to handle this exception only after the stored procedure has executed all the code (so sleep (5))

I hope that I am able to explain

I see...it's the 5 second delay that you are seeing. But OD has nothing to do with the 5 seconds. It's not doing anything but waiting for the execution to return. I am baffled by this too. I am pretty sure it's inside the jdbc driver. I will do some research and see what I can find

thank you very much !

Here is a thread I found that is related

https://community.oracle.com/thread/552257

It's a bit old but I think the behavior or mechanism used has changed

my problem is a little different. The timeout on the query works always perfectly. In fact, for example, putting it at 4 seconds on a procedure that lasts for 5 seconds, the request fails (I can verify this because I have no output from the procedure ) even if I have to always wait for 5 seconds to regain control of the application. If I put the query timeout to 5 seconds, the procedure almost always correctly returns the output. If I put the timeout to 6 seconds the output is always correct. It does not seem a problem of lack of precision in the management of the timeout ... if, for example, imposed a query timeout of four seconds but the stored takes 30 seconds to run, I can never take back control of the application before the 30 seconds ( and of course I never get the correct output from the procedure)

I am kind of confused by what you saying. If you set the time out to 5 seconds, and the procedure is take 30 seconds to finish, would you can the cancel exception before the 30 seconds?

DD runtime regains control only after the stored procedure has completed its code ( so if the stored lasts 30 seconds, I am always waiting for 30 sec ...).
What changes, depending on the query timeout, it is only the out result of the stored.
If the query timeout >= stored execution time ---> output is OK
If the query timeout < stored execution time , as soon as I take control of the code ( after 30 sec....) , oracle exception timeout are logged.

It is essential to set a time limit because otherwise the calls all go out to MPP fetch timeout !

Regards

Can you try to make a small application that calls an oracle procedure that lasts 20 seconds, set a query timeout to 5 and check out what's going on ?

I can tell you this is not in OD's control. Our code is simply executing the statement and wait for it's return all in one thread. If JDBC returns with an exception, it would be thrown right away in OD, and you see the exception logged right away. It's JDBC that has to wait till the stored procedure to finish. You may want to try tweak the way how you simulate the delay to see if it makes a difference. If you have Oracle support, you should post a question there.

Hi,
I tried to run code from a thread ( in a servlet I start a thread and after this I wait for a xx time anche check session variable...) and it works
I would like to do this test, I hope to make me understand..maybe an idea is a bit 'crazy ...
From a servlet : create a thread that calls a data_node that contains a node type of database operation and then move immediately to another servlet that waits for a certain period and check the outcome of the stored according to the content of the variables associated to 'object Data_Base ...
Can I do this?
My main problem is that I have a lot of interfaces that use the nodes Data_Base operation and to use the standard method I would have to rewrite almost everything ...
Regards
Marco