Posts

Using a Parameterized Cursor in SQLRPGLE

By -
Image
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...
Post a Comment
Read more

Dynamic SQL in RPG

By -
Image
Simple Example of Using Dynamic SQL in RPG Dynamic SQL is a powerful technique on IBM i that allows you to build SQL statements at runtime instead of hardcoding them. This flexibility is essential when your SQL needs to adapt based on user input, configuration, or variable conditions. In this post, I’ll walk you through a simple RPG example that uses a parameterized dynamic SQL statement to retrieve data from a file. The Program **FREE // ************************************************************* // * Author.......: ALDO SUCCI                                 * // * Description..: Simple example of using dynamic SQL in RPG * // *************************************************************   // Declare variable to hold the customer type (3 characters) dcl-s wCLCIB0 CHAR(3);   // Declare variable to hold the customer name retrieved from the database dcl-s wCLNOM...
5 comments
Read more

SQL Indicator Variables in Embedded SQL for RPG on IBM i

By -
Image
SQL Indicator Variables in Embedded SQL for RPG on IBM i When working with DB2 on IBM i, handling NULL values properly is essential, especially when you retrieve data using embedded SQL in RPG. In this post, I’ll show you a simple and practical way to check if a column is NULL using what’s called an SQL Indicator Variable . The Scenario Let’s say you have a customer file called CLANA00F with the following fields: Field Type Length Description CLANN0 CHAR(1) 1 Customer status CLCCL0 DEC(7,0) 7 Customer ID CLCIB0 CHAR(3) 3 Customer type CLNOM0 CHAR(50) 50 Customer name CLSTS0 CHAR(1) 1 Customer status Now, imagine that CLSTS0 is nullable (i.e., it can contain NULL ), and you want to find out whether it has a value or not. The RPG Example **free ctl-opt dftactgrp ( *no ) actgrp ( *caller ); // **************************************************************** // * Autore.......: ALDO SUCCI ...
1 comment
Read more

RPGFree reading and updating a file using SQL embedded syntax

By -
Image
Reading and Updating a File Using SQL Embedded in RPGFree In this tutorial, I’ll show you how to read a file from the beginning and update a field in the same file using embedded SQL in free-format RPG IV (RPGFree). Step 1: Create the Physical File First, we need to create a file and populate it with some test data. Create the file using the following SQL source: Source member: IPFLSQL Source file: myLib/MySourceFile -- IPFLSQL.SQL -- Create the physical table IPFLI00F CREATE OR REPLACE TABLE myLib/IPFLI00F ( IIFANN CHARACTER(1) NOT NULL WITH DEFAULT, IITEXT CHARACTER(25) NOT NULL WITH DEFAULT ) RCDFMT IPFLI; Run this member with the following command: RUNSQLSTM SRCFILE(myLib/MySourceFile) SRCMBR(IPFLSQL) Now, manually insert a few records into the file IPFLI00F using DFU, ACS, or any method of your choice. The values are not important — just a few test records. Step 2: Compile and Run the RPG Program Below is the RPGFree program that reads all records ...
4 comments
Read more

options(*CONVERT) in RPGfree '

By -
Image
The *CONVERT option in RPG is specified to tell the compiler to automatically convert a value passed as a parameter to the correct type if it differs from the type defined in the procedure.   How OPTIONS(*CONVERT) Works OPTIONS(*CONVERT) allows for automatic data conversion between compatible types when a procedure or function is called with a parameter of a different type than expected. For example, if a procedure is defined with a numeric parameter and a different type, such as a decimal, is passed to it, the compiler will automatically handle the conversion so that the value matches the declared type. For example: **free //      ******************************************************* //      * convert to char                                    ...
2 comments
Read more

(IBM i fans only) Retrieving Files Used in Programs, SQL packages, Service programs, Modules, Query definitions in an IBM i Library

By -
Image
In this post, we’ll explore how to efficiently retrieve program files used in queries within an IBM i library using the DPR  command, along with its associated CLP ( DPRCL ) and SQL RPGLE program ( DPRRPG ). This combination allows users to dynamically fetch program file references based on various filters, enhancing the management of programs in the IBM i environment. Overview of Components 1. The DPR Command The DPR command serves as the entry point for retrieving program files. It accepts two parameters: the library name and the object type. Below is the source code for the DPR  command:                CMD        PROMPT('Show Pgm Files via DSPPGMREF')  /* COMMAND PROCESSING PROGRAM IS: DPRCL */                PARM       KWD(LIBRARY) TYPE(*CHAR) LEN(10) MIN(1) ...
Post a Comment
Read more