TechTip: SQL Communications Area

[MCWebsite.Staff]

** This thread discusses the article: TechTip: SQL Communications Area **
** This thread discusses the Content article: TechTip: SQL Communications Area0

[matt.haas@thomson.com]

TechTip: SQL Communications Area

** This thread discusses the article: TechTip: SQL Communications Area **
Telling people to use SQLCOD is bad for a couple of reasons. First, when V5R1 was released, IBM started discouraging it's use. The reason they did that is because of the second reason you shouldn't use it: the value it returns can change from release to release for the same error. I got bit by that once and it wasn't pretty (it was also a mad scramble to fix the problem since it was found in production). Luckily, the change we got bit on had to deal with errors resulting from communication problems between servers so data wasn't corrupted. On the other hand, the values returned by SQLSTT are defined by an organization other than IBM and they will not change the meaning of existing error codes. The third reason not to use SQLCOD is that there can be a 1 to many relationship between SQLCOD and SQLSTT which can make error handling more complicated than it needs to be. Matt

[Guest.Visitor]

TechTip: SQL Communications Area

** This thread discusses the article: TechTip: SQL Communications Area **
Hi, to get the message text for an SQL error, I'd suggest to use the SQL command GET DIAGNOSTICS. BTW the SQLCA was enhanced in Release V5R3, i.e. longer field names that are already used in other programming languages such as SQLCODE and SQLSTATE were added. Instead of binary the new numeric fields are defined as Integer. Birgitta

---

Code

[K.Forsythe]

TechTip: SQL Communications Area

** This thread discusses the article: TechTip: SQL Communications Area **
Matt, You are correct that the use of SQLCODE for specific error handling is discouraged. But if you'll notice in the article, SQLCODE is simply being tested for success or failure. Negative return codes will always be a failure, no matter what release of i5/OS you are running. The specific code is only being to generate the text of the error message in a very generic process that is always correct from release to release. That technique will work correctly on any release of the OS. But again, as you point out. For users monitoring for specific errors within their code, SQLSTATE is the better choice. A tech tip explaining how to monitor for specific error codes would be very useful. Perhaps you'd like to write one? Kevin

[K.Forsythe]

TechTip: SQL Communications Area

** This thread discusses the article: TechTip: SQL Communications Area **
Birgitta, Thanks for the good code sample. Get diagnostics is a great tool for error handling. I didn't use it in the tech tip because I specifically needed the SQL Error message ID to retrieve the predefined error message from the QSQLMSGF message file. With the specific error message ID, I can use standard 5250 message handling techniques to send that error message to an error message subfile at the bottom of the display. Retrieving only the text is not as convenient for that. It could still be done using CPF9898, but I'd rather display the actual SQL error ID to the user, instead of just the text. The article does mention the alternate spelling of SQLCOD(SQLCODE), and SQLSTT(SQLSTATE), though not which release they became available. Thanks for providing that information and pointing out the different data types. Thanks, Kevin