Count with SQLRPGLE Variable File Name Been having fun with SQL and RPGLE this afternoon… the question was “How do I find out if and how many policies exist in one of the reservation files for a given customer code?” Obviously, we could write a little piece of RPGLE doing a READ LOOP and incrementing

Read More

Let’s look at a quick little example RPG email validation program – SQLRPGLE ILE There are lots of code snippets, example programs, and long waffling discussions in internet land filled with pointy-headed AS400, Series, and IBM i programmer chaps (and chappettes) arguing about the best way to “validate an email address in RPG”. I know

Read More

I was updating a little RPG utility this morning to add some more detail to a spreadsheet it generated. Adding the Library Description will help the non-propeller heads who read this stuff. This is super easy to do with a single line of SQL added to your RPG program. What is this spreadsheet you are

Read More

Data Area (*DTAARA) using IBM i SQL Our first problem is, SQL doesn’t know what a *DTAARA is. SQL reads data from Tables but our gloriously quirky IBM i Box has other places to secretly store data. Data Areas are just one of these tricky little blocks of data that other machines don’t know about

Read More

In the middle of this RPG program, I want to reformat and replace some values in a big long address field. But for the life of me… I cant remember how to use the SCANREPLACE built in function in RPG. #Dingbat /me memory circuits need a service methinks. So, like all programmers, I wonder off to

Read More

Using SQL in RPGLE programs is easier than you think. Making the move from RPG native file IO to SQL database IO is really quite straightforward:  Changing from good old READE loops to SQL FOR Loops simply means using the SQL CURSOR function. SQL has a groovy way of referring to what the stuff that

Read More

Substring in RPGLE SQL This morning, I had to make a little change to an RPG4 program to let it respond to a new parameter being passed into it via JSON. This value had to be stored in the first character of a field in a physical file in the database. Simple Logic and we

Read More

I found this excellent article by Birgitta Hauser, Software and Database Engineer, Toolmaker Advanced Efficiency GmbH. It covers the basic concepts you will need to consider if you want to “Replace IBM i Native File Access with SQL” Just in case it vanishes I’m going to reproduce it here: If you are considering SQL and

Read More

AKA – Why does this compile fail using SQLRPGLE and QUALIFIED data structures? Sometimes I find a problem and scratch my head and just cannot figure it out. I swear a lot. Then I discover the solution by climbing out of my little box, turning my head at a funny angle, squinting and thinking a

Read More

RPG PROGRAM USER(*OWNER) – Adopts the object owner level of Authority Most IBM i Programmers are aware of the USER(*OWNER) compile setting — anyone calling this program will adopt the authority level of the program. So if a program is compiled by user QSECOFR and USER(*OWNER) then anyone calling that program will automatically be elevated

Read More

SQL Tables with Indexes and Views are faster than DDS Phsyicals and Logicals. #HONEST SQL is a wonderful beast for quickly getting data from huge tables (aka Files). The trouble with SQL is that it’s so very simple to use; programmers get lazy and sometimes forget to do the analysis to make it as simple

Read More

Us IBM i blokes love reading things from files. Hashtag internet fact. But sometimes we just want to read a certain number of records, or rows in SQL speak. For example, when building a page by page subfile with say 15 lines per page, we might want to be able to read an entire file,

Read More