You already know how to read files with READ and CHAIN. They work. But the moment your query involves a join, a filter across three fields, an aggregate, or a sorted result set — native RPG I/O starts fighting you. You end up writing loops inside loops, building intermediate arrays, and praying the logic stays correct six months from now.
Embedded SQL in RPGLE fixes this. You write a SQL statement directly inside your RPG program. The compiler handles the translation. The result is cleaner code, less of it, and queries that any SQL-literate developer can read at a glance.
This post covers how embedded SQL works, how to set it up, and the patterns you will actually use day to day.
How embedded SQL works
Embedded SQL is not a library call or an API. It is a preprocessor feature. When you compile an RPGLE program that contains SQL statements, the compiler first runs it through a precompiler called CRTSQLRPGI. That precompiler translates each SQL statement into the native I/O calls the system understands, then compiles the result as a normal RPGLE program.
From your perspective, you write SQL directly in your source. From the system’s perspective, it is still RPG.
The bridge between SQL and your RPG variables is the host variable. Any RPG variable prefixed with a colon inside a SQL statement is treated as a host variable — SQL reads from it or writes to it directly.
Setting up: CRTSQLRPGI instead of CRTBNDRPG
One change at compile time. Instead of:
CRTBNDRPG PGM(MYLIB/MYPGM) SRCFILE(MYLIB/QRPGLESRC)
You use:
CRTSQLRPGI OBJ(MYLIB/MYPGM) SRCFILE(MYLIB/QRPGLESRC) COMMIT(*NONE)
COMMIT(*NONE) disables commitment control, which is what you want unless you are explicitly managing transactions. You can also set this at the top of your source:
**FREE ctl-opt option(*nodebugio *srcstmt) commit(*none) dftactgrp(*no) actgrp(*caller);
SQLSTATE and SQLCODE
Every SQL statement sets two special variables automatically:
- SQLSTATE — a 5-character code.
'00000'means success.'02000'means no rows found. - SQLCODE — numeric.
0is success,+100is no rows found, negative values are errors.
Check these after every SQL statement in production code.
exec sql
select FirstName, LastName, Salary
into :FirstName, :LastName, :Salary
from EMPLOYEEPF
where EmployeeID = :SearchID;
if SQLCODE = +100;
// No record found
elseif SQLCODE < 0;
// SQL error
endif;SELECT INTO — fetching a single row
When you expect exactly one row, SELECT INTO reads the result directly into your RPG variables.
**FREE
ctl-opt dftactgrp(*no) actgrp(*caller);
dcl-s EmpID packed(6:0);
dcl-s FirstName varchar(30);
dcl-s LastName varchar(30);
dcl-s Department varchar(20);
dcl-s Salary packed(9:2);
EmpID = 100234;
exec sql
select FirstName, LastName, Department, Salary
into :FirstName, :LastName, :Department, :Salary
from EMPLOYEEPF
where EmployeeID = :EmpID;
if SQLCODE = 0;
dsply ('Found: ' + %trimr(FirstName) + ' ' + %trimr(LastName));
elseif SQLCODE = +100;
dsply 'Employee not found';
endif;
*inlr = *on;
return;If the query returns more than one row, SQL raises an error. Use cursors when you expect multiple rows.
Cursors — processing multiple rows
A cursor is a pointer into a result set. You declare it, open it, fetch rows one at a time in a loop, then close it.
**FREE
ctl-opt dftactgrp(*no) actgrp(*caller);
dcl-s EmpID packed(6:0);
dcl-s FirstName varchar(30);
dcl-s LastName varchar(30);
dcl-s Salary packed(9:2);
dcl-s Counter int(10) inz(0);
exec sql
declare EmpCursor cursor for
select EmployeeID, FirstName, LastName, Salary
from EMPLOYEEPF
where Salary > 50000
order by LastName, FirstName;
exec sql open EmpCursor;
exec sql fetch EmpCursor into :EmpID, :FirstName, :LastName, :Salary;
dow SQLCODE = 0;
Counter += 1;
dsply (%trimr(LastName) + ', ' + %trimr(FirstName));
exec sql fetch EmpCursor into :EmpID, :FirstName, :LastName, :Salary;
enddo;
exec sql close EmpCursor;
dsply ('Total records: ' + %char(Counter));
*inlr = *on;
return;INSERT, UPDATE, DELETE
DML statements work the same way — host variables wherever you would otherwise hardcode a value.
INSERT:
exec sql
insert into ORDERLOG (OrderID, CustomerID, OrderDate, Total)
values (:NewOrderID, :CustID, current_date, :OrderTotal);UPDATE:
exec sql
update EMPLOYEEPF
set Salary = Salary * 1.05,
LastReviewDate = current_date
where Department = :DeptCode
and HireDate < :CutoffDate;DELETE:
exec sql
delete from TEMPORDERS
where OrderDate < current_date - 30 days;Joins — where embedded SQL really earns its keep
A join that would take 30 lines of RPG with CHAIN and conditional logic becomes five lines of SQL.
exec sql
declare InvoiceCursor cursor for
select i.InvoiceID, i.InvoiceDate, i.Total,
c.CompanyName, c.ContactEmail
from INVOICEPF i
join CUSTMAST c on c.CustomerID = i.CustomerID
where i.Status = 'OPEN'
and i.DueDate < current_date
order by i.DueDate;Same cursor pattern: declare, open, fetch loop, close.
NULL handling with indicators
SQL columns can be NULL. If you fetch a NULL into an RPG variable without an indicator, you get an error. Indicator variables are small integers that SQL sets to -1 when the column is NULL.
dcl-s MiddleName varchar(20);
dcl-s MiddleInd int(5);
exec sql
select MiddleName
into :MiddleName :MiddleInd
from EMPLOYEEPF
where EmployeeID = :EmpID;
if MiddleInd = -1;
MiddleName = '';
endif;When to use embedded SQL vs native I/O
Native RPG I/O (READ, CHAIN, WRITE) is still right for sequential file processing, simple key lookups, and high-volume loops where you read every record in order.
Embedded SQL wins when:
- You need a join across two or more files
- You need aggregation (
SUM,COUNT,AVG) - You need a filtered, sorted result set
- The query logic would be complex to express in native RPG loops
- Another developer needs to maintain the code and knows SQL
Most real RPGLE programs use both — native I/O where it is fast and direct, embedded SQL where the query logic gets complicated.
A complete working example
**FREE
ctl-opt dftactgrp(*no) actgrp(*caller) option(*nodebugio *srcstmt);
dcl-s OrderID packed(9:0);
dcl-s CustName varchar(50);
dcl-s OrderDate date;
dcl-s OrderTotal packed(11:2);
dcl-s MinAmount packed(11:2);
MinAmount = 1000;
exec sql
declare OrderCursor cursor for
select o.OrderID, c.CompanyName, o.OrderDate, o.Total
from ORDERPF o
join CUSTMAST c on c.CustomerID = o.CustomerID
where o.Status = 'OPEN'
and o.Total >= :MinAmount
order by o.Total desc;
exec sql open OrderCursor;
exec sql fetch OrderCursor
into :OrderID, :CustName, :OrderDate, :OrderTotal;
dow SQLCODE = 0;
dsply (%char(OrderID) + ' | ' + %trimr(CustName));
exec sql fetch OrderCursor
into :OrderID, :CustName, :OrderDate, :OrderTotal;
enddo;
exec sql close OrderCursor;
*inlr = *on;
return;Next post: CL commands for RPG programmers — the system layer you cannot ignore.