# RPGLE Fully Free Program Generator with Embedded SQL and MONITOR

> Source: https://netray.co/tools/ibm-i-rpgle-free-format-program-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Fill in your table and columns and get a complete fully free ILE RPG program that reads rows with an SQL cursor, handles SQL and runtime errors, and compiles with one CRTSQLRPGI command.

## Category

developer

## Erp

IBM i

## Audience

- RPG developer
- IBM i developer
- Developer learning RPG

## Tool

**Kind:** template

**Output:** code

**Language:** text

**Fields:**

#### Program name

**Key:** program_name

**Type:** text

**Default:** CUSTRPT

#### Library

**Key:** library

**Type:** text

**Default:** MYLIB

#### Table or physical file

**Key:** table_name

**Type:** text

**Default:** CUSTMAST

#### Key column

**Key:** key_field

**Type:** text

**Default:** CUSNO

#### Key column RPG type

**Key:** key_type

**Type:** text

**Default:** packed(7:0)

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

#### Name column

**Key:** name_field

**Type:** text

**Default:** CUSNAM

#### Name column RPG type

**Key:** name_type

**Type:** text

**Default:** char(30)

#### Amount column

**Key:** balance_field

**Type:** text

**Default:** CUSBAL

#### Amount column RPG type

**Key:** balance_type

**Type:** text

**Default:** packed(11:2)

#### WHERE condition

**Key:** where_clause

**Type:** text

**Default:** CUSBAL > 0

#### Commitment control

**Key:** commit_level

**Type:** select

**Options:**

- *NONE
- *CHG

**Default:** *NONE

#### Source stream file directory

**Key:** src_path

**Type:** text

**Default:** /home/dev

**Blocks:**

#### {{program_name}}.sqlrpgle

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

// One row from {{library}}.{{table_name}}; types must match the columns
dcl-ds row qualified;
  {{key_field}} {{key_type}};
  {{name_field}} {{name_type}};
  {{balance_field}} {{balance_type}};
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 = {{commit_level}}, naming = *sql,
                    closqlcsr = *endmod, datfmt = *iso;

exec sql declare c1 cursor for
  select {{key_field}}, {{name_field}}, {{balance_field}}
    from {{library}}.{{table_name}}
   where {{where_clause}}
   order by {{key_field}}
   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.{{balance_field}};
    avgAmt = total / rows;
    msg = %char(row.{{key_field}}) + ' ' + %trim(row.{{name_field}})
        + ' ' + %char(row.{{balance_field}});
    dsply msg;
  on-error 00102 : 00103;    // divide by zero, result too large
    dsply ('Calculation error on ' + %char(row.{{key_field}}));
  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)

**Body:** CRTSQLRPGI OBJ({{library}}/{{program_name}}) SRCSTMF('{{src_path}}/{{program_name}}.sqlrpgle') OBJTYPE(*PGM) COMMIT({{commit_level}}) DBGVIEW(*SOURCE)

CALL PGM({{library}}/{{program_name}})

#### Optional: write rows to a table instead of DSPLY

**Body:** // Replace the dsply msg; line inside the loop with:
exec sql insert into {{library}}.{{program_name}}OUT
  ({{key_field}}, {{name_field}}, {{balance_field}})
  values (:row.{{key_field}}, :row.{{name_field}}, :row.{{balance_field}});
if sqlcode < 0;
  dsply ('Insert failed SQLCODE ' + %char(sqlcode));
endif;

// Create the output table once, for example in Run SQL Scripts:
// CREATE TABLE {{library}}.{{program_name}}OUT AS
//   (SELECT {{key_field}}, {{name_field}}, {{balance_field}} FROM {{library}}.{{table_name}})
//   WITH NO DATA;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use it

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** **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.

**Cta:** 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.

**Faq:**

#### What does a simple RPG program look like?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

rpg program example, sample rpg program, rpg programming language example, rpg iv on ibm as400, rpgle free format example, sqlrpgle cursor example, error handling in rpg, rpg programming for beginners

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json