| Author |
Message
|
| geethgubi |
 Posted: Tue Jul 14, 2009 1:10 pm Post subject: Stored Procedure - Timeout request in 15 mins |
 |
|
Apprentice
Joined: 18 Dec 2008
Posts: 44
|
| I have a java compute node which connects to oracle database (for performing some task) thro' stored proc. But sometimes the connectivity neither fails nor connects. In that scenerio, My JCN is busy connecting to that database. It not able to pick up my next request. Due to this, am facing lot of problems. So a new reqmnt came in which is the connection request should be given, if the stored proc does not return anything for 15 mins then I want my JCN to relinquish that connectivity request, store that request in the error table and go the next request. Can someone help me out on how to do this? I could not get any idea on how to do it. |
|
| Back to top |
|
 |
| jbanoop |
| Posted: Tue Jul 14, 2009 6:10 pm Post subject: |
|
|
Chevalier
Joined: 17 Sep 2005
Posts: 401
Location: SC
|
| how are you trying to access the database from the JCN ? Can you show us the code snippet that does this ? |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Tue Jul 14, 2009 8:59 pm Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
| jbanoop wrote: |
| how are you trying to access the database from the JCN ? Can you show us the code snippet that does this ? |
this has been discussed a lot in this forum please search "MBSqlStatement" you will get the results
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Wed Jul 15, 2009 2:04 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| Gaya3 wrote: |
| jbanoop wrote: |
| how are you trying to access the database from the JCN ? Can you show us the code snippet that does this ? |
this has been discussed a lot in this forum please search "MBSqlStatement" you will get the results |
Searching for "MBSqlStatement" will show us the code snippet that geethgubi is using? How does that work? |
|
| Back to top |
|
|
| geethgubi |
| Posted: Wed Jul 15, 2009 5:37 am Post subject: cODE SNIPPET |
|
|
Apprentice
Joined: 18 Dec 2008
Posts: 44
|
Class.forName("oracle.jdbc.OracleDriver");
conDb = DriverManager.getConnection(url, props);
storedProc = conDb.prepareCall( "{?= call PKG_abc_ABC.REQUEST(?)}" );
storedProc.registerOutParameter(1, Types.CLOB); // register return function type
//oracle CLOB
CLOB newClob = CLOB.createTemporary(conDb,false, CLOB.DURATION_SESSION);
newClob.setString(1,dupData);
storedProc.setClob(2, newClob ); // IN Parameter 1
// Execute and retrieve the returned value
storedProc.execute(); |
|
| Back to top |
|
|
| jbanoop |
| Posted: Wed Jul 15, 2009 7:36 am Post subject: |
|
|
Chevalier
Joined: 17 Sep 2005
Posts: 401
Location: SC
|
| try utilizing the setQueryTimeout() method on the Statement interface. Should be available on the CallableStatement. |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Wed Jul 15, 2009 7:43 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
As an aside... I am a little interested in knowing how you roll this transaction back if the flow blows up further down the line?
Does this not matter or do you execute a compensating transactionor do something else again? |
|
| Back to top |
|
|
|
|
