Developer ToolsFree generatorPDF, Excel, Word exportIBM iAll IBM i (AS/400) ERPs tools

RPGLE Fully Free Program Generator with Embedded SQL and MONITOR

This generator writes a complete fully free ILE RPG program for IBM i 7.3, 7.4 and 7.5 that reads a table with an embedded SQL cursor, checks SQLCODE after every statement and traps runtime errors with MONITOR. Fill in your table and columns, copy the source to a stream file, and compile it with the CRTSQLRPGI command shown.

Fill in

Program name

Library

Table or physical file

Key column

Key column RPG type

Must match the column: check with DSPFFD or QSYS2.SYSCOLUMNS.

Name column

Name column RPG type

Amount column

Amount column RPG type

WHERE condition

Commitment control

Source stream file directory

Generated code

Download:

CUSTRPT.sqlrpgle

**FREE
ctl-opt dftactgrp(*no) actgrp(*new) option(*srcstmt : *nodebugio);

// One row from MYLIB.CUSTMAST; types must match the columns
dcl-ds row qualified;
  CUSNO packed(7:0);
  CUSNAM char(30);
  CUSBAL packed(11:2);
end-ds;

dcl-s rows int(10) inz(0);
dcl-s total packed(15:2) inz(0);
dcl-s avgAmt packed(15:2) inz(0);
dcl-s msg varchar(52);

// SET OPTION must come before any other SQL statement in the source
exec sql set option commit = *NONE, naming = *sql,
                    closqlcsr = *endmod, datfmt = *iso;

exec sql declare c1 cursor for
  select CUSNO, CUSNAM, CUSBAL
    from MYLIB.CUSTMAST
   where CUSBAL > 0
   order by CUSNO
   for read only;

exec sql open c1;
if sqlcode < 0;
  dsply ('Open failed SQLCODE ' + %char(sqlcode));
  *inlr = *on;
  return;
endif;

dow 1 = 1;
  exec sql fetch next from c1 into :row;
  if sqlcode = 100;          // no more rows (SQLSTATE 02000)
    leave;
  elseif sqlcode < 0;
    dsply ('Fetch failed SQLCODE ' + %char(sqlcode));
    leave;
  endif;

  monitor;
    rows += 1;
    total += row.CUSBAL;
    avgAmt = total / rows;
    msg = %char(row.CUSNO) + ' ' + %trim(row.CUSNAM)
        + ' ' + %char(row.CUSBAL);
    dsply msg;
  on-error 00102 : 00103;    // divide by zero, result too large
    dsply ('Calculation error on ' + %char(row.CUSNO));
  on-error *program;
    dsply ('Unexpected error ' + %char(%status));
    leave;
  endmon;
enddo;

exec sql close c1;

dsply ('Rows ' + %char(rows) + ' total ' + %char(total) + ' avg ' + %char(avgAmt));

*inlr = *on;
return;

Compile and run (CL)

CRTSQLRPGI OBJ(MYLIB/CUSTRPT) SRCSTMF('/home/dev/CUSTRPT.sqlrpgle') OBJTYPE(*PGM) COMMIT(*NONE) DBGVIEW(*SOURCE)

CALL PGM(MYLIB/CUSTRPT)

Optional: write rows to a table instead of DSPLY

// Replace the dsply msg; line inside the loop with:
exec sql insert into MYLIB.CUSTRPTOUT
  (CUSNO, CUSNAM, CUSBAL)
  values (:row.CUSNO, :row.CUSNAM, :row.CUSBAL);
if sqlcode < 0;
  dsply ('Insert failed SQLCODE ' + %char(sqlcode));
endif;

// Create the output table once, for example in Run SQL Scripts:
// CREATE TABLE MYLIB.CUSTRPTOUT AS
//   (SELECT CUSNO, CUSNAM, CUSBAL FROM MYLIB.CUSTMAST)
//   WITH NO DATA;

Fully free source (**FREE in column 1) needs IBM i 7.1 TR11 or 7.2 TR3 or later with the compiler PTFs; it is standard on 7.3, 7.4 and 7.5. Check each RPG type against the column definition (DSPFFD or QSYS2.SYSCOLUMNS); a mismatch gives SQLCODE errors on FETCH or decimal data errors. DSPLY shows at most 52 characters. With COMMIT(*CHG) the table must be journaled and inserts need a COMMIT. The output table name in the optional block must be 10 characters or fewer for system naming.

Get your RPG program reviewed

Send a generated or existing SQLRPGLE program and we will check types, SQL error handling and compile options for your release.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Enter the library, table and three columns: a key, a name and an amount. For each column give the RPG type that matches its definition, for example packed(7:0) for a DECIMAL(7,0) column or char(30) for CHAR(30); DSPFFD or QSYS2.SYSCOLUMNS shows the definitions. Choose *NONE for commitment control unless the table is journaled and you plan to write. Save the first block as a stream file, for example /home/dev/CUSTRPT.sqlrpgle, from VS Code with Code for IBM i, RDi, or EDTF, then run the CRTSQLRPGI command and CALL the program. Each row appears as a DSPLY message, followed by a count, total and average.

Source member alternative: SRCFILE(MYLIB/QRPGLESRC) SRCMBR(CUSTRPT) with type SQLRPGLE
Run from an interactive session to see DSPLY output
Use the optional block to write rows to a table for batch use

What each part does

ctl-opt dftactgrp(*no) is required to use procedures and named activation groups; actgrp(*new) gives each call a clean activation group. option(*srcstmt) makes job log statement numbers match your source lines. The qualified data structure row is the host structure for FETCH, so one FETCH fills all three subfields. SET OPTION fixes commitment control, SQL naming (library.table), cursor closing and date format at compile time, and it must be the first SQL statement in the source. SQLCODE 100 means no more rows, negative values are errors; SQLSTATE '02000' is the equivalent end-of-data test if you prefer SQLSTATE. MONITOR traps runtime errors in the loop body, with specific status codes listed before the catch-all on-error *program.

Worked example

With the defaults the program reads MYLIB.CUSTMAST where CUSBAL > 0 in CUSNO order. For a customer 1001 named ACME TOOLING with a balance of 2,450.00 the DSPLY line reads '1001 ACME TOOLING 2450.00'. After the last row the program shows 'Rows 214 total 386112.50 avg 1804.26' (your numbers will differ). If the cursor finds no rows the loop exits on the first FETCH with SQLCODE 100 and the summary shows zero rows. If an amount column was defined as zoned in the file but packed here, you get a FETCH error or decimal data error, which is why the types are separate fields.

Version notes and gotchas

**FREE must start in column 1 of line 1; nothing else can be on that line. Fully free source arrived with 7.1 TR11 and 7.2 TR3 and is standard from 7.3. SRCSTMF on CRTSQLRPGI lets you keep source in the IFS and in Git; a source member works the same. DSPLY truncates at 52 characters and is not meant for batch jobs, where it waits for a reply; use the table output block or a printer file for batch. With naming = *sql, unqualified table names resolve to a schema named after the user, so always qualify, or switch to naming = *sys and the library list.

Frequently Asked Questions

What does a simple RPG program look like?

A modern RPG program starts with **FREE, a ctl-opt line, declarations with dcl-s and dcl-ds, then calculations in free format ending with *inlr = *on and return. The generated program is a realistic example: it declares a data structure, opens an SQL cursor, loops through the rows, handles errors and prints a summary.

Is RPG IV still used on IBM i?

Yes. ILE RPG (RPG IV) is the main business language on IBM i and still gets enhancements through Technology Refreshes, including fully free syntax, embedded SQL improvements and new built-in functions. Many ERPs that run on IBM i, such as Infor LX and XA, JD Edwards World and in-house systems, are written in RPG.

How do I handle errors in an SQLRPGLE program?

Check SQLCODE or SQLSTATE after every EXEC SQL statement: 0 is success, 100 (SQLSTATE 02000) is no data, negative is an error, positive other than 100 is a warning. For RPG runtime errors, wrap code in MONITOR with ON-ERROR status codes. SQL errors do not trigger MONITOR, so both checks are needed.

How should a beginner start with RPG programming?

Start with fully free RPG and embedded SQL rather than fixed-format cycle code: it reads like other languages and is what new code uses. Use VS Code with Code for IBM i or RDi, compile programs like this one, step through them with the debugger, and read the ILE RPG Reference for each opcode you use.

If your RPG team wants an assistant that explains existing programs, drafts SQL and answers questions about your IBM i data in plain English, we can run it privately on your own servers.

Talk to an Expert Explore More Free Tools