Are you an old school RPG programmer? There are so many times it’s great to be old school. I love traditions, but with RPG programming, old school will leave you behind. It sure is easier to just continue with the way you have always written programs isn’t it? I find myself in ruts like that all too often. Actually I was afraid to try embedded SQL in RPG. I had a coworker write some code using embedded SQL a few years back and I avoided that code like the plague. Now, like free form RPG, I wish I had picked up on this great method of data access and manipulation sooner. I didn’t use his model but I sure wish I had been more willing to pick up on embedded SQL back then. I can only imagine the great applications I could have written or enhance.

The most effective way I found to use embedded SQL is to separate it into a nice reusable procedure. By passing my file layout as an externally defined data structure to the RPG program with the embedded SQL I’m able to keep my RPGLE code separate. Now I’ve accomplished a small form of encapsulation and have a flexible data retrieval procedure for my physical file. Typically I pass in the SQL parameters to the procedure and generate the SQL code on the fly. Think about not having to create a logical file for every view of a physical file you want to use in an RPG program.

Several years ago I wrote an application of RPG programs. A major piece of any application is always the file maintenance. Well I thought it would be cool to refresh the screen with a message for each child record that was deleted. The boss and the data entry people thought it was kind of nice as well so they knew each child record being deleted even if the messages were too fast to see. After few years these files had grown extremely large. Deleting parent and all related child records was taking too long with all the messages being written to the screen. Add to that when the application was run on a remote system sometimes it could take ten or more minutes to delete on set of records. Enter embedded SQL to the RPG programs. Granted the writes to the screen were my bottle neck but after switching to the embedded SQL and removing the messages the performance of the delete increased a hundred fold. Never again did anyone wait even a minute for a large delete. Try embedded SQL if you haven’t. Like most things the more you do it the easier it will become.

I’m sure many of you have great stories on how embedded SQL in RPG programming has helped you. I am a big fan of RPG. I’m always looking to learn new techniques and sharpen my skills. Visit my site and let me know some of your stories or if you have a question I can help with.