Ever wanted to find your IBM i OS Version using SQL? Following on from this blog about how to find the OS version from an RPG Program, a nice chap called Tomas Petris left a comment showing a slick way of pulling the IBM i Version with one SQL statement: The SQL statement is simple

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

IBM i SQL numeric to alphameric So – today I was talking to a colleague who wanted to make a database change to a big old file. This meant changing one of the fields from 7 numeric to 12 alpha. Sounds simple right? Oh if only life were that simple… Now add the complexity that

Read More

I found this wonderful article over at Midrange Blog It does exactly what I am trying to do: Lets me encrypt a certain field (column) in a table so that its encrypted as its added and updated. It also lets me automatically decrypt it when I read the record back (the only caveat is that

Read More

IBM i SQL – Using SUBST A while ago I had a problem where I wanted to change the first two characters in a table and I used the very simple concat and subst operations to do it with SQL. Today, we had a slightly more complex requirement of updating text in the middle of

Read More

Using SQLRPGLE to Validate email addresses in IBM i Following on from my previous blog (basic email validation) I decided that it would much nicer to (a) have the regex stored in a data area so that we can easily play with it to try different validation scenarios and (b) lets have a shot at

Read More

AS400 hundred year date format I had to pull records from two files with a matching date value in each file. To make things interesting, the date on one file was stored in timestamp format and the date field in the other file was stored in hundred year date format. SQL (and QUERY400) can very

Read More

IBM i SQL to validate Email So, we have a customer master file that’s been around for years. Users have been keying in customer info and sometimes adding email addresses, sometimes not, sometimes entering nonsense, or even just text that says things like “this chap has no email so don’t email him”. Yes, it’s a

Read More

Back in the olden days – files were filled with numbers and long string of data. Words like SAUSAGES might have been stored as a 30 ALPHA field in the format “SAUSAGES______________________” which, even tho its padded with *blanks – is just wasting a lot of space. Nowadays, most systems use variable length fields, shorter,

Read More

SQL works very nicely on IBM i Servers — the added benefit is that we can access the database using SQL from other machines, access it using IBM SQL and also using Native (DB2) File IO. But, SQL has some features that native database IO does not support – huge long table names (aka “alternative

Read More

Want to Scan Replace in RPG? 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 can’t remember how to use the SCANREPLACE built in function in RPG. #Dingbat /me memory circuits need a service methinks. So, like

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