IBM i fans only - tips and tricks to start RPG free format programming with simple examples

How to Use GET DIAGNOSTICS in SQLRPGLE to Retrieve Row Count on IBM i In this post, I’ll walk you through a simple and effective way to retrieve the number of rows affected by SQL statements in your SQLRPGLE programs using the GET DIAGNOSTICS statement. Whether you're inserting, updating, or deleting rows, GET DIAGNOSTICS allows you to programmatically capture how many records were impacted. What You'll Learn How to use GET DIAGNOSTICS after an SQL operation How to check the number of rows inserted, updated, or deleted A practical example using two test tables Prerequisites Before running the example, create two tables using these SQL statements: CREATE TABLE TESTFILE ( TFTYPE CHAR(2) NOT NULL WITH DEFAULT, TFNUMB NUMERIC(7, 0) NOT NULL WITH DEFAULT ); CREATE TABLE TESTFILE2 ( TFTYPE CHAR(2) NOT NULL WITH DEFAULT, TFNUMB NUMERIC(7, 0) NOT NULL WITH DEFAULT ); Then populate TESTFILE with the following sample data: INSERT INTO T...

Using a Parameterized Cursor in SQLRPGLE – A Simple and Clean Example When working with embedded SQL on IBM i, using a cursor with parameters is a great way to selectively retrieve data from a file based on runtime input. In this post, I’ll walk you through a simple SQLRPGLE program that demonstrates this concept. This free-format RPG example shows how to: Accept an input parameter Open a cursor based on that parameter Fetch records from the CLANA00F file Display basic customer information Properly handle SQL errors What Is CLANA00F? The file CLANA00F contains customer master data. Here's a simplified layout of its most relevant fields: Field Type Description CLCCL0 S(7) Customer numeric ID CLNOM0 A(50) Customer name CLCIB0 A(3) International code Our goal is to retrieve only those customers whose CLCIB0 matches a parameter passed when the program is called. Program Highlights ctl-opt dftactgrp(*no) actgrp(*caller); dcl-s CLCCL0...