Ray's Top n Suggested Enhancements for SQR

Ray's Top n Suggested Enhancements for SQR

Today, n = 46.
SQR has a long tradition of being a practical programming tool. Much of the design of the language and the features it includes came from SQR being used in production report and programming environments.
Here's our list of suggestions for enhancing SQR. Some changes are little, and some are big. We've listed the ones we think would be most helpful first.
Debugger/profiler/coverage analyzer. Although SQR is fairly easy to debug by inserting print statements, there are times when it would be much easier to use an integrated debugger that would allow me to set breakpoints, tracepoints, watchpoints, step through the code, and examine and modify variables during the execution. An execution profiler or coverage analyzer (how many times did each statement execute) would also be nice, and should be easy to include if debugger support is added.

Avoid undefined variables. Add a command line flag which causes SQR to print a warning if any variables are referenced before they are assigned. The program could continue to execute, but a message should be logged.

Avoid bogus globals. Cause a warning message to appear if a global variable has a name that begins with #_ or $_. Although this is perfectly legal, it is almost always unintended. Provide a switch on the command line which supresses this message.

Add a mechanism that always references the global version of a variable, regardless of if it's in a local procedure or a global context. For example, #__variable or $__variable. These would always refer to the global variables "#variable" and "$variable". (This syntax could be used, for example, in small #INCLUDE file that might be included into both Local and Global procedures.)

Add a reserved variable (perhaps "#break-trigger-level") the contains the level number of the level that triggered a break in an ON-BREAK. This would be set as soon as SQR recognized that a break had occured (i.e. before the "after" procedures are called), and would be set to 0 when a row was read that didn't trigger a break. (Perhaps it would only be applicable within a begin-select paragraph [but see this suggestion].) This would allow (among other things) an "after" procedure to determine if any other after procedures will be executed after itself for this row of data, allowing the last procedure to execute to perform a NEW-PAGE or other "final" actions.

INPUT. Allow the PROMPT argument to be a $variable. Add a NOPUNCTUATION keyword for input to supress the ': '.

INPUT should be able to accept an arbitrary number of characters, rather than the current limit of 2046/2047 characters.

CALL SYSTEM should be able to accept an arbitrary number of characrers in its "command" string, rather than the current limit of 512

Allow OPEN to open "standard input" in READ mode and "standard output" and "standard error" in WRITE (or APPEND) mode.

More/better UNSTRING.

Add a REST keyword to allow the un-assigned portion (remainder) of a string to be put into a final variable.
Allow UNSTRING BY any one character in a string containing delimiter characters (any single character in the string will serve as a delimiter).
Allow UNSTRING BY one or more characters in a string containing delimit characters (any sequence of characters in the string will serve as a delimiter).
Allow UNSTRING to unstring INTO an ARRAY element or INTO an ARRAY field having multiple occurrences (instead of into a list of variables), maybe with a way to get a #count of the number of strings unstrung.
Finally, allow regular expressions as delimiters.

Better interactive prompting for connection information. Prompting should be consistent with the database server's standard application behavior. For example, the command line version of SQR for Oracle should accept 'username@database' on the command line and prompt for the password (without echoing it to the screen), as command line SQL*Plus does. The Oracle version of SQR for Windows should allow you to type 'username/password@database' in the "username" box, as SQL*Plus for Windows does.

Enhancements to String functions. Allow negative positions (from the end) in string functions. Allow reverse searches using instr().

Add new LET functions. initcap(s), regex_instr(), regex_replace() would be useful.
Allow MOVE to do multiple assignments. For example:
MOVE '' to $fred $barney $betty $wilma
MOVE 0 to #pebbles #bambam
It would be acceptable to disallow edit masks for this form, or to only alow a single edit mask at the end of the list.
PRINT RIGHT/CENTER/LEFT moves text within field/page. Allow RIGHT/LEFT/CENTER to be specified with PRINT and have it place the text appropriately on the line. If a field-width is given, have it place the text appropriately within the field.
Allow ELSE-IF to make it easier to avoid nesting IF statements. That is, we'd like to say
if #x > 1
  ! something
else-if #y > 1
  ! something else
end-if
instead of
if #x > 1
  ! something
else
  if #y > 1
    ! something else
  end-if
end-if
Allow PRINT ON-BREAK BEFORE= and AFTER= procedures outside of a BEGIN-SELECT paragraph. (It would be very useful, for example, to have this functionality when processing fields read in from a sorted flat file.) This may involve adding a new pair of commands, e.g. BEGIN-ON-BREAK and END-ON-BREAK, which would allow SQR to know when to execute the inital BEFORE= procedure and final AFTER= procedure calls (as BEGIN-SELECT and END-SELECT do now). (Perhaps there would need to be a rule forbidding BEGIN-ON-BREAKs to be nested within a given procedure [similar to the existing rules for BEGIN-SELECT].)

Additional functionality related to ARRAYs.

Add a SORT-ARRAY command which takes an array name and an ordered list of attributes to sort on, each of which may be followed by DESC/ASC.
Add the ability to declare an array to be the same in definition (except size) as another array. (Or, this might be accomplished using "STRUCT" datatypes; see below.)
Enhance CLEAR-ARRAY to allow clearing of one specified element of the array (rather than the default of clearing all elements of the array). (Alternately, add a command called something like CLEAR-ARRAY-ELEMENT.) This would set all fields in that element of the array back to the field defaults.
Add ARRAY-MOVE which moves an entire row from one entry in an array to another.
Add ARRAY-INSERT and ARRAY-DELETE commands to insert or delete rows from an array.
Eliminate the maximum number of arrays. Alternatively, make it a settable maximum in the -M file.
Allow an entire row of an array to be passed as a parameter to a procedure (this could appear inside the procedure as an array with only one row).
Allow arrays as parameters to procedures. This would enhance code-reusability (for sorting procedures, for example).
Allow structured variable declarations with CREATE-STRUCT. For example:
CREATE-STRUCT name = emp field = ssn:number field = last_name:char field = first_name:char field = address_1:char field = address_2:char field = hire_date:date field = monthly_quota:number:12

This would be like an array with a single row, and could be manipulated with PUT, GET, ARRAY-ADD (etc), and LET without specifying an array index. CLEAR-STRUCT would behave similar to CLEAR-ARRAY. Ideally, STRUCTs could be passed as parameters to procedures.
Allow load-lookup from a (text) file, and allow a lookup table to be populated directly from the SQR source code. Currently, a lookup table may only be loaded from the database.

New command-line options

Generate source listing. I would love a flag on the sqr command line that causes a file to be generated which contains the text that sqr compiles, after processing all compiler directives (#include, #define, #ifdef, {substitutions}, etc). Basically, the output of the preprocessor, perhaps with line numbers.
Syntax-check only. I would love to see a command line argument which would allow me to just check syntax, rather than always defaulting to running the program. For example, sometimes, after entering a lot of code, I want to just catch the typo's before continuing the development process. Although I can use -RS, it would be nice to have something which doesn't even generate the output .sqt file.
Multiple Output Formats. I'd like to be able to say "-printer:lis,ps,html,pdf" and have SQR generate ALL the formats I list, each in an appropriately named file without having to run the .spf file through SQRP for each.
Flag to supress html frames. I'd like to be able to say -XFRM to supress the creation of the .frm file and associated -TOC file when I say -printer:HT or -printer:EH. (Enhancement request #3506).

Support for Proportional Fonts (WRAP,FILL,RIGHT/CENTER/LEFT). Although SQR allows me to use proportional fonts in my reports, it doesn't really know enough about them to do them well. Better support for proportional fonts would include the ability to WRAP, FILL, CENTER, or RIGHT justify based on differing character widths.

Support for vertical placement within a line. Add a SUPERSCRIPT and SUBSCRIPT argument to PRINT.

Support for ITALIC in addition to BOLD and UNDERLINE.

PAGE-NUMBER and LAST-PAGE should allow BOLD, UNDERLINE, etc.

ALTER-PRINTER with no arguments should restore defaults.

Add reserved variables (perhaps #heading-lines and #footing-lines) or other mechanism for determining the number of heading lines and footing lines in the header and footer that apply to the currently active report.

Support for a boolean data type.
New Reserved Variables.

Add $sqr-command-line which would contain the exact command line of the SQR command.
Add $sqr-connect-string which would contain the username/password@connect portion of the command line.
Add $sqr-database-name which would be the -Ddatabase name or the instance name.
Add $sql-text which would contain the text of the last SQL command executed (for use in on-error procedures, for example).

Alternative method for obtaining database connection information at runtime. Ideally, we should be able to avoid putting passwords in command files or on command lines.

Better CALL/CALL SYSTEM USING. Allow WAIT and NO WAIT for all operating systems with the default being WAIT. Have CALL SYSTEM return the status of the executed program on all operating systems; under windows I get the near-useless status that COMMAND.COM executed successfully.

Allow expressions as parameters when calling procedures. (For example, "do myproc(#count + 1)".)

Allow recursive procedures. Make it possible to declare a procedure as RECURSIVE. This would cause its parameters and local variables to be stored on a stack.

Allow procedures as parameters. It would be nice to be able to pass the name of a procedure which does comparisons to a procedure which does sorts, as an example.

Better file support.

Add a SEEK command to go to a particular record in fixed-length files.
Allow fixed-length files to be updated in-place.
Allow ISAM/VSAM files to be opened.
Allow INSERT and DELETE record operations in ISAM files.
Add a parameter to the OPEN command to prevent the automatic stripping of trailing spaces from each line.

Add a sleep(n) function to LET, or a SLEEP statement.

Add a cpu_time() function to LET which returns the number of milliseconds CPU time the SQR program has consumed since starting.

PRINT-CHART Enhancements. Note: some of these features may be available in the "NewGraphics" charts starting in SQR v6.0.

I'd like to be able to more fully control the scaling and layout of charts. I should be able to specify the size of the font in the title, legend, axis labels, etc. Too often, the automatic scaling of these gets in the way of producing a sufficiently pleasing chart.
Multiple axis lables in PRINT-CHART. I should be able to plot two or more Y-axes (say, one on the left and one on the right), each with its own scale.
Full support for PRINT-CHART under Windows. Allow -printer:ps, -printer:hp, etc. on Windows for reports that contain PRINT-CHART output.

Allow PCL bitmaps for HP output. Currently, only HPGL/2 images are allowed for HP output. PCL would be useful.

Provide a BMP to HPGL converter for HP images. HPGL/2 images seem hard to produce exactly the way SQR wants them.

More GRAPHIC functions. GRAPHIC [DIAG-]LINE, GRAPHIC CIRCLE, GRAPHIC ARC, GRAPHIC ELIPSE, etc.

Allow bar-codes to be oriented in at least 4 directions: left-to-right (normal), right-to-left (upside down), vertically going up, and vertically going down.

Support for more printers. It would be provide more options to enhance developer productivity if I could use -printer:HP to print to DeskJets and other PCL printers besides LaserJets. Also, It wouild be great if users or printer vendors could write their own SPF-to-printer converters. In other words, it would be great if the SPF format were open, or at least documented.

Allow DECLARE-PRINTER TYPE=WP. All the other supported printer output types can have their own DECLARE-PRINTER paragraphs to set font size, etc. for a given report. Since WP output can't have a DECLARE-PRINTER, one is forced to use run-time commands to get the correct settings for WP output.

Provide a mechanism for separately compiled modules and dynamic libraries. It would be nice if we didn't have to recompile procedures that are used in many programs.

Better support for Informix. Allow a flag on the begin-select paragraph which would cause the cursor to be held open across commits. This would be especially helpful when using a select to drive inserts or updates with periodic commits (SQRIBE enhancement request #1641). Also, allow mulitple values to be to be able to say: EXECUTE procname (vars) INTO &var1, &var2, &var3.
Fixed

Multiple Database Connections. I'd like to be able to simultaneously have two or more connections to a database, each with its own commit and read consistency context. This would be extremely helpful for doing live data conversions or data replications. This would be useful even if for the same database engine (Oracle to Oracle, for example). This fuctionality is available in SQR for DDO (or SQR for ODBC on Windows platforms).

Multiple Heterogenous Database Connections. This would allow data to be integrated from multiple database types in a single program. Even if I can't "join" the data in a single SQL statement, it would be very useful to be able to combine the data from multiple data sources in a single program. I can do some of this with ODBC on a PC, but I'd like to be able to do this in a production, server-side report or data interface program. This fuctionality is available in SQR for DDO (or SQR for ODBC on Windows platforms).

PDF Output. I'd really like to be able to say "-printer:PDF" and have SQR produce a .PDF file output file. Adobe's Portable Document File (PDF) format is widely used, provides viewers for a variety of operating systems, and supports just about anything that you'd want to be able to display in a report. This functionality is available starting with SQR v4.3.2, using the "-printer:PD" command line option..

CSV output. I'd like to be able to say -printer:csv, or have a utility SPF2CSV which produces a comma-separated, variable-length file that can be readily imported into a spreadsheet program. Often a user would like to be able to take the report and drop it into a spreadsheet. It would be great if I could do this without having to modify the program, even if it produced a less-than-ideal .csv file. This functionality is available starting with SQR v4.3.1, using the "-EH_CSV" command-line option..

Add new LET functions:replace(s,t,u) This was added in SQR v6.0.

Allow DECLARE-PRINTER TYPE=PD. All the other supported printer output types can have their own DECLARE-PRINTER paragraphs to set font size, etc. for a given report. Since PDF output can't have a DECLARE-PRINTER, one is forced to use run-time commands to get the correct settings for PDF output. This was added in SQR v6.1. You can also use the "DEFAULT-PD" printer name.

Provide a CONTINUE for WHILE which causes execution to continue at the next loop iteration (skipping the rest of the body). This was added in SQR v8.

Multiple Output type support for PRINT-IMAGE. Currently PRINT-IMAGE supports a single TYPE= option, which must match the printer type currently being generated for this report. If it does not match, a grey box or a box with a slash through it is printed. Instead, PRINT-IMAGE should support a series of TYPE/SOURCE pairs, and choose the correct file to include based on the report output type. (This mapping should occur when the final output is beging generated, i.e. by SQRP.) This was added in SQR v8.

Additional functionality related to ARRAYs.

Allow array sizes to be dynamic at runtime. This was added in SQR v8.

Acknowledgments
Many thanks to people who have contributed to this list:
Jarrod Blomberg, Tony DeLia, David Donnelly, Adam Dray, Eric Eastman, Kathi Hardy, Joan Hudson, Don Mellen, Mike Palmer, Lee Parmenter, Nathan Stratton Treadway, Tobias Teschner
Did we leave anything out that you think is a needed enhancement to SQR? Give us your feedback.

Last updated: Tuesday, February 27, 2007