2 min read· Admin

Embedded SQL in ILE RPG: Host Variables, SQLSTATE, and Nulls

Record-level access navigates to data one record at a time. Embedded SQL states which data is needed and lets Db2 for i find it. In modern IBM i development, both appear in the same programs, and the beginner-level SQL toolkit is small.

SQLRPGLE and the precompiler

A source member of type SQLRPGLE is processed by the SQL precompiler before the RPG compiler sees it. Each EXEC SQL statement is replaced with generated RPG code. When the RPG compiler reports an error in code you did not write, the cause is almost always a host variable whose type does not suit the column it is used with.

Host variables

An RPG variable used in an SQL statement is prefixed with a colon. A host data structure, built with EXTNAME or LIKEDS, can receive a whole row at once — list the columns explicitly so their order matches the subfields.

exec sql
  select ITEMDESC, ITEMPRICE
    into :itemDesc, :itemPrice
    from ITEMMAST
   where ITEMNO = :itemNo;

Check SQLSTATE after every statement

SQL does not raise RPG exceptions. '00000' means success, '02000' means no row was found or changed, and anything else is a warning or an error. A MONITOR group around EXEC SQL does not catch a failed statement.

Nulls

A nullable column, or an aggregate over no rows, can return NULL. An indicator variable after the host variable — :lastSale :lastSaleNull — receives -1 when that happens. MAX over an empty set returns a NULL with SQLSTATE '00000', so the indicator is the only signal.

Settings and row counts

SET OPTION, the first SQL statement in the member, fixes compile-time settings such as commitment control and date format. GET DIAGNOSTICS returns the number of rows a statement changed.

Lesson 28's exercise maps real Db2 for i catalog columns to RPG host variables on PUB400, then compiles and runs embedded SQL against tables built in earlier lessons. Multi-row processing with cursors follows in the next phase of the course.

Get the lesson

Lesson 28, “Embedded SQL Foundations: Static SQL and Host Variables,” is part of Beginner ILE RPG in IBM i ILE RPG Mastery and is available at ILERPG.com for USD 10. The complete Beginner ILE RPG series — all 18 lessons — is USD 180, or USD 135 with coupon code RPG30, which takes 25% off when you purchase the full series.