SQLCODE: LET'S USE IT WELL

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;