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.
const?
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.
$ for variables?
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
sub procedures?
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
Checking SQL status?
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 😉