An SQR Wish List

As much as I like SQR, I am reluctantly willing to admit that it is not perfect. Ray Ontko, one of the pioneers of SQR, listed his ideas for improvements on his company’s website. Here’s my wish list. Given the maturity of SQR, I don’t expect any of the changes to occur, but I’ve had many happy daydreams about using these features.

Borrow From Other SQR Versions

SQR already does these things, just not the version of SQR that ships with Peoplesoft. Would it be so hard to give us these?

Array Extensions: when we use an index value higher than the array’s number of rows, SQR will enlarge the array, as it does with the load-lookup command.

Multiple Databases: SQR can connect to more than one database, for applications that compare databases or copy from one to another.

Bitwise operators (bitand, bitor, bitxor): there’s no point in me describing Boolean arithmetic until we get it, and I’m not sure what I’d use it for, but if those other SQR programs have it, I want it too.

Built-in functions: command_line returns the entire PSSQR command line for inspection; filesize returns the size of a disk file in bytes; fromhex, hex, and tohex deal with strings of hexadecimal characters; isnumber reports on whether a string would be valid as a number; lengthp, lengtht, substrp, substrt, transform, and unicode deal with multibyte-character strings.

Enhance Commands

Some SQR commands already do most of what I want. I just want a little more.

The commit and rollback commands have an enormous impact on the state of the database while an SQR program is running, yet they leave no trace. I wish they left a message in the log file like load-lookup does.

I suggested many enhancements to the load-lookup and lookup commands in my March 15, 2009 entry, “5 Uses: Load-Lookup Love Letter.” I wish load-lookup would tell us how many rows it read, and what duplicates it found. I wish lookup could take and return numbers or dates when appropriate and set a flag when it couldn’t find an entry for the key value. I wish lookup could return rows by row number.

File I/O (open, read, write, and close) in SQR is sequential and record oriented. It can open files for reading or for writing. It can handle text files with variable length lines with CR/LF line delimiters in Windows, LF line delimiters in Unix.  I wish SQR supported random-access file I/O. I wish it supported streaming I/O. I wish it could open a file for both reading and writing. I wish it could handle variable length lines with any delimiters.

I wish there were a name= parameter for begin-select and begin-sql to give them a unique identifier to which other commands could refer. Begin-sql would also need a no-wait option to tell SQR that it can continue execution while the database handles the SQL command(s), similar to the nowait option for call system. This would support the new start-select and waitfor-sql commands that I will describe below.

I wish there were a row-count= parameter for begin-select to identify a numeric variable that would contain, at all times, the number of rows that the SQL statement has returned to the SQR program so far.

Add Commands

With complicated joins, grouping, summary functions, and sorts, it may take a SQL select command a long time before the first row is available for SQR. If begin-select is named (see above), and the bind variables are already known, the start-select command could send the SQL to the database server in advance, and have the results waiting when SQR reaches the begin-select command.

Similarly, a SQL DML command (delete, insert from a select, or update) can also take a long time. If begin-sql is named and has a no-wait flag (see above), SQR can continue working on the file server or the client while the database server performs the SQL command(s). However, there may be a point at which SQR should pause until the database server is finished. At that point, we need the waitfor-sql command with the name of the begin-sql paragraph.

I wish there were a command to sort an array on one or more fields, ascending or descending. I wish there were a command to search an array for a particular value in a particular field, returning the index. If the array is sorted on that field, the search command should use a binary search.

It’s not easy handling files after SQR has created them, and it’s an odd design to delete() or rename() by function. I wish we had delete and rename commands, and also copy and ftp commands.

We use the break command to exit from an evaluate and from a while loop. When there is an evaluate within a while loop or vice-versa, the break only applies to the inner structure. I wish there were exit-evaluate and exit-while commands to clarify where we want to go.

Speaking of exiting, the only way to leave a procedure is at the end. I know that conforms with the teaching of structured programming, but it can take some messy code to make it to the end of the procedure when we’ve realized early that we don’t want to execute anything in it. I wish there were a return command.

Speaking of loops, there is life after while. The do-until loop guarantees that the code within will run at least once. The for loop helps us to remember to initialize, increment, and inspect the counter.

We can write our own functions, but they have to be written in C, compiled by a C compiler, and linked to the PSSQR object files. Has anybody ever done that?  I wish there were a begin-function variation of begin-procedure, and a return command to return a value to the function call.

Add Built-In Functions

SQR has built-in functions like abs(), upper(), and datetostr(). These are like procedures that can be placed within an expression, that take the value equal to the result of their calculation. I thought of a few more that would be useful.

Fileinfo($filename, $info) returns information about a disk file. The value of $info can be ’size’, ‘created date’, ‘modified date’, ‘permissions’, ‘drive’, ‘path’, ‘extension’.

Filename($regexpr) takes a regular expression, like ‘c:\sqr\*.sqr’ and returns the first file that matches it. Subsequent calls to filename() take a null string, and return additional files that match the most recent regular expression.  That gives us an entire directory listing, one file at a time.

Like($var1, $var2) performs like the SQL like operator, comparing two string variables, either of which can contain a “%”.

Lookup(’name’, $key) performs like the lookup command, but returns the value to the expression that contains the lookup() function.

Max(dates, numbers, strings) finds the maximum value of a list of dates, a list of numbers, or a list of strings.

Median(dates, numbers, strings) finds the median value of a list of dates, a list of numbers, or a list of strings. This is a lot harder than it sounds.

Min(dates, numbers, strings) finds the minimum value of a list of dates, a list of numbers, or a list of strings.

Nbrtodate(#year, #month, #day) returns a date.

Proper($name) capitalizes the first letter of each name, and puts the rest in lower case. Perhaps it’s smart enough to handle roman numerals differently (King George III).

Random(#min, #max) returns a random number between #min and #max.

Unstring($string, $delimiter, #item) performs like the unstring command, except that it returns only one of the values, as specified by #item.

Add command line options

The PSSQR command line has flags that affect the compiler and the program. I wish there were three new ones to help me catch bugs.

A compiler option to require that variables be declared would reduce the chance that a typo in a variable name would go unnoticed.

An interpreter option to trace the program as it executes. This could show the commands being executed and the variables changing values.

An error handling option to list all variables and their values if the program ends in an error.

Let Literals, Variables, Array Elements, and Expressions Work Anywhere

In the open command, the file name can be a variable, the file number (handle) can be a variable, but the record length must be a literal number. In the load-lookup command, the where parameter can be a variable, but all the other parameters must be literals. The add command can take a literal or a variable for its first argument and a variable for its second argument, but not array elements for either. The if, let, and while commands and the built-in functions (e.g. mod()) are the only features of SQR that can process expressions.

I wish I could use variables, array elements, or expressions anywhere I can use literals.

Turbo-charge Variables

There is an SQR reserved variable for the database management system (e.g. Oracle) and for the login username. I wish there were a variable that contained the name of the instance (e.g. DEV, TEST, PROD, etc.).

I wish we could call a procedure with a variable.

let $procedure = cond(#row_exists = {true}, ‘update_row’, ‘insert_row’)
do $procedure


let $compare_proc = ‘compare_emplid’
let $swap_proc = ’swap_emplid’
do quicksort($compare_proc, $swap_proc)

I wish we could reference a single character within a string.

let $tenth_letter = $alphabet(10)
let $alphabet(10) = ‘j’

Take A Turn

Is there a programmer alive who didn’t want more from his or her language? For which features do you wish?


  1. Bob Josephson says:

    Isn’t automatically growing an array a dangerous feature? Resizeable arrays are great, but it really should be done explicitly by the programmer.

    • administrator says:

      I don’t know how it works on the other versions of SQR, so I can’t say whether it’s dangerous. Sometimes an array overflow means that I made a logic error, sometimes it means there’s an error in the data. In those cases, I’d rather have the program abort. Other times it just means that there’s more data than we expected. There might be better ways to deal with the need for a larger array, but I was referring to features that the makers of SQR have already created.