SQL return codes: Difference between revisions

Line 1:

{{ambox

|image=[[Image:Wikibooks-logo.svg|48px|Wikibooks logo]]

Line 4:

Line 7:

|text='''This article has now been successfully [[b:Help:Transwiki|imported]] to [[Wikibooks]].''' You can find it on Wikibooks under the name '''[[b:SQL return codes|SQL return codes]].''' <br/> <small>If this page can be [[WP:EDIT|rewritten]] into an encyclopedic article, please do so and remove this message and/or add a link to the Wikibook using {{tl|wikibooks}}.</small>

}}[[Category:Articles copied to Wikibooks in need of cleanup]]

'''SQL Return Codes''' are used on a day to day basis for the diagnosis of programming failures as a result of SQL calls by [[IBM DB2|DB2]] [[computer programs]]. An important feature of [[IBM DB2|DB2]] programs is the error processing. The error diagnostic containing the SQL Return Code is held in the field '''SQLCODE''' within the DB2 [[SQLCA]] block.

== SQLCA ==

The SQL communications area (SQLCA) structure is used within the [[IBM DB2|DB2]] program to return error information to the [[application program]]. This information in the SQLCA and the SQLCODE field is updated after every [[Application programming interface|

API]] call for the SQL statement.

== SQLCODE ==

The SQLCODE field contains the SQL return code. The code can be zero (0), negative or positive.

0 means successful execution.

Negative means unsuccessful with an error.

An example is -911 which means a timeout has occured with a rollback.

Positive means successful execution with a warning.

An example is +100 which means no rows found.

Here is a more comprehensive list of the SQLCODEs for DB2:

=== Zero (Successful) ===

0 Successful

=== Negative values (Errors) ===

-102 String constant is too long.

-117 The number of values in the INSERT does not match the number of columns.

-180 Bad data in Date/Time/Timestamp.

-181 Bad data in Date/Time/Timestamp.

-199 Illegal use of the specified keyword.

-204 Object not defined to DB2.

-205 Column name not in table.

-206 Column does not exist in any table of the SELECT.

-216 Not the same number of expressions on both sides of the comparison in a SELECT .

-224 FETCH cannot make an INSENSITIVE cursor SENSITIVE.

-229 The locale specified in a SET LOCALE statement was not found.

-305 Null indicator needed.

-311 Varchar, insert or update. -LEN field with the right data length not set.

-482 The procedure returned no locators.

-501 Cursor not open on FETCH.

-502 Opening cursor that is already open.

-503 Updating column needs to be specified.

-530 Referential integrity preventing the INSERT/UPDATE

-532 Referential integrity (DELETE RESTRICT rule) preventing the DELETE.

-536 Referential integrity (DELETE RESTRICT rule) preventing the DELETE.

-545 Check constraint preventing the INSERT/UPDATE.

-747 The table is not available.

-803 Duplicate key on insert or update.

-805 DBRM or package not found in plan.

-811 More than one row retrieved in SELECT INTO.

-818 Plan and program: timestamp mismatch.

-904 Unavailable resource. Someone else is locking your data.

-911 Deadlock or timeout. Rollback has been done.

-913 Deadlock or timeout. No rollback.

-922 Authorization needed.

-927 The language interface was called but no connection had been made.

=== Positive Values (Warnings) ===

+100 Row not found or end of cursor.

+222 Trying to fetch a row within a DELETE statement.

+223 Trying to fetch a row within an UPDATE statement.

+231 FETCH after a BEFORE or AFTER but not on a valid row.

+304 Value cannot be assigned to this host variable because it is out of range.

+802 The null indicator was set to -2 as an arithmetic statement didn't work.

@@ Line 1: / Line 1: @@
+<!-- Please do not remove or change this AfD message until the issue is settled -->
+{{AfDM|page=SQL Return Codes|date=2007 October 18|substed=yes}}
+<!-- End of AfD message, feel free to edit beyond this point -->
 {{ambox
 |image=[[Image:Wikibooks-logo.svg|48px|Wikibooks logo]]
@@ Line 4: / Line 7: @@
 |text='''This article has now been successfully [[b:Help:Transwiki|imported]] to [[Wikibooks]].''' You can find it on Wikibooks under the name '''[[b:SQL return codes|SQL return codes]].''' <br/> <small>If this page can be [[WP:EDIT|rewritten]] into an encyclopedic article, please do so and remove this message and/or add a link to the Wikibook using {{tl|wikibooks}}.</small>
 }}[[Category:Articles copied to Wikibooks in need of cleanup]]
+'''SQL Return Codes''' are used on a day to day basis for the diagnosis of programming failures as a result of SQL calls by [[IBM DB2|DB2]] [[computer programs]]. An important feature of [[IBM DB2|DB2]] programs is the error processing. The error diagnostic containing the SQL Return Code is held in the field '''SQLCODE''' within the DB2 [[SQLCA]] block.
+== SQLCA ==
+The SQL communications area (SQLCA) structure is used within the [[IBM DB2|DB2]] program to return error information to the [[application program]]. This information in the SQLCA and the SQLCODE field is updated after every [[Application programming interface|
+API]] call for the SQL statement.
+== SQLCODE ==
+The SQLCODE field contains the SQL return code. The code can be zero (0), negative or positive.
+means successful execution.
+   Negative means unsuccessful with an error.
+   An example is -911 which means a timeout has occured with a rollback.
+   Positive means successful execution with a warning.
+   An example is +100 which means no rows found.
+Here is a more comprehensive list of the SQLCODEs for DB2:
+=== Zero (Successful) ===
+    Successful
+=== Negative values (Errors) ===
+  -102  String constant is too long.
+  -117  The number of values in the INSERT does not match the number of columns.
+  -180  Bad data in Date/Time/Timestamp.
+  -181  Bad data in Date/Time/Timestamp.
+  -199  Illegal use of the specified keyword.
+  -204  Object not defined to DB2.
+  -205  Column name not in table.
+  -206  Column does not exist in any table of the SELECT.
+  -216  Not the same number of expressions on both sides of the comparison in a SELECT .
+  -224  FETCH cannot make an INSENSITIVE cursor SENSITIVE.
+  -229  The locale specified in a SET LOCALE statement was not found.
+  -305  Null indicator needed.
+  -311  Varchar, insert or update. -LEN field with the right data length not set.
+  -482  The procedure returned no locators.
+  -501  Cursor not open on FETCH.
+  -502  Opening cursor that is already open.
+  -503  Updating column needs to be specified.
+  -530  Referential integrity preventing the INSERT/UPDATE
+  -532  Referential integrity (DELETE RESTRICT rule) preventing the DELETE.
+  -536  Referential integrity (DELETE RESTRICT rule) preventing the DELETE.
+  -545  Check constraint preventing the INSERT/UPDATE.
+  -747  The table is not available.
+  -803  Duplicate key on insert or update.
+  -805  DBRM or package not found in plan.
+  -811  More than one row retrieved in SELECT INTO.
+  -818  Plan and program: timestamp mismatch.
+  -904  Unavailable resource. Someone else is locking your data.
+  -911  Deadlock or timeout. Rollback has been done.
+  -913  Deadlock or timeout. No rollback.
+  -922  Authorization needed.
+  -927  The language interface was called but no connection had been made.
+=== Positive Values (Warnings) ===
+  +100  Row not found or end of cursor.
+  +222  Trying to fetch a row within a DELETE statement.
+  +223  Trying to fetch a row within an UPDATE statement.
+  +231  FETCH after a BEFORE or AFTER but not on a valid row.
+  +304  Value cannot be assigned to this host variable because it is out of range.
+  +802  The null indicator was set to -2 as an arithmetic statement didn't work.

SQL return codes: Difference between revisions

wikademia>Addhoc