In the ten years that I have been programming in SQR, I’ve found a few cases where the compiler is a bit more limited than the documentation implied. However, I can only think of one example in which SQRW acted in a way that was unambiguously wrong.
SQR Reserved Variables
Every SQR program may use a collection of SQR reserved variables. The SQR Language Reference for Peoplesoft lists them in chapter 2, “Understanding SQR for Peoplesoft,” in the “SQR Data Elements” section. The SQR interpreter sets some of the variables before beginning to execute our code, like $sqr-database, $sqr-platform, and $sqr-program. Commands within our programs set other variables while executing, like #current-column, #end-file, and $sql-error.
These reserved variables behave like the normal string or numeric variables they resemble. We can use them in let commands, procedure calls, print commands, and so on. We can even set their values ourselves, although that would usually ruin their utility as messages from the SQR interpreter.
SQR Display and Show Commands
SQR has two commands, display and show, that output unstructured data to a console window and a text file. Display is similar to print in that it outputs one variable or literal. Show is similar to write in that it outputs a list of variables or literals in a single command. These commands are useful first, as real-time communication from the program to the user, and second, as historical record of the program. Remember, whatever the program outputs to the console window, it also outputs to the log file. In theory.
Suppose we wish to make the filename of the source code appear in the console window and in the log file. The command I tried was “show $sqr-program”, and it didn’t work. I explored the problem with the following program.
show 'line 1: "' $sqr-program '"'
let $x = $sqr-program
show 'line 2: "' $x '"'
let $x = '*' || $sqr-program || '*'
show 'line 3: "' $x '"'
move $sqr-program to $x
show 'line 4: ' $x
display 'line 5: ' noline
Later, I noticed that it produced a log file that seems perfect.
line 1: "c:\sqr\test5.sqr"
line 2: "c:\sqr\test5.sqr"
line 3: "*c:\sqr\test5.sqr*"
line 4: c:\sqr\test5.sqr
line 5: c:\sqr\test5.sqr
However, this is what I saw in the console window.
line 1: ""
line 2: ""
line 3: "*c:\sqr\test5.sqr*"
There are four cases in which display or show outputs to the screen and the filename does not appear: outputting $sqr-program itself by (line 1) the show command or (line 5) the display command, outputting a variable that equals $sqr-program exactly, whether $x is set by the (line 2) let command or the (line 4) move command
There is one case in which show outputs to the screen and the filename does appear: when $sqr-program is part of a formula that sets $x. Another case that worked is when show outputs $sqr-program itself, but with an edit xxxxxxxxxxxx clause.
Think About This Bug
The more I thought about the bug, the stranger it seemed. SQR could show or display any other variable correctly, even other reserved variables. SQR could use $sqr-program properly in other contexts, like formulae and print commands. I have several questions and have been left unanswered.
- Is there something unique about the $sqr-program variable? If so, why?
- When we set one string equal to another string, with let OR move, does SQR not perform a memory allocation and byte-by-byte copy?
- How is the output to screen and output to log file linked if it works in every single case and variation except one?
The Support Problem
There were two obstacles in seeking help with this problem. First, people trying to help will ask us to send them a copy of the log file. Unfortunately, display and show output correctly to the log file. It seems to be evidence that there is no problem. The only way to demonstrate the problem is to run the program on a PC and watch the console window.
The second obstacle is the support policies of the software vendors. Peoplesoft Inc. and the Peoplesoft division of Oracle Corp. support(ed) the delivered functionality of the Peoplesoft applications. If our paychecks miscalculate, they will help us. They did not and do not support the customizations we add ourselves. That means not helping us debug the SQR programs we write.
I discovered this bug in 2006, when Hyperion was an independent company supporting their own version of SQR. Unfortunately, I wasn’t their customer and had no right to technical support.
I was wondering if anyone had some insight on converting a large number of SQR reports from OCI to ODBC.
any help or pointers would be appreaciated.
I did this:
let $x = ‘”‘ || $sqr-program || ‘”‘
display ‘line 5: ‘ noline
and I got this:
line 5: “c:sqrtest5.sqr”.
It’s an interesting but not insurmountable puzzle.