ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » ESQL: What is the SQLSTATE?

Post new topic  Reply to topic
 ESQL: What is the SQLSTATE? « View previous topic :: View next topic » 
Author Message
jamesyu
PostPosted: Wed Feb 17, 2010 1:18 pm    Post subject: ESQL: What is the SQLSTATE? Reply with quote

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
View user's profile Send private message
mgk
PostPosted: Wed Feb 17, 2010 2:14 pm    Post subject: Reply with quote

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
View user's profile Send private message
mqjeff
PostPosted: Wed Feb 17, 2010 6:05 pm    Post subject: Reply with quote

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
View user's profile Send private message
mgk
PostPosted: Thu Feb 18, 2010 1:43 am    Post subject: Reply with quote

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
View user's profile Send private message
mqjeff
PostPosted: Thu Feb 18, 2010 6:01 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » ESQL: What is the SQLSTATE?
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.