Guru: Claude’s SQL Tip - IT Jungle

August 10, 2026

Today’s tip comes to us from Claude, as in the AI chatbot created by Anthropic. I don’t need to introduce him. Many readers of this august publication are far better acquainted with him than I am. I have heard reports of IBM i programmers who have quit writing source code, telling Claude to write it for them. Some folks seem to be right proud of that fact. Furthermore, I’m told that some places no longer allow humans to write source code.

I had a few ideas in mind for this article and was weighing the merits of each one when it occurred to me that I should consult Claude. If he can sling code, evidently better than I can, surely he could suggest a good topic for me to write about. He did better than that. He wrote the tip.

I began by telling Claude, “I need an IBM i-related technical tip. Good topics are RPG and SQL.” He gave me four categories to select from and the option to request more categories. I selected Embedded SQL and RPG. That’s always a good one. Here is what he wrote:

Embedded SQL in RPG – Using Cursors Efficiently

One of the most common mistakes RPG developers make with embedded SQL is using a cursor when they don’t need one, or not using one when they do. Here’s the core pattern to understand:

When to use SELECT INTO vs. a Cursor

Single-row fetch – use SELECT INTO:

exec sql
  SELECT cusname, balance
    INTO :cusName, :balance
    FROM customers
    WHERE cusno = :custNumber;

Clean, simple, no cursor needed. If the query returns more than one row, SQL will throw +01403 or -00811 – so only use this when you’re fetching exactly one row.

Multi-row results – use a Cursor:

exec sql
  DECLARE c1 CURSOR FOR
    SELECT cusno, cusname, balance
      FROM customers
      WHERE status = :activeStatus
      ORDER BY cusname;

exec sql OPEN c1;

dow sqlcode = 0;
  exec sql
    FETCH NEXT FROM c1
      INTO :cusNo, :cusName, :balance;

  if sqlcode = 0;
    // process row
  endif;
enddo;

exec sql CLOSE c1;

Pro Tip – Check SQLSTATE, not just SQLCODE

After every SQL statement, check SQLSTATE for portable, readable status handling:

if sqlstate = '02000';   // no data found — normal end of fetch
  leave;
endif;

if sqlstate >= '02000' and sqlstate < '03000';  // warning range
  // handle warning
endif;

SQLCODE = 100 and SQLSTATE = ‘02000’ both mean no more rows, but SQLSTATE is the SQL standard and more self-documenting.

Bonus — Use SET OPTION at the top of your program

exec sql
  SET OPTION
    COMMIT    = *NONE,
    CLOSQLCSR = *ENDMOD,
    DATFMT    = *ISO;

This controls commitment control, cursor lifespan, and date format at the source level – much cleaner than fiddling with job settings and avoids hard-to-trace runtime surprises.

Bottom line: Use SELECT INTO for single-row lookups, cursors for sets, always check SQLSTATE after fetches, and use SET OPTION to make your SQL behavior explicit and portable. Want to go deeper on any of these – like scrollable cursors or blocked fetching for performance?

That concludes Claude’s technical tip. I didn’t answer his concluding question.

I have to say that I’m somewhat impressed. This is a decent tip. I’ve worked on many programs where the programmer opened a cursor, executed one FETCH, and closed the cursor. In such situations I much prefer SELECT INTO. I infer that Claude does, too. And it was good of him to mention SET OPTION.

I will end with a couple of observations. First, Claude advised the use of SQLSTATE rather than SQLCODE. I whole-heatedly concur. I never use SQLCODE. However, his example used SQLCODE.

Second, I would have added that the SQL SET is an alternative to SELECT INTO.

exec sql set (:cusName, :balance) =
 (SELECT cusname, balance          
    FROM customers                 
    WHERE cusno = :custNumber);

Does this mean that Claude is going to write my articles from now on? It would surely ease my life. Claude seemingly does in fractions of a second what sometimes takes me days to accomplish.

Nah.

Ted Holt is the original, the one and only, chief of the Four Hundred Gurus. We are glad he is back with us writing technical material that helps IBM i programmers. He is a self-employed, independent programmer living near Tupelo, Mississippi, who is old enough to retire but is not ready to do so. He still enjoys programming and is available to help others as needed. He welcomes your comments, questions, and suggestions.

推荐订阅源

IT Jungle

RELATED STORIES