Here’s a complete, simple example of an SQLRPGLE (RPGLE with embedded SQL) program. This program:

• Connects to a database

• Selects customers from a table (CUSTOMERS)

• Filters by city

• Displays customer names using DSPLY

🛠️ 1. Table Assumed

Let’s assume you have a table named CUSTOMERS with the following structure:

📄 2. SQLRPGLE Program Example

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

Dcl-S CustID   Int(10);
Dcl-S Name     Varchar(50);
Dcl-S City     Varchar(50);

Exec SQL
    Set Option Commit = *None;

Exec SQL
    Declare C1 Cursor For
        Select CustID, Name, City
        From Customers
        Where City = 'Paris'
        Order By Name;

Exec SQL
    Open C1;

Dow 1 = 1;
   Exec SQL
       Fetch Next From C1 Into :CustID, :Name, :City;

   If SQLCODE = 100;
      Leave;  // No more data
   ElseIf SQLCODE < 0;
      Dsply ('SQL Error: ' + %Char(SQLCODE));
      Leave;
   EndIf;

   Dsply ('Customer: ' + %Trim(Name) + ' - ' + %Trim(City));
EndDo;

Exec SQL
    Close C1;

*INLR = *ON;
Return;

🔍 Key Concepts:

• Embedded SQL: SQL statements like SELECT, FETCH, OPEN, and CLOSE are directly in RPG code.

• Cursor: Used to fetch multiple rows.

• Host variables (:CustID, :Name, etc.): RPG variables used in SQL statements.

• SQLCODE: Used to check the result of each SQL operation.

Let’s extend the SQLRPGLE example to include:

1. INSERT: Add a new customer

2. UPDATE: Modify an existing customer's city

3. DELETE: Remove a customer

4. Enhanced Error Handling with SQLSTATE and SQLCODE

5. Optionally: Fetching rows into an array (if you're on IBM i 7.3 TR7 or 7.4 TR1+)

🧾 Full SQLRPGLE Program: CUSTOMERMGMT

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

Dcl-S CustID   Int(10);
Dcl-S Name     Varchar(50);
Dcl-S City     Varchar(50);
Dcl-S SQLState Char(5);
Dcl-S Msg      Varchar(100);

// Disable commitment control for simplicity
Exec SQL
    Set Option Commit = *None;

// 🟢 INSERT a new customer
CustID = 1001;
Name   = 'Jean Dupont';
City   = 'Paris';

Exec SQL
    Insert Into Customers (CustID, Name, City)
    Values (:CustID, :Name, :City);

If SQLCODE <> 0;
   Exec SQL Get Diagnostics Condition 1 :Msg = MESSAGE_TEXT;
   Dsply ('Insert failed: ' + Msg);
Else;
   Dsply ('Customer inserted: ' + %Trim(Name));
EndIf;

// 🟡 UPDATE city for this customer
City = 'Lyon';

Exec SQL
    Update Customers
    Set City = :City
    Where CustID = :CustID;

If SQLCODE <> 0;
   Exec SQL Get Diagnostics Condition 1 :Msg = MESSAGE_TEXT;
   Dsply ('Update failed: ' + Msg);
Else;
   Dsply ('City updated to ' + City);
EndIf;

// 🔴 DELETE customer
Exec SQL
    Delete From Customers
    Where CustID = :CustID;

If SQLCODE <> 0;
   Exec SQL Get Diagnostics Condition 1 :Msg = MESSAGE_TEXT;
   Dsply ('Delete failed: ' + Msg);
Else;
   Dsply ('Customer deleted: ' + %Char(CustID));
EndIf;

// 🟦 SELECT and display customers from Paris
Exec SQL
    Declare C1 Cursor For
    Select CustID, Name, City
    From Customers
    Where City = 'Paris'
    Order By Name;

Exec SQL
    Open C1;

Dow '1' = '1';
   Exec SQL
       Fetch Next From C1 Into :CustID, :Name, :City;

   If SQLCODE = 100;
      Leave;
   ElseIf SQLCODE < 0;
      Exec SQL Get Diagnostics Condition 1 :Msg = MESSAGE_TEXT;
      Dsply ('Fetch failed: ' + Msg);
      Leave;
   EndIf;

   Dsply ('[' + %Char(CustID) + '] ' + %Trim(Name) + ' - ' + %Trim(City));
EndDo;

Exec SQL
    Close C1;

*INLR = *ON;
Return;

📌 Requirements

• Table CUSTOMERS must exist with columns: CUSTID, NAME, CITY.

• System value QCCSID must support accented characters for names like "Jean Dupont".

• Program must be compiled as SQLRPGLE with embedded SQL (CRTSQLRPGI).

⚙️ Optional: Fetch Multiple Rows into an Array (IBM i 7.3 TR7+)

If you're on a supported release, I can show you how to define:

Dcl-S NameArray Varchar(50) Dim(100);
Exec SQL Fetch from C1 for 100 rows into :NameArray;