Even the best programmer in my one-person cubicle makes mistakes. I’ve worked in several different languages, and made the same mistakes in many of them; out of bounds array index, dividing by zero, messing with Texas.

Yet, when I switched from C programming to SQR programming, suddenly I stopped writing null-pointer bugs … and started to forget to increment my loop counters. If a language doesn’t have a certain feature, we can’t misuse it. That’s a plus. But if a language doesn’t have some other feature, we might be more prone to certain mistakes.

Here are some of the bugs that are easiest to write in SQR. The language doesn’t protect us from these mistakes.

1. Infinite Loops

Most languages have “for” loops. When we start a loop in C or Java, there is an immediate opportunity / reminder to initialize a counter, specify the condition for continuing to loop, and increment the counter. SQR has only while loops. When we start a loop in SQR, there is only an immediate opportunity to specify the condition for continuing to loop.

I’ve tried to train myself to write the code to initialize and to increment the counter as soon as I write the loop, but I sometimes forget. I’ve programmed a macro in my text editor called “for,” which asks me for the counter, start value, and end value, and then sets up a while loop.

The other way to exit a loop is the break command. Unfortunately, SQR (and C and Java) also use the break command to exit an evaluate (or switch) command. That complicates the exit process if there is an evaluate command within a while loop. We might write an if command within a while loop to control the decision to break. Have you ever replaced that if command with a multibranch evaluate command and found (eventually) that the break stopped working?

Consider “extending” the SQR language with the following pseudo-commands. They don’t eliminate the break bugs, but they may help a reader find them.

#define exit-evaluate break
#define exit-while break

2. Variable Name Confusion

Many were the times I’ve carefully summed all the #amount into #total, only to have my disloyal fingers type:

print $total (+1,1)

Some languages require us to declare variables to help the compiler know how to handle them. SQR allows us to declare our variables, but usually determines type (string vs. number) and scope (global vs. local) itself. SQR saves us time and effort, but leaves us vulnerable to mistyped variable names ($total, #totl, #totals).

SQR variables default to global, not local. SQR procedures that don’t use parameters can access global variables “natively.” If we add parameters to the procedure call as an afterthought, suddenly those variables are local variables with no relationship to the global variables of the same name.

The first version of SQR did arithmetic with the add, subtract, multiply, and divide commands only; it didn’t have the +, -, *, or / operators. That allowed for variables like #amount-1, #amount-2, and #amount-3. Those variables are still supported, for backward compatibility, but they look a lot like #amount – 1 (subtract 1 from #amount).

3. Mixing Up Prepositions

OK, this is just me having trouble remembering similar but distinct syntax. The compiler will catch these mistakes readily enough, but SQR prepositions are nuisances for me.

  • add … to
  • divide … into
  • multiply … times
  • subtract … from

Maybe it’s just me, but I always want to type “multiply … by.” To my ears, either multiply phrase implies that the result will be stored in the first variable.

  • get … from
  • put … into
  • read … into
  • write … from

Put is the opposite of get. Write is the opposite of read. The opposite of from is to – not into. Yes, “into” makes sense if you visualize variables as boxes that contain values. Maybe we should use “out-from” rather than “from.”

By the way, if “get … from” or “extract … from” or “write … from” is within a begin-select block, and the from keyword is at the beginning of a line, SQR will confuse it with the SQL from that introduces the database table names. The solution is to end the previous line with a dash.

  • concat … with

I always forget, and think I’m programming in a language that uses “append … to”, which would make it clear that the first string is put at the end of the second string.

  • string … by … into
  • unstring … by … into

Unstring is the opposite of string. Unlike all the other opposite commands that change the prepositions, this pair swaps the arguments. The string parameters are (1) a list of strings, (2) a delimiter, and (3) a variable which holds multiple, delimited strings. The unstring parameters are (1) a variable which holds multiple, delimited strings, (2) a delimiter, and (3) a list of string variables.

4. Comparing Date Strings

Even after the introduction of date variables, it is still possible to store dates in string variables and use them in SQL statements. Note that the database can compare the EFFDT column in a table with the $asofdate string variable in the SQR program if $asofdate is formatted for the database (e.g. Oracle’s ’12-APR-2009′).

But when we write the comparison in SQR,

let $asofdate = '12-APR-2009'
let $effdt = '31-MAR-2009'
if $effdt <= $asofdate
  show 'This command will not execute, because…'
  show 'the $effdt STRING starts with a "3" and that is GREATER '
       'than the $asofdate STRING that starts with a "1"'

We need to declare $asofdate and $effdt as date variables. If they’re used in SQL statements, SQR will reformat them for the database automatically. If they’re used in the display or show commands, SQR will reformat them according to the date-edit-mask in the sqr.ini file or the alter-locale command.

5. Undone By Unstring

String and unstring are nifty little commands that promise a lot. Unstring, in particular, seems like an enormous relief from multiple use of instr() and substr() and fiddling with positions and lengths within the source string. Yet, unstring will disappoint you like your first paycheck’s difference between gross pay and net pay.

String will take any number of inputs and concatenate them to a single output variable. Unstring will take a single input and separate it into a FIXED number of output variables. It’s great if we know the number of items in the input string, but if we underestimate that number, the end of the string will be lost without notice.

This is the bug enabling part; the only way to determine whether unstring has discarded data is to parse the input string ourselves. If we have more than enough output variables, we have to test them all to see which ones have values. If we want to process the outputs in a loop, we need a put command to put them into an array.

Unless we’re very confident of the quality of our input string, the unstring command is not worth the risk.

6. Lining Up Columns and Values For Insert

SQR inherits this problem from SQL, but the meta-SQL in Application Engine shows us a better way. When we insert a row into a table, we have the option to list the columns of the table. Then we must list a value for every column. In the Peoplesoft environment, where character columns are not allowed to be null, we end up listing every column twice, and they had better be in the same order with no omissions. Meta-SQL in Application Engine allows us to omit columns with default values (char = ‘ ‘, number = 0) and use assignments (columnname = value) rather than parallel lists.

7. Ambiguous Lookup Results

When lookup doesn’t find a key value, it returns null. When it does find a key value, and the return value is null, it returns null. This isn’t a common problem in Peoplesoft environment because character columns are always defined as “non-null,” but date columns can be null.

Many other commands signal when they fail. Open, read, and write have an optional status flag. Begin-select and begin-sql return counts of the rows they’ve read or changed, and can jump to an on-error procedure. SQR tells us if a let command has divided by zero, and the divide command lets us specify the correction for that case. Lookup doesn’t even recognize a failed search as an error.

Similarly, load-lookup doesn’t care if it doesn’t load any rows (due to an empty table or an overly restrictive where parameter).

Easiest Bugs To Fix

On the other hand, SQR makes some bugs easier to fix. The compiler warns us about the #amount-1 ambiguity. The compiler validates SQL statements up front, rather than waiting thirty minutes into the program execution for the database server to reject them.

Looking Ahead

Numbers are probably the most important aspect of business applications, but dates may be a close second. At the same time, dates are more complicated to manipulate. Next week, we’ll scratch the surface.