Brewing Your First File Read

 The RPG Blend – Real Code, Real Coffee, No Nonsense

Today’s roast: Green Mountain Black Granite – Expresso style. Bold. Dark. Straight to the point—just like today’s lesson.

If you’ve been following The RPG Blend, you’ve already brewed your first “Hello World” in RPG and set up your dev environment on PUB400. Now, it’s time to go beyond the basics and start doing something practical—reading data from a SQL table using free-form RPGLE.

This article is designed for beginners who want to learn how to read and display data using modern RPG and embedded SQL, without dealing with legacy DDS files.

All code samples are available in my GitHub repo:
👉 The RPG Blend GitHub Repository

Step 1: Create a SQL Table

We’ll create a simple CUSTOMERS table. This is your test dataset, and it gives us something to read from in the RPG program.

Option A: Use STRSQL

  1. Log into PUB400 via 5250.
  2. Enter STRSQL.
  3. Paste and run the following:

CREATE TABLE GEORGEDEV/CUSTOMERS (

  ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),

  NAME VARCHAR(40),

  EMAIL VARCHAR(40)

);

What This Does:

  • ID: Automatically generated unique id.
  • NAME: Customer name, up to 40 characters.
  • EMAIL: Customer email, up to 40 characters.

Why 40 characters?
When using the DSPLY opcode in RPG, the output is limited to 52 characters per display line. By keeping each field at or under 40 characters, we ensure that the combined output (including labels like "Name: " or "Email: ") fits comfortably within that display constraint. This avoids truncation or formatting issues on the green screen.

Option B: Use RUNSQLSTM (From a Source Member)

  1. Use WRKMBRPDM to create a member in QSQLSRC, e.g. CREATECUST.
  2. Paste the same SQL.
  3. Run with:

RUNSQLSTM SRCFILE(GEORGEDEV/QSQLSRC) SRCMBR(CREATECUST)

Step 2: Insert Sample Data

Next, we’ll populate the CUSTOMERS table with a few rows:

INSERT INTO GEORGEDEV/CUSTOMERS (NAME, EMAIL)

VALUES ('Alice Smith', 'alice@example.com'),

       ('Bob Johnson', 'bob@example.com'),

       ('Carla Lee', 'carla@example.com');

You can run this either in STRSQL or from another source member using RUNSQLSTM.

Step 3: Write the RPG Program

Now we’ll write an RPGLE program that uses embedded SQL to read and display customer data.

Create a new source member:

  • File: GEORGEDEV/QRPGLESRC
  • Member: READCUST
  • Member type: sqlrpgle

Why SQLRPGLE?
This tells the compiler that the member contains embedded SQL statements. Unlike regular RPGLE, SQLRPGLE members are compiled using the CRTSQLRPGI command, which allows the embedded SQL statements to be preprocessed and translated into native RPG code. Without this, your SQL won't be recognized.

Code:

**free

ctl-opt dftactgrp(*no) actgrp(*caller);

 

// Define a data structure that matches the table columns

dcl-ds customer extname('CUSTOMERS') end-ds;

dcl-s response char(1) inz;

 

// Declare a SQL cursor to fetch all rows from the table

exec sql

  declare c1 cursor for

    select id, name, email from georgeDev.customers;

 

// Open the cursor

exec sql

  open c1;

 

// Loop until there are no more rows to fetch

dow sqlcode = 0;

  exec sql

    fetch c1 into :customer;

 

  if sqlcode = 0;

    // Display each field using DSPly

    dsply ('ID: ' + %char(customer.id));

    dsply ('Name: ' + customer.name);

    dsply ('Email: ' + customer.email);

    dsply ('---') ‘ ‘ response;

  endif;

enddo;

 

// Close the cursor

exec sql

  close c1;

 

*inlr = *on;

Explanation:

  • ctl-opt dftactgrp(*no) actgrp(*caller): Required for embedded SQL.
  • dcl-ds customer extname('CUSTOMERS'): RPG automatically maps fields to SQL columns by name.
  • declare/open/fetch/close: This is the core flow of using SQL cursors.
  • %char(): Converts numeric values to character for display.
  • dsply: Displays text to the green screen.
  • sqlcode = 0: Means the last SQL operation was successful.
  • *inlr = *on: Ends the program properly.

Step 4: Compile and Run

Compile with:

CRTSQLRPGI OBJ(GEORGEDEV/READCUST) SRCFILE(GEORGEDEV/QRPGLESRC)

Run it:

CALL GEORGEDEV/READCUST

You should see:

ID: 1

Name: Alice Smith

Email: alice@example.com

---

ID: 2

Name: Bob Johnson

Email: bob@example.com

---

ID: 3

Name: Carla Lee

Email: carla@example.com

---

Final Sips

In this edition of The RPG Blend, you:

·         Created a real SQL table using DDL

·         Inserted sample data into it

·         Wrote an RPGLE program that uses embedded SQL to read and display data

·         Compiled and executed your code using modern, free-form RPG

No DDS. No SEU/PDM. Just clean, modern RPG powered by SQL.

This is the kind of hands-on work that takes you from beginner to capable developer—one sip at a time.

Now it’s your turn to explore further. Add more records. Filter the data. Modify the structure. The more you experiment, the deeper your mastery grows.

Until next time:

·         Keep exploring embedded SQL in RPG

·         Keep practicing in PUB400

·         And always keep your coffee close

Comments

Post a Comment

Popular Posts