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
What is IBM i Email and SPF?
Updating Numeric DTAARA in RPGLE
How to capture IBM-i job info for submitted jobs
Register license key in SOFTLANDING SOFTMENU
Going the (Levenshtein) Distance in RPG Free
Don’t hardcode library names in your TURNOVER SQL source #youbigsilly
How to Install IBM Access Client Solutions (ACS)
5733XJ1 IBM i Access Client Solutions – QuickStartGuide