Menu

Programming & Db2

Your SQLRPGLE Cursor Has More Rows. Why Did the Loop Stop?

An optional lookup can overwrite SQLCOD and stop a cursor loop early. Follow a five-row SQLRPGLE example, identify which statement returned +100, and give FETCH its own status handling.

IBM iSQLRPGLERPGDb2 for iSQLCODEDebugging

The query returns five orders. The RPG loop processes two, closes the cursor, and returns. Before changing the query, check which SQL statement last updated the status your loop is testing.

An embedded SQL loop can stop early even when the cursor has more rows available. One way this happens is easy to overlook: a statement inside the loop returns +100, and the loop treats that result as the end of the cursor.

The useful debugging question is: which statement returned that status?

This walkthrough uses a deliberately small, synthetic example. The baseline was compiled and run on IBM i, confirming the behaviour described here: the broken program processes two orders, while the corrected program processes all five. The example demonstrates the bug in isolation; it is not a captured production incident.

SQLCOD is not an end-of-cursor flag

Embedded SQL reports statement results through the SQL communication area, or SQLCA. In ILE RPG, SQLCOD is the short name for SQLCODE; SQLSTT is the short name for SQLSTATE. The status describes the SQL statement that produced it. It is not stored separately for each cursor. IBM SQLCODE and SQLSTATE concepts · SQLCA field descriptions

That makes the following distinction essential:

Statement What +100 means in this example
A single-row FETCH NEXT No next row was returned; the cursor reached the end of its result.
A singleton SELECT ... INTO No row matched the lookup.

Both can return SQLSTATE '02000'. A missing lookup row says nothing about whether the cursor has another order. IBM documents both cases under SQL0100. IBM SQL messages

Five orders, one missing optional lookup

Our fixture has two tables: EOICURSOR.ORDERS and EOICURSOR.PROMOS.

Order Promotion code Lookup result
1 SAVE10 Found: discount 10.00
2 NONE No matching promotion
3 SAVE10 Found: discount 10.00
4 SAVE10 Found: discount 10.00
5 SAVE10 Found: discount 10.00

For this example, a missing promotion is allowed and means zero discount. That is an explicit business rule for the fixture. In another application, missing reference data might require rejecting the order or stopping the batch.

The cursor reads orders by ORDER_ID. The program then looks up the promotion for each fetched order. The promotion key is unique, and the selected columns are non-nullable, keeping the example focused on statement status.

The loop that stops too soon

Here is the relevant part of the broken program. Declarations, the checked OPEN, and cleanup are included in the downloadable source.

// Deliberately incorrect outer loop condition.
dou SQLCOD = 100;
  exec sql fetch next from C1 into :orderId, :promoCode;

  if SQLCOD = 100;
    leave;
  elseif SQLCOD <> 0;
    // Report unexpected FETCH status, then stop.
    leave;
  endif;

  discount = 0;
  exec sql select DISCOUNT into :discount
    from EOICURSOR.PROMOS
    where PROMO_CODE = :promoCode;

  if SQLCOD = 100;
    discount = 0;  // Missing optional promotion is allowed.
  elseif SQLCOD <> 0;
    // Report unexpected lookup status, then stop.
    leave;
  endif;

  processed += 1;
  // Display this order. No further SQL before ENDDO.
enddo;

RPG evaluates the DOU condition at the bottom of the loop. After order 2, SQLCOD is 100 because the promotion lookup found no row. Assigning zero to discount does not change that status.

The program counts order 2, reaches ENDDO, and exits. It never asks the cursor for order 3.

Step SQL statement just executed SQLCOD Consequence
Fetch order 1 FETCH NEXT 0 Process the fetched row.
Look up SAVE10 SELECT INTO 0 Bottom-of-loop test allows another iteration.
Fetch order 2 FETCH NEXT 0 The cursor successfully returned a row.
Look up NONE SELECT INTO 100 Lookup has no match.
Evaluate DOU No new SQL statement 100 Exit before fetching order 3.

This depends on the exact statement order. A loop with another FETCH immediately before its continuation test will have a different status at that point. An SQL lookup inside a cursor loop is not enough, by itself, to cause this bug. The failure is checking one statement’s result as though it belonged to another.

Give FETCH control over cursor exhaustion

The corrected program uses an explicit loop and handles the fetch result immediately. It saves the code, state, and warning flag in ordinary RPG variables before running any other SQL.

dou *off;
  exec sql fetch next from C1 into :orderId, :promoCode;
  fetchCode = SQLCOD;
  fetchState = SQLSTT;
  fetchWarn = %subst(SQLWRN:1:1);

  if fetchCode = 100;
    reachedEnd = *on;
    leave;
  elseif fetchCode <> 0 or fetchState <> '00000' or fetchWarn = 'W';
    // Report saved FETCH status; this is not normal completion.
    hadError = *on;
    leave;
  endif;

  discount = 0;
  exec sql select DISCOUNT into :discount
    from EOICURSOR.PROMOS
    where PROMO_CODE = :promoCode;
  lookupCode = SQLCOD;
  lookupState = SQLSTT;
  lookupWarn = %subst(SQLWRN:1:1);

  if lookupCode = 100;
    discount = 0;
  elseif lookupCode <> 0 or lookupState <> '00000' or lookupWarn = 'W';
    // Report saved lookup status and stop processing.
    hadError = *on;
    leave;
  endif;

  processed += 1;
  // Display this order, then fetch the next one.
enddo;

With this structure, the missing promotion affects only order 2’s discount. Iteration continues until a subsequent FETCH reports end-of-data, or an unexpected status causes an explicit stop.

Saving the status also makes its ownership visible during debugging: fetchCode belongs to the fetch, and lookupCode belongs to the lookup. Check the result before calling a helper that may run more SQL against the same SQLCA.

These examples deliberately stop on unexpected warnings as well as errors. IBM notes that SQL warning indicators can be set even with a zero SQLCODE. A production application should define which warnings it accepts rather than treating all non-negative results as interchangeable. SQLCA field descriptions

Preserve the distinction between “finished” and “stopped”

The complete programs check OPEN and CLOSE too. They keep an explicit reachedEnd flag and an error flag, so cursor cleanup cannot turn an earlier failure into apparent success.

A successful CLOSE can replace the SQL status you were investigating. Preserve the relevant statement status before cleanup, and report completion from the program’s own outcome flags.

The demonstration uses DSPLY and a counter as its processing step. It does not update orders. Its normal RPG return is not a production success contract: a real caller or scheduler needs an explicit way to distinguish a completed batch from a failed one.

Also handle the lookup’s output deliberately. Initialize or assign the fallback value on the no-match path, and consume lookup values only after accepting the lookup result. Otherwise, the program can accidentally reuse a previous order’s value. A missing optional promotion is not the same as a valid zero-valued promotion, even if this example chooses the same discount for both.

Reproduce the example

The download contains a setup script and both complete SQLRPGLE programs:

Use an unused development schema name. Run the setup once, then compile the examples with CRTSQLRPGI using the instructions. The programs use COMMIT(*NONE) for this read-only demonstration; the example does not prescribe transaction settings for business updates.

The confirmed baseline result is:

Program Orders displayed and counted Completion message
CURBAD 1 and 2 Stopped before FETCH reported end-of-data
CURGOOD 1 through 5 Complete: FETCH reached end-of-data

The instructions also include additional test cases: moving the missing lookup to order 1, making every lookup match, and selecting an empty result. These are suggested checks for your environment; they are separate from the confirmed five-order baseline above.

Three changes that do not solve the underlying problem

Changing the loop to check SQLSTATE instead. If the test still follows the lookup, it still observes the lookup’s result. The ownership problem remains.

Resetting SQLCOD to zero after the lookup. That overwrites diagnostic information to satisfy a loop condition. Prefer a loop whose continuation does not depend on an unrelated statement’s status.

Moving FETCH and ignoring all other statuses. That may change this particular symptom, but errors or warnings from the lookup still need their own handling.

For a simple optional lookup like this one, a LEFT JOIN may also remove the second SQL statement. Evaluate that separately: uniqueness, missing-row handling, and the intended processing behaviour must stay correct.

This article covers one row per forward fetch. Multiple-row fetches need row-count and partial-result handling; do not copy the early +100 exit into a rowset loop without accounting for returned rows. IBM SQL programming guide

When a cursor loop stops unexpectedly, put a breakpoint immediately after each SQL statement and identify which result the continuation test sees. In this example, order 3 is still waiting. The loop exits on the result of the promotion lookup for order 2.

References

Documentation and supporting sources for this article.

Comments

Share your thoughts, questions, or real-world IBM i experiences related to this article.