SQR Library

SQR is a programming language specifically designed for SQL database reporting and information processing. It includes a unique blend of procedural and structured programming constructs (if, while, begin-procedure, etc.) and features to facilitate report writing (on-break, next-column, next-listing, new-report).

SQR runs on a variety of operating platforms (MS-Windows, many flavors of Unix, VMS, MVS, VM, etc.) and SQL databases (Oracle, Sybase, DB2, Informix, SQLBase, etc.). For this reason, it is a popular tool among database application vendors (such as PeopleSoft) as a way of allowing their applications to be implemented in a variety of technical environments.

SQR is developed and marketed by Oracle.

SQR was originally marketed by SQRIBE Technologies until that company was acquired by Brio in August 1999. Then Hyperion acquired Brio in late 2003, and Oracle acquired Hyperion in April 2007.

Included below are links to documents, utility programs, libraries, procedures, and even links to other resources. Feel free to use these on your projects. Suggestions welcomed. Let us know what you find to be useful.

Documents

• SQR Programmer Reference Card, v11.1.1 Don Mellen, Editor, Ray Ontko & Co.
• SQR Programmer Reference Card, v9.x Don Mellen, Editor, Ray Ontko & Co.
• SQR Programmer Reference Card, v8.x Don Mellen, Editor, Ray Ontko & Co.
• SQR Programmer Reference Card, v6.x Don Mellen, Editor, Ray Ontko & Co.
• SQR Programmer Reference Card, v5.x Don Mellen, Editor, Ray Ontko & Co.
• SQR Programmer Reference Card, v4.x Don Mellen, Editor, Ray Ontko & Co.
• SQR Programmer Reference Card, v3.x Don Mellen, Editor, Ray Ontko & Co.
• SQR Programmer Reference Card, v2.x Don Mellen, Editor, Ray Ontko & Co.
• SQR Coding Standards and Naming Conventions Ray Ontko, Editor, Ray Ontko & Co.
• Ray's Top n SQR Features Ray Ontko, Editor, Ray Ontko & Co.
• Ray's Top n Uses for SQR Ray Ontko, Editor, Ray Ontko & Co.
• Ray's Top n Suggested Enhancements for SQR Ray Ontko, Editor, Ray Ontko & Co.

Libraries

• hp.sqh
  hp printer support routine library. Contributed by Ray Ontko, Ray Ontko & Co.
• csv.sqh
  CSV file procedure library. Contributed by Ray Ontko, Ray Ontko & Co.
• sylk.sqc
  SYLK file procedure library. Contributed by James Womeldorf, Fastenal Company.
• cgi-lib.sqh
  Library that allows SQR to be called from a webserver using the Common Gateway Interface (CGI). Contributed by Eric Dimick Eastman, Ray Ontko & Co.
• config.sqh
  Config file procedure library. Contributed by Eric Dimick Eastman, Ray Ontko & Co.

Utilities

• bmp_to_hpg.sqr
  SQR program to convert BitMaP (.bmp) image files to HP/GL (.hpg) files suitable for use with SQR. This allows you to use DECLARE-IMAGE and PRINT-IMAGE when you specify -printer:hp on the sqr command line.
  • Sample input .bmp file: test.bmp.
  • Sample output .hpg file: test.hpg.
  • Sample program: hpgl.sqr.
  Contributed by Ray Ontko, Ray Ontko & Co.
• lis_to_spf.sqr
  SQR program to convert files in mainframe format to text/SPF/etc. format (e.g. in order to publish them using Brio Portal (formerly ReportMart)). As it converts, it can perform bursting based on text found at a particular location on each page. Bursting can cause multiple files to be generated and/or (with SQR 4.1 and above) a Table-Of-Contents entry to be generated in the output SPF file. (It can run under SQR3 with somewhat reduced functionality.) Contributed by Nathan Stratton Treadway, Ray Ontko & Co.
• ora_doc.sqr
  a report program which produces documentation about an ORACLE schema. It lists columns, constraints, indexes and comments for all tables owned by a user. Contributed by Ray Ontko, Ray Ontko & Co.
• sqrsh.sqr
  sqr shell program that does everything you ever wanted (autodoc, trace, profile, pretty, libr, etc). Contributed by Ray Ontko, Ray Ontko & Co.

Editor Config Files

• Ultra-Edit syntax file for SQR
  A syntax file for Ultra-Edit updated to version 6.1.4 of SQR. The groupings have been re-arranged and named in a manner that made more sense to the author. Contributed by Don Mellen, Ray Ontko & Co.
• VIM syntax file for SQR
  A syntax file for VIM, based on the Ultra-Edit syntax file above. Contributed by Nathan Stratton Treadway, Ray Ontko & Co.

Examples

These are tiny, trival programs that illustrate something non-obvious about the SQR language.

• Recursion
  Sometimes, especially if the data is organized in a hierarchical or tree structure, it makes sense to have a procedure call itself. This is known as a recursive call. SQR, unlike most languages, does not have a call stack. So, in order for the recursion to work as expected we have to emulate one in the SQR code itself. It gets even a bit more complicated if we need "begin-select" sections in the recursive procedure. If SQR encounters the "begin-select" line again before the previous cursor is closed it will just fetch the next row. Below are two examples, each with a correct program and one that you might think would work at first glance.
  • Fibonacci Sequence: Each number is the sum of the two previous numbers. So, the sequence starts: 1,1,2,3,5,8,13,21,34 . . . fib_right.sqr fib_wrong.sqr
  • Family Tree: This example descends a family tree starting with an ancestor. recurse_right.sqr recurse_wrong.sqr
• Vertical Printing
  SQR doesn't directly support rotating text or other elements, but some of its output formats do. Here are a couple examples using PostScript. We have successfully used GhostScript to generate PDF from the PostScript. rotate_text.sqr   rotate_text.pdf   |   rotate_barcode.sqr   rotate_barcode.pdf

Procedures

• print_center.sqr centers proportionally spaced text on a position for printer=ps. Contributed by Mike Hill, Copyright Clearance Center, Inc.

Links

• Books
  • SQR Programmer Reference by Don Mellen.
  • SQR in PeopleSoft and Other Applications by Galina and Vlad Landres.

• Mailing Lists
  • sqr-users SQR-Users list at sqrug.org
  • sqr-jobs SQR job seekers' and recruiters' list
  • rm-users ReportMart-Users list at sqrug.org (for the product now called "Brio Portal")
  • peoplesoft-fans at YahooGroups.com
  • MAXIMO at YahooGroups.com
  • MaximoAtlanta at YahooGroups.com
  • a1a-peoplesoftjobs at YahooGroups.com (moderated)
  • a1a-psjobs at YahooGroups.com (moderated)
  • Brio Users Group for help with Brio's solutions
  • Brio Reporting Tool List a discussion list for users of the Brio (Intelligence) reporting tool

• User Groups
  • SQRUG SQR User Group
  • IOUG International Oracle User Group
  • Brio Users a Brio Intelligence user group
  • Brio Enterprise NT Club a user club for Brio Intelligence NT users
  • SLERP, a PeopleSoft- and SAP-oriented site with a number of forums.

• SQR Programmer Resources
  • SQRUG Library
  • SQR Tools Page
  • SqrPlus by SRI Technologies
  • SQR Tools VS by Textile Computer Systems.
  • RJ's SQRW Developer by RJ Solutions.
  • SQR Runner by CON+DEV Consulting und Development GmbH
  • SQR Express, by Business Computing Solutions LLC
  • HP PCL escape sequences (Link contributed by David Resh)

• Applications
  • CSS HRizon, from ADP
  • Maximo, from MRO Software (formerly named PSDI)
  • PeopleSoft

• Databases
  • Informix (now owned by IBM)
  • Ingres, from Computer Associates
  • Oracle
  • Red Brick (now owned by IBM)
  • Sybase

• File Viewers
  • PCL Viewer, by SoftCase
  • Ghostview, PostScript and PDF viewer software
  • Adobe Acrobat Reader, PDF viewer software
  • Excel 97/2000 Viewer, by Microsoft, for viewing CSV output.

• Source Code Management
  • CCC/Harvest, from Computer Associates (who purchased CCC/Harvest's prior owner, Platinum).
  • MKS Source Integrity, from Mortice Kern Systems, Inc.
  • PVCS, from Merant (formerly named Intersolv).
  • Microsoft Visual Source Safe, from Microsoft.
  • Mainsoft Visual Source Safe for Unix, from Mainsoft.
  • Perforce.
  For more complete information, visit this FAQ, this list of tools, this web index, this web directory, or the comp.software.config-mgmt newsgroup. (Links contributed by John S. Fetzik)

• Report Repository Tools
  • Brio Portal (formerly ReportMart), from Brio Software (formerly Brio Technology, who merged ReportMart's owner, SQRIBE Technologies).
  • Report2Web (or alternate site), from Redwood Software (who merged with the company named Report2Web).
  • products, from Mobius (makers of InfoPac).

Page maintainer: Ray Ontko (rayo@ontko.com)