Posts

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...
1 comment
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 ...
Post a 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