8 Special And Great SQR Features

There are many features of SQR that set it apart from other programming languages.

1. Easy, In-Line Integration With SQL

The begin-execute, begin-select, and begin-sql commands support any SQL statement, using SQL syntax.  The execute (for a stored procedure in Sybase and Microsoft SQL Server databases), commit, and rollback commands perform their SQL equivalents.  There is no need for subroutine calls or special data objects.  There are special built-in variables ($sqr-database, #sql-count, $sql-error, #sql-status) that supply information about the database and the most recently executed SQL statement.

2. Platform Independence

SQR runs on DB2, Essbase, Informix, Ingres, Oracle, SQLBase, SQL Server, and Sybase databases, and ODBC datasources.  It runs on Windows, VM, VMS, MVS, and UNIX operating systems.

3. International Savoir-Faire

SQR travels well and offers tools for localization.  The alter-locale command will tailor the way SQR inputs and outputs dates, numbers, currency, and even the names of months and weekdays.  The alter-printer command will change character sets.

4. Load-Lookup

This command is a unique little surprise gift from the SQR designers.  We could have managed without it – there are other ways to perform its functions – but it can be so convenient.  It reads the database and creates a lookup table in memory.  I’m going to write about it in several future posts so for now I’ll just say, it’s great.

5. Date Support

This one sneaks up on you.  I used to think that SQR had minimal support for dates.  Then I began to notice that SQR does everything I need.  Three functions and two commands convert between dates and character strings.  The dateadd() function adds a quantity of time to a date.  The datediff() function subtracts one date from another and returns a quantity of time.

The edit() and datetostr() functions, and the move and print commands supposedly just format dates as strings, but actually extract any of 22 facts about a date: the month’s name, abbreviation, number, or roman numeral; the day of the week as a name, abbreviation, or number; the day of the month or the year or since 4713 BC; the week of the month or the year, etc.

6. Event Driven Functions

When something portentous happens, SQR can automatically execute some code on the side, before continuing with the program flow.  It’s like having a conditional subroutine call before and after every line of our program.

When a page of a report is complete, the code within begin-heading and begin-footing commands will execute.  When the database signals an error in an SQL statement, the procedure named in the on-error parameter will execute.  When the value of a database column or a print variable changes, the procedure named in the on-break parameter will execute.  When a report is about to begin or end, or a page in a report is about to begin or end, the procedure named in a declare-procedure command will execute.

7. Extensive Input And Output Options

When running SQR from the command line or the Windows desktop, the user can input from the keyboard during program compilation or execution.  Alternately, that input can come from the SQR command itself, either in the command or in a text file referenced by the command.  SQR can input data from database tables or record-oriented sequential disk files.  It can input environment variables or disk file information (whether it exists) from the operating system through in-line functions (see the documentation of the let command).

That’s just the appetizer for SQR output.  It can output text to a scrolling text window and its log file.  Have you tried the ANSI-escape-string based special effects, including color?  Or the 8-bit-ASCII character based graphics?  It can output error messages to a separate file.  It can output data to database tables and disk files.  Have you tried writing a command file and then executing it with the call system command?

It can print to one or multiple report files.  It can append its output to a pre-existing report file.  It can display the report on the terminal as it prints.  Without changing the program itself, at the time of program execution, SQR can generate the report as CSV (comma separated values; good for input to spreadsheets and desktop databases), HTML (for browsers), PDF (for Adobe Acrobat), PCL (Hewlett Packard’s printer language), Postscript (Adobe’s printer language), or unformatted text.

8. Powerful Printing

Of course SQR has page headings and page footings.  Of course it has commands to support columnar reports.  Of course it has mail merge.  It draws lines and boxes.  It prints color and image files.  It generates tables of contents.

It has breakpoint functions that react when the output value changes.  SQR can vary the printing of the value; every time, just when it changes, at the top of the page, or not at all.  We can specify the spacing between groups of data.  We can execute routines before or after the new value prints.  We can access the previous value of the variable that changed.

It has page numbering … and a “page x of y” function where saves the entire document through the last page, then goes back to put the number of pages on each page.  We don’t have to program it.  We don’t have to buffer it.  It’s just one short command to change the entire behavior of the program.

It has a function for word wrap and it also has reverse word wrap for right-to-left languages like Hebrew.  It prints fifteen types of bar-codes.  It prints twelve types of data charts.

Request For Information

Dear Reader (or Readers, if there are two of you), can you help me improve this post?

  1. What other features of SQR are special and great?
  2. Are there any features on my list that aren’t quite so special (maybe they’re more widespread than I thought) or aren’t so great?
  3. Can you correct or supplement any of the details in my SQR history?  I would welcome more specific dates, more information about the companies involved, names of the people who made SQR what it is today (and how to contact them for interviews), what versions of SQR (i.e. standard, DDO, multibyte characters, different platforms, different release numbers) were released when, etc.

Looking Ahead

Next week, we will discuss how to parse a filename in SQR.  Please do not parse any filenames until then unless it is absolutely necessary.

Brain Teaser

Meanwhile, here is a brain teaser.  Please post solutions as comments.

The NAME column contains peoples’ names, and they should all be in the form of “Smith, John A. Jr.”  However, some of them are in alternate forms “John A. Smith Jr.” and “Smith, Jr., John A.”  After reading a character string into &NAME, what is a program snippet that identifies the values with too few commas or too many commas?

7 Comments

  1. Bob Josephson says:

    Great start to the blog! Do you take requests for article ideas? I’d love to see a posting on using Peoplesoft/SQR as a front end for legacy systems like the Honeywell 200.

    • administrator says:

      The Honeywell 200 was a great computer from the 1960s. Bob “Josephson” (my brother, and our dad’s name was Joseph) and I both worked on it as part of a high school work-study program. I programmed it in Fortran, I think Bob programmed it in Cobol. That was the better choice, since the Honeywell’s machine language was optimized for business applications.

    • John says:

      Great cheat sheet you have put together here. Sometimes it is more hepfull to new SQL users to see an explanation in this format than reading the books online pages. Well done.

  2. Kevin says:

    if length(&name) – length(translate(&name, ‘,’, ”)) <> 1
    ! the name is bad…

    • administrator says:

      That’s good code, and I’ll be surprised if anybody can do it in a more straightforward way. To expand the challenge, can somebody show us how to route the “too few commas” and “too many commas” to different branches? Or how to use the unstring command?

      • little says:

        The Name “Smith Jr., John A. ” is right?

        • administrator says:

          The puzzle was to write the best code to count commas in a string. The rules for formatting names may be set differently in each organization. Consistency makes it easier to write programs to parse the names and makes the name sorts more predictable.