Posts

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

(IBM i fans only) Retrieving Files Used in Queries of an IBM i Library

By -
Image
A colleague of mine left me a real treasure trove of queries, accumulated over many years of work. It’s an endless list: over 1000 queries, all well cataloged and described. Recently, I needed to find a query related to customer orders. Knowing that the order file is called OTORD, the customer file CLCLI, and the sample file CMCMP, I started opening the most likely queries. The process was long and frustrating: Open Query > Specify file selections > View the files used > Close Query. Stressful, right?   So, I thought I’d let our trusty IBM i do some work for me. I created a procedure to retrieve the list of files used by all the queries in a specific library, with the ability to add some filters to answer more specific questions. For example:   Which queries use the files OTORD00F, CLCLI00F, and CMCMP00F?   The result? A clean and well-organized subfile: Don't forget to set the screen to 132 columns! The source code follows: FQRCL    ...
Post a Comment
Read more