Over the last year I’ve been using SQL more and more for all my RPG file access needs. The more I use it the more I like it and I’m building my own little set of reusable components and settled in with some naming standard that work for me.
I thought I would enter them here in case they helped someone else, or one of the many freshers out there learning to program with RPG on the IBM i System.
Never ever start variables or constant names with ‘SQL’something, those names are reserved for the SQL Precompiler (for example for defining fields within the SQLCA or SQLDA).
Even though there is today no field named SQLYourVar, IBM may update the data structures tomorrow and integrate a new subfields with exactly the name of your variable or constant and them *boom* your program stops working: The “best” thing that can happen is that your program will no longer compile. If the compile completes normally it is even worse, because wrong information is moved to your parameters or the SQL variables.
Use CONST for all inputs and then generate a return variable. In the olden days of RPG3 and RPG400 – a parameter was typically passed into a routine, updated, and returned. Scrap this technique and lean towards passing in a variable as a CONST and then returning a fresh variable as the return version of that. ie: ADDRESS CONST is passed in and RtnAddress Variable is returned. It just makes for cleaner code.
Some people hate prefixing field names with special characters. I’ve been editing HTML and PHP for a while and like the standard of prefixing a variable with a “$” to show its a variable. So if I see a field called “$count” or “$something” then I know its a transient variable, a throw away variable, something that is just used for this procedure
Use sub-procedures for any small “single use” pieces of code. Make sure that parameters passed in are either pointers (an efficient way of pointing at a memory space) or just code then as varying values.
d s 1024a varying d s 1024a varying
dcl-c SquirrelSuccess const('00000') dcl-c SquirrelNoData const('02000') dcl-c SquirrelFileClosed const('24501')
These are constant values so we can check an SQL Input/Output status after its been attempted. For example:
run sql select * from something
If SquirrelSuccess; // do something elseif SquirrelNoData; // error handling for nothing was read elseif SquirrelFileClosed // error handling for file closed endif;
Obviously – call the variables anything you like.
No Squirrels were harmed during the making of this motion picture 😉
IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.
RPG Programming Standards – Code Style Guide for IBM i, iSeries and AS400 Programmers
Turnover Pre-Run and Post-Run Promotion Form Commands
Funny Spam mail from Grill Insider
ILEditor connection problem with PUB400
IBM Rational Developer Ports
Upload a SAVF using IFS and IBM i Access Client Solutions (no FTP)
Using RPGLE to extract invoice numbers from an alpha string
How do we Check for Batch or Interactive in CLLE
Second Look: IBM RDi Extract Procedure Refactoring feature