SQLCODE: LET'S USE IT WELL

Last Updated on 3 March 2020 by Roberto De Pedrini

I don’t have a habit of browsing through the code written by colleagues, but sometimes it touches me. I often notice an error in the use of SQLCODE: it is important to clarify that the test “SQLCODE <> 0” is wrong. Let’s re-read what is written in the “SQL reference” manual:

The database manager sets SQLCODE after each SQL statement (…) is executed. SQLCODE is set as follows:

• If SQLCODE = 0 and SQLWARN0 is blank, execution was successful.
• If SQLCODE = 100, no data was found. For example, a FETCH statement returned no data, because the cursor was positioned after the last row of the result table.
• If SQLCODE> 0 and not = 100, execution was successful with a warning.
• If SQLCODE = 0 and SQLWARN0 = ‘W’, execution was successful with a warning.
• If SQLCODE <0, execution was not successful.

From the parts I have highlighted it is clear that the test “SQLCODE <> 0” is wrong, because only a negative SQLCODE signals a failed execution, while a positive SQLCODE signals a successful execution. So the right control (e.g. after a FETCH) is:

if sqlCode <0; // Execution failed.
  dump (a);
  leave;
elseif sqlCode = 100; // No data.
  leave;
else; // FETCH successful.
  // Processing the row.
endif;

Related Posts

DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more