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.
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?
- What other features of SQR are special and great?
- 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?
- 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.
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.
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?