When is SQLCODE not SQLCODE?

If you have ever written a tidy little subprocedure that does some embedded SQL, then checked SQLCODE afterwards only to find it stubbornly sitting at zero even though the statement clearly failed, you have probably met this gotcha.

Today we are going to answer three questions that trip up plenty of IBM i developers:

• What actually is SQLCODE?
• Can we define it again inside a subprocedure?
• Is a local RPG variable the same thing as the internal SQL variable?

Spoiler: the answer to the last one is a very firm “no”. Let’s unpack why.

What is SQLCODE?

When you embed SQL in an *SQLRPGLE program the precompiler does a lot of behind-the-scenes work for you. One of the things it quietly slips in is the SQLCA (SQL Communications Area). This is a data structure that DB2 updates after every executable SQL statement.

Inside that structure lives SQLCODE (and its overlay SQLCOD). After your statement runs you get:

• 0 – everything was fine
• +100 – no row found (or end of cursor)
• positive value – warning
• negative value – proper error

You do not normally declare SQLCODE yourself. The precompiler provides it automatically as part of the SQLCA (unless you deliberately switch to SET OPTION SQLCA = *NO, in which case it declares standalone DCL-S SQLCODE INT(10) and DCL-S SQLSTATE CHAR(5) for you).

In normal everyday coding you just use it:

exec sql 
  select name into :custName 
  from customer 
  where custid = :searchId;

if SQLCODE < 0;
  // handle error
elseif SQLCODE = 100;
  // not found
endif;

Simple. Until you put the same logic inside a subprocedure and decide to “help” by declaring your own version.

The subprocedure trap – trying to define SQLCODE again

Here is the classic mistake:

dcl-proc GetCustomerName export;
  dcl-pi *n;
    inCustId   zoned(6) const;
    outName    char(30);
    outFound   ind;
  end-pi;

  dcl-s SQLCODE int(10);          // <-- "I'll just define it here so it's local and tidy"

  exec sql 
    select name into :outName 
    from customer 
    where custid = :inCustId;

  if SQLCODE = 0;
    outFound = *on;
  elseif SQLCODE = 100;
    outFound = *off;
  else;
    outFound = *off;
    // log the real error? too late...
  endif;

  return;
end-proc;

Compile it. It runs. The SELECT fails with a -204 (object not found) or whatever. Yet inside the procedure your IF never sees the error. SQLCODE is still zero. What on earth is going on?

Local RPG variable vs internal SQL variable – they are not the same thing

This is the heart of the matter.

When you write dcl-s SQLCODE int(10); inside the subprocedure you have created a brand new local RPG variable. It lives on the stack for that procedure activation and has absolutely nothing to do with the SQLCA that DB2 is updating.

The embedded EXEC SQL statement still causes the SQL runtime to update the real SQLCODE field inside the SQLCA (the one the precompiler generated at module level). Your local variable is never touched by SQL. It just sits there with whatever value it had when the procedure started (usually zero).

So when you write if SQLCODE < 0; inside that subprocedure you are checking your local copy, not the one SQL just populated. Hence “SQLCODE is not SQLCODE”.

It is exactly the same problem you get with any other global variable you accidentally shadow with a local declaration of the same name. The precompiler and the SQL engine are still doing their job; your name lookup is just pointing at the wrong Dave.

How to do it properly

Rule of thumb: Do not declare SQLCODE, SQLCOD, SQLSTATE or SQLSTT yourself in an embedded SQL program unless you have a very specific reason and you are using SQLCA = *NO.

Inside subprocedures the cleanest approaches are:

Just use the global one (the normal case). Because the SQLCA is module-level, the real SQLCODE is visible inside the subprocedure as long as you have not shadowed it with a local declaration of the same name.

dcl-proc GetCustomerName export;
  dcl-pi *n;
    inCustId   zoned(6) const;
    outName    char(30);
    outFound   ind;
  end-pi;

  exec sql 
    select name into :outName 
    from customer 
    where custid = :inCustId;

  if SQLCODE = 0;
    outFound = *on;
  elseif SQLCODE = 100;
    outFound = *off;
  else;
    outFound = *off;
    logSQLError(SQLCODE);   // pass the real value out if you need it
  endif;

  return;
end-proc;

Capture it immediately if you want a local copy for some reason:

dcl-s mySQLCode int(10);

exec sql ... ;

mySQLCode = SQLCODE;   // grab the real value before anything else can change it

Use GET DIAGNOSTICS for richer information (recommended for anything beyond simple success/failure checks). It works beautifully inside subprocedures and does not rely on the old SQLCA fields at all.

dcl-s rows        int(10);
dcl-s sqlState    char(5);
dcl-s msgText     varchar(256);

exec sql 
  select ... ;

exec sql GET DIAGNOSTICS 
  :rows     = ROW_COUNT,
  :sqlState = RETURNED_SQLSTATE,
  :msgText  = MESSAGE_TEXT;

Quick checklist to avoid the pain

• Never start a variable name with SQL unless you really mean the SQLCA fields.
• If you feel the urge to declare SQLCODE inside a subprocedure, stop and ask why. Nine times out of ten you do not need to.
• Check SQLCODE (or better, use GET DIAGNOSTICS) immediately after the statement that interests you. The value only reflects the most recent SQL operation.
• Remember that subprocedures can see module-level variables. Shadowing is the only thing that breaks the visibility of the real SQLCODE.

There you go. Another one of those “the compiler let me do it so it must be fine” moments that turns into a happy hour debugging session.

Next time your subprocedure SQL looks like it succeeded when it clearly did not, have a quick look for a local DCL-S SQLCODE lurking in the shadows. Remove it, recompile, and suddenly the real SQLCODE will start telling you the truth again.

Happy coding, and may your SQLCODEs always be the ones you expect.