|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
|
|
ESQL: What is the SQLSTATE? |
« View previous topic :: View next topic » |
Author |
Message
|
jamesyu |
Posted: Wed Feb 17, 2010 1:18 pm Post subject: ESQL: What is the SQLSTATE? |
|
|
Acolyte
Joined: 31 Jan 2007 Posts: 70
|
Hi All,
I am little confused with the key word "SQLSTATE" when trying to decalre an errror handler such as: DECLARE EXIT HANDLER FOR SQLSTATE LIKE'%'.
Here are my questions:
1. Is this "SQLSTATE" the same as the SQLSTATE funtion?
2. If yes to the question 1, then the ESQL spec says "SQLSTATE is a database state function that returns a 5 character data type of CHARACTER with a default value of ’00000’ (five zeros as a string)." Notice that, it is a 5 characters returned. But if you look at the sample SQL states in the ESQL document, they are 6 characters, such as "Dddddd", "S22033", "MQW001" etc. Is the ESQL spec talking about 2 different SQL states (ie. SQLSTATE vs. SQL state)?
3. If yes to the question 2, meaning SQLSTATE <> SQL state, then where to get this "SQL state"?
Thanks,
James |
|
Back to top |
|
|
mgk |
Posted: Wed Feb 17, 2010 2:14 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003 Posts: 1639
|
Looks like the doc for SQLSTATE needs updating a little. SQLSTATEs can be 5 or 6 characters depending upon how the node is configured. This comes about because the way to handle errors has changed over time. Originally the SQLSTATE function was only used for database exceptions and they were always 5 chars long. However to be able to see them you have to change the node property called "Throw Exceptions on Database Errors" from "true" (the default) to "false". Then after each DB call (SELECT etc) you (the user) has to code a call to SQLSTATE to check if that call had an error. Then in 6.0 of WMB HANDLERS were introduced. There allowed you to check (catch) exceptions for other types of errors as well as DB errors (SQL errors, MQ errors etc). However, to be able to use these new exceptions, you have to leave the "Throw Exceptions on DB Errors" property set to the default of "true". This allows the exceptions to be caught by a handler. And in this case these new exceptions were all 6 chars long, and so a "D" was added to the front of all DB exceptions when the SQLSTATE function is used inside a HANDLER. This allows you to create a HANDLER to handle all DB exception in one go, such as:
Code: |
DECLARE EXIT HANDLER FOR SQLSTATE LIKE 'D%'. |
So, simply put, when used in a HANDLER the SQLSTATE is always 6 chars, but when used outside of a HANDLER (with the "Throw Exceptions on DB Errors" node property set to false) they are still 5 chars to ensure that any pre-6.0 error handling code still works as it always did.
For the record, the recomended approach for all new ESQL code is to leave the "Throw Exceptions on DB Errors" set to the default "true" (and also check the "Treat DB Warnings as Errors" as well if necessary) and use the SQLSTATE function inside a HANDLER to handle DB (and other) exceptions.
I hope this history lesson clears things up a little for you.
Kind Regards, _________________ MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
Back to top |
|
|
mqjeff |
Posted: Wed Feb 17, 2010 6:05 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008 Posts: 17447
|
Of course as usual MGK is technically 100% accurate, but has missed the actual point of the question.
SQLSTATE will return the same values that you will find in ExceptionList for the Number item... i.e. it will return a BIP value if the code you have wrapped in the DECLARE HANDLER throws an Exception. |
|
Back to top |
|
|
mgk |
Posted: Thu Feb 18, 2010 1:43 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003 Posts: 1639
|
Quote: |
SQLSTATE will return the same values that you will find in ExceptionList for the Number item... i.e. it will return a BIP value if the code you have wrapped in the DECLARE HANDLER throws an Exception. |
Hi Jeff, sorry to have to say this, but this is just not correct, someone has misinformed you . SQLSTATEs are separate from and unrelated to BIP message numbers...
Kind Regards, _________________ MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
Back to top |
|
|
mqjeff |
Posted: Thu Feb 18, 2010 6:01 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008 Posts: 17447
|
mgk wrote: |
Quote: |
SQLSTATE will return the same values that you will find in ExceptionList for the Number item... i.e. it will return a BIP value if the code you have wrapped in the DECLARE HANDLER throws an Exception. |
Hi Jeff, sorry to have to say this, but this is just not correct, someone has misinformed you . SQLSTATEs are separate from and unrelated to BIP message numbers...
Kind Regards, |
|
|
Back to top |
|
|
|
|
|
|
Page 1 of 1 |
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|
|