| Author |
Message
|
| gheekshike |
 Posted: Thu Dec 13, 2012 2:28 am Post subject: Database connectivity in ESQL |
 |
|
Novice
Joined: 13 Dec 2012
Posts: 11
|
How do i check the database connectivity before hit the database in ESQL?
and if there is no connectivity how can i handle the Exception.
Plz help me out.
Thanks in Advance. |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Thu Dec 13, 2012 4:20 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Well, if I assume that you are using version 5.0, which I really should assume since you've not specified the version you're using and everyone who forgets to specify the version is *always* using a really horribly outdated version, then there's nothing you can do here.
If, however, you're using a proper and modern version, ESQL allows you to declare a handler.
Happy Reading! |
|
| Back to top |
|
|
| gheekshike |
| Posted: Thu Dec 13, 2012 4:29 am Post subject: |
|
|
Novice
Joined: 13 Dec 2012
Posts: 11
|
Hi,
I'm new to message broker. I'm using MB6.1 version.
How do i check the database connectivity before hit the database in ESQL?
and if there is no connectivity how can i handle the Exception.
Plz help me out.
Thanks in Advance. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Dec 13, 2012 4:42 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
By reading, thinking, trying, and repeating.
But really.
Suppose you have a magic esql statement, something that lets you write code like this:
| Code: |
IF magicDSNISACTIVEANDAVAILABLE(myDSNName) THEN
Select T.* from Database.myTable as T;
end if; |
This provides you absolutely no value whatsoever.
Because it's entirely possible that the dsn will stop being available in the time it takes for the broker to finish evaluating the IF clause and before it starts evaulating the select statement.
So just because you know that the database is available and active at time T0, that tells you *nothing* about the availability of the database at time T1.
Otherwise, I can not be more specific about how to detect an error than using the words 'declare handler'. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Dec 13, 2012 4:46 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| gheekshike wrote: |
| How do i check the database connectivity before hit the database in ESQL? |
Why do you want to? What does this achieve except wasting processing time by giving you the same information you'll obtain from a database operation?
| gheekshike wrote: |
| if there is no connectivity how can i handle the Exception. |
The same way you handle all other broker exceptions. Literally. It's just another exception inside broker.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| visasimbu |
| Posted: Sat Dec 15, 2012 12:16 am Post subject: |
|
|
Disciple
Joined: 06 Nov 2009
Posts: 171
|
Hi gheekshike,
In windows, you can check the DB connectivity of DSN under the administrative tools in the control panel. |
|
| Back to top |
|
|
| Vitor |
| Posted: Sat Dec 15, 2012 5:48 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| visasimbu wrote: |
| In windows, you can check the DB connectivity of DSN under the administrative tools in the control panel. |
If you actually read the thread rather than look at it, the OP is trying to check the connectivity programatically from within ESQL not generally.
I assume you're not proposing ESQL to move the mouse and double click things?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Sat Dec 15, 2012 10:32 am Post subject: Re: Database connectivity in ESQL |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| gheekshike wrote: |
| How do i check the database connectivity before hit the database in ESQL? |
That depends on whether the node acquired the connection and then lost it, or never acquired it at all. A node typically attempts to establish connectivity before executing its ESQL. If that fails, then the Exception also occurs before executing the ESQL, so there's very little the ESQL could do about it. The message flow would have to handle that Exception the same as any other, either through the node's Failure terminal, or through an upstream node's declared handler or Catch terminal.
What the message flow actually does with the Exception depends on the business requirements. |
|
| Back to top |
|
|
| arunkumar1989 |
| Posted: Mon Dec 17, 2012 3:20 am Post subject: |
|
|
Voyager
Joined: 21 Nov 2012
Posts: 98
Location: Chennai
|
Hi All,
Actual requirement is
Need to log every exception in db. in this case if db is stoped mean need to send a mail,
here i want to check whether db is running or not before do db operations(INSERT,SELECT,PASSTHROW,..)
How can i find whether my db is running or not ....
Thanks to all.  
_________________
Being in a crowd when you are alone is ignorance. Enlightenment is being alone in a crowd; a feeling of oneness in a crowd. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Dec 17, 2012 5:39 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| arunkumar1989 wrote: |
| How can i find whether my db is running or not .... |
You've answered your own question.
| arunkumar1989 wrote: |
| Need to log every exception in db. |
Which is exactly what we've been advising you to do in this thread.
Now if you mean this literally:
| arunkumar1989 wrote: |
| in this case if db is stoped mean need to send a mail |
and if you need to send a mail in the event that the database is stopped but not if (for example) your INSERT has violated a duplicate key condition then that's a simple check of SQLCODE in your code.
If you want different advice you need to explain clearly how what you're currently being advised to do doesn't meet this:
| arunkumar1989 wrote: |
| here i want to check whether db is running or not before do db operations(INSERT,SELECT,PASSTHROW,..) |
without using any phrase along the lines of "the requirement is to check first" or "the client wants the check done first" and you need to explain how you would handle the scenario where you check the database is running (somehow), do a SELECT to prove you need to do an INSERT not an UPDATE, then perform the INSERT only to have it fail because the database has stopped.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| arunkumar1989 |
| Posted: Mon Dec 17, 2012 8:38 pm Post subject: |
|
|
Voyager
Joined: 21 Nov 2012
Posts: 98
Location: Chennai
|
now i will say very clear.
I wants to log every exception...in this case how can i store my database exception.
_________________
Being in a crowd when you are alone is ignorance. Enlightenment is being alone in a crowd; a feeling of oneness in a crowd. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Dec 18, 2012 12:03 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
The following points come to mind.
1) Lookup how to serialise/serialize a message tree.
2) Lookup how to traverse and exceptionList to extract dignificant data
3) Lookup the BrokerVariables (eg BrokerName etc) available to ESQL
Then put that together and wave the magic wand called 'experience' and bingo you will have your solution.
Oh, this -> http://www.mqseries.net/phpBB2/viewtopic.php?t=58663 is also relevant to what I would do if I was asked to do what you are doing.
Please don't ask us to code this for you as we won't (unless you pay one of us to do it for you. I hear Vitor has a Christmas special of $800/day plus expenses  )
Please do try of the suggestions you have been given rather than wanting it done for you. That way you can acquire the Broker Magic for yourself.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Dec 18, 2012 5:50 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| arunkumar1989 wrote: |
| I wants to log every exception...in this case how can i store my database exception. |
I would say some kind of INSERT statement is what you're looking for there. 
I echo the valuable points of my worthy associate, except to debunk the rumor I offer discount rates.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue Dec 18, 2012 5:54 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Vitor wrote: |
| except to debunk the rumor I offer discount rates. |
If nothing else, he quotes the rate in the wrong currency. |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Dec 18, 2012 6:12 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
| Vitor wrote: |
| except to debunk the rumor I offer discount rates. |
If nothing else, he quotes the rate in the wrong currency. |
I accept payment in a variety of currencies. In these uncertain economic times it's just a wise precaution.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
