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. Note, SQL has some features that native database IO does not support – namely huge long table names (aka

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

Find the IBM i System Name Three Ways Let’s look at three techniques to find the IBM i System Name A super easy technique using SQL in an SQLRPGLE Program Just as easy in a CLLE prograam Calling the IBM i *API to get the information, an RPGLE program Let’s dive straight in: (1) Get

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 wish I could take credit for this article. But I cant 🙂  It hits the nail on the head and the author (Daniel Gray) explains himself very clearly: Getting up to speed with PHP on the IBM I If I can, I’d like to offer my IBM I friends some advice on getting up

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

Most IBM i shops using webservices to talk to the Internet, will convert the data they are sharing into a web-service format like XML or JSON before sending it out. Obviously the responses will be in the same format and they need to convert it back into native IBM i format to use it. That

Read More