| Frequent Sources of SQR Bugs | KEVIN RESCHENBERG
02-22-2004 |
Having written, reviewed and/or debugged hundreds of SQRs and SQCs by now, I've noticed some patterns in the types of bugs
that tend to occur frequently. The following readily come to mind. Here—the result of very unscientific observation—is my Top 10 List.
(Oh, by the way—using our debugger can help you work through many of these!)
Top 10 Causes of Bugs in SQR Programs
#10: Misunderstanding page heading processing and timing
Page headings are produced at the end of a page, not at the beginning as you might expect. If you maintain variables containing information
for the page heading, be sure you understand this point or the heading information might not match what's on the page.
#9: Misunderstanding ON-BREAK
ON-BREAK is a powerful option that can be coded along with a column name in a SELECT. Used properly, it can help with the flow of control
of your program. Used incorrectly, it causes headaches. If you use ON-BREAK, be sure that you understand which columns and other variables
have "new" vs. "old" values when the break occurs. (See also #8.) While learning SQR initially, I did not fully understand how ON-BREAK really
works, and so I got into the habit of collecting all of the column values at once and then coding my own break logic. That works but I wouldn't
necessarily recommend it.
#8: Misusing code between column (field) names in a SELECT
Some programmers retrieve all columns in a SELECT and then begin with the code to process them, while others specify a column and then immediately
follow it with code for processing that one column. I prefer the first method. If you use the other method, be sure that, once again,
you know which values are available and understand the fact that SQR will retrieve the one column and run your processing code before retrieving
the next column's value. Referring to columns other than the one you have just retrieved can cause bugs.
#7: Failing to test run control
Most SQR programs require run control parameters. To make testing and development easier, programmers often code the run control SELECT (or SQC call)
but then code another option, using INPUT to ask for values when the program is run outside of PeopleSoft using SQRW. This can work, but unfortunately,
most of the testing is then done with the INPUT method, which will not be used after the program is moved into production. I recommend that you
don't do this, but instead "trick" SQR into thinking that your program was run from within the system by answering the three or four questions that appear
when you run your program. If you have no idea what I'm talking about, I'll deal with this subject in next week's topic.
#6: Variable misspellings
#Amount and #Amuont are two different, valid variables. SQR doesn't require us to declare variables, so a misspelling isn't caught during the compile
phase and it's up to the programmer to find the bug. You have probably seen programs that move column values into variables, then shift those
variables' values into other variables, and so on until (hopefully) they are eventually used. I try to use fewer variables in order to reduce the
possibility of misspellings. In most cases you can use column values ("&" variables) directly without moving them into other variables. These
have the additional advantage of causing compile errors if they are misspelled.
#5: Misunderstanding or mixing up local and global variables
I trip up on this one all the time. Most languages that provide for global and local variable scopes allow you to declare a global variable and then use
it anywhere in the program. SQR allows this, too, except that the exact name of the variable changes depending on where it is used. In a global
procedure (any procedure that does not accept parameters listed within parentheses and that does not include the LOCAL keyword), a variable name
will be something like #Amount. But if you want to use that same variable within a local procedure, you must code it as #_Amount. Forgetting to
code the underscore creates a new local variable called #Amount which has no connection with the global #Amount. The same thing can happen if you
create a global (regular) procedure, add a lot of code to it, and then later decide to pass parameters to it and turn it into a local procedure.
If you forget to go through the code and add the underscores, the program will still compile but it won't work.
#4: Bad initialization
If you are asked to maintain a program with a procedure called Init-Vars or similar, be careful—this can be a red flag. When you add new variables
to your program, check to see whether they must be added to the initialization procedure as well. I prefer to initialize my variables inline,
immediately before they are needed, instead of initializing them in some other procedure or after use. I find that this almost always simplifies
the logic and prevents errors of this type.
#3: Failing to use RTRIM()
'Y ' is not the same as 'Y' in SQR. Depending on your database and various options, string values may contain trailing spaces. This will break
your comparisons (IF, EVALUATE, etc.). Before using a string or column variable in a comparison, RTRIM it. Just get in the habit of RTRIMming
everything that might be used in comparisons. It will eliminate a lot of hard-to-find bugs.
#2: Bad SQL
If you have bad joins or other problems in your SQL, the most careful SQR coding practices won't help. Test out your SQL if possible in a query
tool such as Query Analyzer, SQL Plus, or Toad. Then copy it into your SQR. Some people even use PS Query to create SQL. Be sure to strip out the
security code that PS Query adds if you don't need it in your SQR.
And the #1 cause:
#1: Slipshod design and coding
Convoluted flow of control, monstrous IFs, no comments, random indentation, nonsensical variable names, IFs that don't consider the ELSE
condition...I could go on and on, but will just say that these all provide a welcome nest for bugs. Fortunately, there are a few very simple
tips that can help to eliminate a lot of this. I'll pontificate on that subject soon!
|
