Iseries Sql Error Codes
Contents |
available on-line and can be displayed and printed from the Display Message Description display. You can access this display by using the CL command Display Message Description (DSPMSGD). SQLCODEs are returned in what is sqlcode in db2 the SQLCA structure. SQLSTATE is an additional return code that provides application programs sqlcode = -501 in sqlrpgle with common return codes for common error conditions found among the IBM relational database systems. SQLSTATEs are particularly useful when sqlcode 100 in as400 handling errors in distributed SQL applications. Every SQLCODE has a corresponding message in message file QSQLMSG in library QSYS. The message ID for any SQLCODE is constructed by appending the absolute value (5 sqlcod in sqlrpgle digits) of the SQLCODE to SQ and changing the third character to 'L' if the third character is a 0. For example, if the SQLCODE is 30070, the message ID is SQ30070. If SQL encounters an error while processing the statement, the first characters of the SQLSTATE are not '00', '01' or '02', and the SQLCODE is a negative number. If SQL encounters a warning but valid
Sqlcode: -206 Sqlstate: 42703 Db2
condition while processing your statement, the SQLCODE is a positive number and bytes one and two of the SQLSTATE are '01'. If your SQL statement is processed without encountering an error or warning condition, the SQLCODE returned is 0 and SQLSTATE is '00000'. If you wish, you can quickly reference Positive SQLCODEs or Negative SQLCODEs. When running in debug mode, SQL places a message corresponding to the SQLCODE in the job log for each SQL statement run. If you are not running in debug mode and get a negative SQLCODE, you will get a message in the job log also. An application can also send the SQL message corresponding to any SQLCODE to the job log by specifying the message ID and the replacement text on the CL commands Retrieve Message (RTVMSG), Send Program Message (SNDPGMMSG), and Send User Message (SNDUSRMSG). SQLSTATE values consist of a two-character class code, followed by a three-character code. The class codes conform to ISO/ANSI standards. The class codes are: 00 Unqualified Successful Completion 01 Warning 02 No Data 03 SQL Statement Not Yet Complete 07 Dynamic SQL Error 08 Connection Exception 09 Triggered Action Exception 0A Feature Not Supported 09 Inval
accessible version of the Information Center, or you may want to navigate directly to the main content page.
Management Document Management Enterprise Resource Planning/Financial General Managed Services/SaaS Career General Case Studies Database Business Intelligence DB2 Microsoft Access MySQL Internet Application Servers Commerce General Portals Protocols Telephony Networking Emulation General Wireless/Mobile Op Systems IBM i (OS/400, i5/OS) Linux/Open Source Microsoft UNIX/AIX Product Reviews Programming APIs Business Integration Change Management CL Dev Tools General Java RPG Scripting SQL Visual Basic Web Languages Security Compliance/Privacy General IBM i (OS/400, i5/OS) Microsoft System Admin General High Availability/Disaster Recovery Performance Monitoring & Tuning TechTips APIs Career CL Collaboration Database Dev Tools Doc Mgmt HA/DR IBM i Internet Java Linux Microsoft Networking Programming RPG Scripting Security SQL Sys Admin Web Languages TechTip: Simple Error Handling in Embedded SQL Tips & Techniques - SQL Written by Guest.Visitor Thursday, 11 August 2005 19:00 View Comments Part of good programming practice is error handling: Was the record found? Does the field contain proper data? Is it possible to add a record?You know the sort.The same is true with embedded SQL. Let's review two simple error-handling techniques for your embedded SQL statements. WHENEVER The simplest way to monitor for SQL errors is to use the SQL WHENEVER statement: WHENEVERtypeaction Type indicates the type of status encountered by the SQL statement. It is one of the following: NOT FOUND--True when the SQL statement finds no data (for example, FETCHing past the end of a result table or no records found for delete) SQLWARNING--True when the SQL statement executes successfully, but with warnings SQLERROR--True when the SQL statement failsAction tells SQL what to do in case of one of the errors: CONTINUE--Continue with the next statement in your program GOTO (or GO TO) label--Go to a point in the program indicated by "label." In RPG, that would be a label with a TAG; in COBOL, a paragraph or section name.A WHENEVER statement affects all SQL statements that follow in your program's listing (regardless of program logic), up to a subsequent WHENEVER statement. For any of the three types you do not code a WHENEVER for, SQL assumes an implicit WHENEVER, using an action of CONTINUE. SQLCODE and SQLSTATE I don't like WHENEVER because it's too general and it uses outdated and vilified programming constructs--GOTO and TAG. Instea