| Author |
Message
|
| j.akershoek |
 Posted: Tue Oct 09, 2012 12:59 am Post subject: How to check database connection before doing query? |
 |
|
Newbie
Joined: 09 Oct 2012
Posts: 4
|
We have a flow that regularly polls a stored procedure in a source database (Oracle), to see if there are records available that need to be processed. We use ESQL to fetch the data.
The problem I encounter is this. Every night the source database goes into restricted mode for backup purposes. This causes the broker to lose the connection to the database, so the first time the flow tries to read it again (and the database is up and running once more), we encounter an ORA-03114: Not connected to Oracle-error.
It isn't a big problem, because the next time the flows tries to read the database the connection is established again. But it is annoying, and I am looking for suggestions how to deal with this. Changing the backup procedure isn't an option.
So I am looking for something to check that the broker has a connection to the database, before we try the stored procedure. Is this possible through ESQL? Or are there other suggestions that can solve this?
Regards, Jeroen |
|
| Back to top |
|
 |
| marko.pitkanen |
| Posted: Tue Oct 09, 2012 1:07 am Post subject: |
|
|
Chevalier
Joined: 23 Jul 2008
Posts: 440
Location: Jamsa, Finland
|
Hi Jeroen,
What is the exact version of the broker you are using?
Can you show how have you defined your data source on ODBC -ini?
--
Marko |
|
| Back to top |
|
|
| j.akershoek |
| Posted: Tue Oct 09, 2012 1:22 am Post subject: |
|
|
Newbie
Joined: 09 Oct 2012
Posts: 4
|
Hi Marko,
We are using Broker 6.1.0.10.
I am not sure that odbc.ini gives much information, this is what I get from it:
MQSeries DataDirect Technologies 5.3 32-BIT Oracle (32 bit)
Driver32=D:\Program Files\IBM\MQSI\6.1\bin\UKOR823.DLL
I believe that the setting in ODBC are the default ones.
Regards, Jeroen |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Oct 09, 2012 2:51 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
I forget if the DECLARE HANDLER stuff made it into 6.1.x or if that's only 7.x and later..
but you could do a dummy select on a dummy table and use the declare handler to capture the db error before calling the stored procedure. |
|
| Back to top |
|
|
| marko.pitkanen |
| Posted: Tue Oct 09, 2012 3:10 am Post subject: |
|
|
Chevalier
Joined: 23 Jul 2008
Posts: 440
Location: Jamsa, Finland
|
| mqjeff wrote: |
| DECLARE HANDLER stuff |
That was in my mind too but I'm not sure if flow will find a new connection on the fly or does it need to be reinitialized as an new instance. Well exception can be caught and retry can be perhaps done automatically in this case too.
--
Makro |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Tue Oct 09, 2012 5:31 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| j.akershoek |
| Posted: Tue Oct 09, 2012 10:32 pm Post subject: |
|
|
Newbie
Joined: 09 Oct 2012
Posts: 4
|
Thanks for your suggestions everyone! 
I'll let you know how it goes. |
|
| Back to top |
|
|
| j.akershoek |
| Posted: Tue Oct 23, 2012 4:04 am Post subject: |
|
|
Newbie
Joined: 09 Oct 2012
Posts: 4
|
I've solved this issue by checking the Exception tree if there is an ORA-03114 error. If so I wait one minute (could probably be shorter but I didn't check that) and redirect back to the Database node, to give it another try.
If at that point the problem still persists I continue like before and report an error in the logs.
So, problem solved  |
|
| Back to top |
|
|
|
|
