Ray Ontko & Co.
Library
Services | Products | Library | Careers News | About Us | Contact Us | Search | Site Map

SQR Programmer Reference Card

SQR language version 2.x

© 1998, Ray Ontko & Co.

Don Mellen, Editor. Please direct comments, suggestions, and corrections to donm@ontko.com.

This document may be freely copied and distributed provided it is not modified in any way and it retains the original copyright notice.

Latest version of our on-line card.


TABLE OF CONTENTS


CONVENTIONS USED IN THIS DOCUMENT

     
  []   Optional parameters.
  {}   Required parameters.
  |   Separates a list of "either/or" options.
  ()   Notes or additional comments.
  (Item)+x.x   Denotes items that were added in version "x.x".
  (Item)*   Denotes items that are becoming obsolete (but still backward compatible).
  (Item)-x.x   Denotes items that were removed in version "x.x".

Items can be commands (in which case the version information applies to the entire command), or command options (in which case the version information applies only to that option within that command).

All information comes from a base of version 2.0, i.e. Any command or command option without version designation has existed since 2.0.

When listing the acceptable values for parameters with pre-defined values, the default value is listed first.


SQR COMMAND LINE

SQR [Report] [Connectivity] [Flags...] [Args...] [@File]
-Flags..
  -A Causes the report output to be appended to an existing file of the same name.
-Bn Indicates how many rows to buffer during each retrieve of data from the database.  (Oracle)
-Dn Causes output to be displayed to the terminal as well as written to a file. Pauses after n lines.  (non-Windows)
-DB{database} Causes the program to use database. Overrides embedded SQR "USE" command.  (SYBASE)
-DEBUG[x] Causes 'DEBUG' to be defined. "#DEBUG" or "#ifdef DEBUG" lines will be compiled. The x allows multiple debugs. DEBUGAB defines DEBUG, DEBUGA, and DEBUGB.
-E[file] Causes error messages to be recorded in the named or default file.
-F{file} Will override the default output file name (Report).LIS and use the named file.  (Path can also be specified. If only a path is specified, the default-named report will be placed there. The "new-report" command ignores all -F settings.)+2.26
-G{file_attr}+2.26 Enables specification of report output file attributes.  (VMS)
-M{file} Points to a startup file containing program limits that override the defaults.
-N{database}* Uses the named database, rather than the default database.  (Rdb)
-RS Scans, syntax checks, and saves report and "ASK" responses in a 'run time' file called (Report).SQT.
-RT Immediately processes the 'run time' file and "ASK" responses saved with the -RS flag.
-S Causes the status of all cursors to be displayed at the end of the report run.
-T{n} For testing - causes 'order by' clauses to be ignored to save time during testing. Tests for n pages.
-V{server} Uses the named server.  (Sybase and SQL Server)
-XC+2.20 Suppresses final commit.  (callable SQR)
-XL prevents SQR from logging on to the database.

Args...
Arguments are used by the SQR ASK and INPUT commands while report runs.

@File
Files contain report arguments, one argument per line.

SPECIAL CHARACTERS

(denoting data type)
& Begins a "bind" variable for a database column or expression, regardless of type.
$ Begins a text variable.
# Begins a numeric variable. (Floating point).
@ Begins a marker location variable, used for identifying position fields inside BEGIN-DOCUMENT paragraphs.
' (Single Quote). Surrounds a text literal. For example, 'Ray Ontko & Co.'.
Double to include a quote inside a literal. For example, '''Yes!!'', he said.'. NOTE: The exclamation mark must also be doubled or the rest of the line will be considered a comment.
{} Encloses a substitution variable.
[] Encloses a dynamic SQL variable, dynamic query variable, or dynamic table name.

Variable names may be of any length and are case insensitive. Variable names are known globally throughout a report, except if used in a local procedure, in which case they are local. To reference a global variable from within a local procedure, put an underscore between the special character and the variable name.


PRE-DEFINED VARIABLES

#current-column The current column on the page.
#current-line Refers to the physical line number on the page.
#end-file Set to one (1) if end of file occurs on READ, otherwise zero (0).
#page-count The current page number.
#return-status Value to be returned to the operating system when SQR exits.
#sql-count The count of rows affected by a DML statement (insert, update and delete).
$sql-error The text message from the database explaining the most recent error.
#sql-status The status value from the database after each query is compiled or executed.
$username The database username specified on the command line.

POSITION SYNTAX

(a,b,c) where

Line position and column position values may be fixed, or relative.

Examples:

       print &name (10,15,30)
Will be printed on line 10, column 15.

       print &address (+1,15,30)
Will be printed directly below the name.


DATA TYPES

NUMERIC
Double precision floating point numeric. Range and precision of these are machine dependent.
CHAR
String. Length is limited by the memory of your computer.
The "let", "move", "get" and "put" commands will auto-convert between data types.  They will auto-convert string <-> numeric.


COMMAND REFERENCE

KEY:
Text Numeric
$t Variable #n Variable
&t Column &n Column
£t Literal £n Literal
~t Variable or Literal  ~n Variable or Literal 
t Var, Lit, or Col  n Var, Lit, or Col 
 
v Any Variable value Any Var, Lit, or Col 
NOTES: In some cases, the only significant portion of a numeric is its truncated integer value. An "i" is used to designate these cases. "Round" parameter ranges can be from 0 to 6.
ADD  n TO #n [ROUND= £i]
ARRAY-ADD  n... TO array_name(i_element) [field[(i_occurs)]...]
ARRAY-DIVIDE  n... INTO array_name(i_element) [field[(i_occurs)]...]
ARRAY-MULTIPLY  n... TIMES array_name(i_element) [field[(i_occurs)]...]
ARRAY-SUBTRACT  n... FROM array_name(i_element) [field[(i_occurs)]...]
ASK  substitution_variable [£t_prompt]
BEGIN-DOCUMENT  position
BEGIN-FOOTING  £i_footing_lines
BEGIN-HEADING  £i_heading_lines
BEGIN-PROCEDURE  procedure_name [LOCAL | (arg1,arg2,...argN)]
BEGIN-REPORT *
BEGIN-SELECT
[DISTINCT]
[-C£n_buffer_size]   (Ingres,Oracle,SQLBase)
[-B£n]   (Oracle,SYBASE CT-Lib)
[LOOPS= £n]
[ON-ERROR=procedure]
BEGIN-SETUP
(The following are used exclusively in the SETUP section, ASK, DECLARE-CHART, DECLARE-IMAGE, DECLARE-LAYOUT, DECLARE-PRINTER, DECLARE-PROCEDURE, DECLARE-REPORT, NO-FORMFEED, PAGE-SIZE, PRINTER-DEINIT, PRINTER-INIT, USE)
BEGIN-SQL
[-C£n_buffer_size]   (Ingres,Oracle,SQLBase)
(in setup)  [ ON-ERROR=[STOP | WARN | SKIP] ]
(non-setup)  [ ON-ERROR=procedure]
BREAK
CALL  subroutine USING value {$t | #n} [param]
CALL  SYSTEM USING t_command #n_status
CLOSE  {~i_file_num}
COMMIT
(used with ORACLE, SQLBase, RDB , Ingress)
CONCAT  value WITH $t [{:$t_edit_mask | £t_edit_mask}]
CONNECT  t  [ON-ERROR=procedure]
CREATE-ARRAY  NAME=array_name  SIZE= £i_elements
{FIELD=
name:{CHAR | NUMBER}[:i_occurs]}
[FIELD...]
DATE-TIME * position [date_format [&t]]
DECLARE PRINTER +2.20, *
[TYPE= {LINEPRINTER | POSTSCRIPT | HPLASERJET}]
[ORIENTATION= {PORTRAIT | LANDSCAPE}]
[LEFT-MARGIN= £n_inches]
[TOP-MARGIN= £n_inches]
[LINE-SIZE= £n_points]
[CHAR-SIZE= £n_points]
[LINES-INCH= £i_lines]
[CHARS-INCH= £n_characters]
[POINT-SIZE= £n_points]
[FONT= £i_font_number]
[FONT-STYLE= {PROPORTIONAL | FIXED}]
[SYMBOL-SET= £t_from_HP_defined_sets]
[STARTUP-FILE= £t_file_name]
[BEFORE-BOLD= £t_any_string]
[AFTER-BOLD= £t_any-string]
DECLARE PROCEDURE +2.20, *
[BEFORE-REPORT= procedure_name]
[AFTER-REPORT= procedure_name]
[BEFORE-PAGE= procedure_name]
[AFTER-PAGE= procedure_name]
DISPLAY
value [{:$t_edit_mask | £t_edit_mask}] [NOLINE]
DIVIDE  n INTO #n [ON-ERROR={HIGH | ZERO}] [ROUND= £i]
DO  procedure_name [(value_arg1, value_arg2,...value_argN)]
ELSE
ENCODE  £t_code_string INTO $t
END-DOCUMENT
END-EVALUATE
END-FOOTING
END-HEADING
END-IF
END-PROCEDURE
END-REPORT *
END-SELECT
END-SETUP
END-SQL
END-WHILE
EVALUATE  value
WHEN  {=!=<><><=>=}  value
[sqr code...]
[BREAK]
[WHEN  {=!=<><><=>=}  value] ...
[WHEN-OTHER]
END-EVALUATE
EXIT-SELECT
EXTRACT  $t FROM t ~i_start_location ~i_length
FIND  t IN t ~i_start_loc #n_found_at_offset
GET  v... FROM array_name(i_element) [field[(i_occurs)]...]
GOTO  label
GRAPHIC () FONT +2.20, *  ~i_font [~i_point_size [1 | 0 [~i_pitch]]]
GRAPHIC position BOX +2.20  ~i_depth [~i_rulewidth [~i_shading_percent]]
GRAPHIC position HORZ-LINE +2.20  [~i_rulewidth]
GRAPHIC position VERT-LINE +2.20  [~i_rulewidth]
IF  logical_expression
INPUT
{$t} [MAXLEN= £n] [£t_prompt] [TYPE={CHAR | NUMBER | INTEGER | DATE}] [STATUS= #n_status] [NOPROMPT]
LET  v = expression
LOAD-LOOKUP  
In the SETUP section
{NAME= lookup_table_name}
{TABLE= database_table}
{KEY= key_column_database_expression}
{RETURN_VALUE= return_column_database_expression}
{ROWS= £i_maximum_rows}
[WHERE= £t_where_clause]
[QUIET]
LOOKUP  lookup_table_name value $t_return_val
LOWERCASE  $t
MOVE  value TO v [{:$t_edit_mask | £t_edit_mask}]
MULTIPLY  n TIMES #n [ROUND= £i]
NEW-PAGE  [i_erase_from_line]
NEW-REPORT  ~t_report_file_name
NEXT-COLUMN  [AT-END={NEWLINE | NEWPAGE}] [GOTO-TOP= i] [ERASE-PAGE= i]
NEXT-LISTING  [NO-ADVANCE]+2.13 [SKIPLINES= i] [NEED= i]
NO-FORMFEED *
OPEN
{~t_file_name} AS {~i_file_num} {FOR-READING | FOR-WRITING} RECORD= £i_length[:{FIXED | VARY}] [STATUS= #n]
PAGE-NUMBER  position [£t_pre-text [£t_post_text] ]
PAGE-SIZE *  £i_page_depth £i_page_width
POSITION  position [@document_marker [COLUMNS i ...]]
PRINT  value position [format_commands]
PRINT-DIRECT +2.20
{t} ...
PRINTER-DEINIT +2.20, *  reset_string
PRINTER-INIT *  initialization_string
PUT  value... INTO array_name(i_element) [field[(i_occurs)]...]
READ  {~i_file_num} INTO v:£i_length...
ROLLBACK
(used with ORACLE, SQLBase, RDB , Ingress)
SHOW
[cursor_position]  [CLEAR-SCREEN | CS | CLEAR-LINE | CL] 
[value]  [EDIT {edit_mask}]
[BOLD]  [BLINK]  [UNDERLINE]  [REVERSE]  [NORMAL]  [BEEP]  [NOLINE] ...
STOP  [QUIET]+2.20
STRING  {t}... BY t_delim_txt INTO $t
SUBTRACT  n FROM #n [ROUND= £i]
UNSTRING  {t} BY t_delim_txt INTO $t...
UPPERCASE  $t
USE  database  (SYBASE and SQL Server only)
USE-COLUMN  i_column_number
WHILE  logical_expression
WRITE  {~i_file_num} FROM value[:£i_length]...


EXPRESSIONS

NOTE: Logical expressions can be numeric. 0 is considered false, and non-zero is considered true.

OPERATORS (In descending order of precedence)...

||
+    -    {sign prefixes}
^
*    /    %
+    -
<    >    >=   <=   <>   !=   =
not
and
or   xor
NUMERIC FUNCTIONS
ABS(n)   --> #n_positive
ACOS(n)   --> #n_radians
ASIN(n)   --> #n_radians
ATAN(n)   --> #n_radians
CEIL(n)   --> #i_smallest_greater_than_n
COS(n_radians)   --> #n
COSH(n_radians)   --> #n
DEG(n_radians)   --> #n_degrees
E10(n_power)   --> #n_10_raised_to_power
EXP(n_power)   --> #n_log_base_e_raised_to_power
FLOOR(n)   --> #i_largest_less_than_n
LOG(n)   --> #n_natural_log_of_n
LOG10(n)   --> #n_base10_log_of_n
MOD(nx, ny)   --> #n_remainder_of_x_div_y
POWER(nx, ny)   --> #n_x_raised_to_power_of_y
RAD(n_degrees)   --> #n_radians
ROUND(nx, ny)   --> #n_x_rounded_to_y_decimal_places
SIGN(n)   --> #i_0_pos1_neg1
SIN(n_radians)   --> #n
SINH(n_radians)   --> #n
SQRT(n)   --> #f_squre_root_of_n
TAN(n_radians)   --> #n
TANH(n_radians)   --> #n
TRUNC(nx, ny)   --> #n_x_truncated_to_y_decimal_places
STRING FUNCTIONS
ASCII(t_str)   --> #i_ascii_val_of_first_char_in_str
CHR(i_ascii_val)   --> $t_char_of ascii_val
COND(n_logical, value_a, value_b)   --> v_if_nlogical_then_valuea_else_valueb
EDIT(v_source, t_edit_mask)   --> $t_source_edited_under_mask
INSTR(t_source, t_str, i_start_atoffset)   --> #i_position_of_str_in_source_or_0
ISNULL(t_source)   --> #i_1_if_source_is_null_else_0
LENGTH(t_source)   --> #i_num_of_chars_in_source
LOWER(t_source)   --> $t_lower_cased_source
LPAD(t_source, i_len, t_str)   --> $t_source_padded_on_left_with_str_to_len
LTRIM(t_source, t_char_set)   -->
$t_source_with_all_leftmost_char_set_chars_removed
NVL(value_source, value_alt)   --> v_source_unless_null_then_alt
RANGE(value_source, value_low, value_high)   --> 
#i_1_if_source_is_within_low_to_high_(inclusive)_else_0
RPAD(t_source, i_len, t_str)   --> $t_source_padded_on_right_with_str_to_len
RTRIM(t_source, t_char_set)   -->
$t_source_with_all_rightmost_char_set_chars_removed
SUBSTR(t_source, i_start_pos, i_len)   -->
$t_len_chars_from_source_starting_at_pos
TO_CHAR(n_num)   --> $t_digits
TO_NUMBER(t_digits)   --> #f_num
TRANSLATE(t_source, t_orig, t_repl)   -->
$t_source_with_each_orig_char_replaced_with_respective_repl_char
UPPER(t_source)   --> $t_upper_cased_source

PRINT Format Commands

BOLD +2.20
BOX +2.20
CENTER
CODE +2.20, *
EDIT edit_mask
Text Mask Characters =
X | B | ~ | R[n]
Numeric Mask Characters =
9 | 0 | $ | B | E | . | ,
Numeric Mask Suffix Characters =
MI | PR | PS | PF | NA | NU
FILL
MATCH {match_text i_line i_column print_text} ...
NOP
ON-BREAK
[PRINT= {ALWAYS | CHANGE | CHANGE/TOP-PAGE | NEVER}]
[SKIPLINES= i]
[AFTER= procedure_name]
[BEFORE= procedure_name]
[SAVE= $t]  [LEVEL= £i]  [SET= £i]
[PROCEDURE= procedure_name (for compatibility)]
SHADE +2.20
UNDERLINE +2.20
WRAP
i_line_length  i_max_lines [KEEP-TOP]+2.13 [STRIP= strip_chars] [ON= break_chars] [R]


COMPILER DIRECTIVES

#DEBUG  SQR_command
#DEFINE  substitution_variable value
#ELSE
#END-IF
#IF substitution_variable {=!=<><><=>=} {£t | £n}
#IFDEF  substitution_variable
#IFNDEF  substitution_variable
#INCLUDE  file_name


TABLES

Common HP Symbol Sets+2.20

 CODE  Symbol Set  CODE  Symbol Set
0U ASCII 17U PC-852
10U PC-8 12U PC-850
8U HP Roman-8 7J DeskTop
19U Windows 3.1 Latin 1  0N ISO 8859/1 Latin 1
9E Windows 3.1 Latin 2 2N ISO 8859/2 Latin 2
5T Windows 3.1 Latin 5 5N ISO 8859/9 Latin 5
10J PS Text 12J MC Text
13J Ventura International 14J Ventura US
1U Legal 6J  Microsoft ® Publishing 
15U Pi-Font 6M Ventura Math
5M PS Math 8M Math-8
579L Wingdings Font 19M Symbol

 

Font Numbers+2.20

HP LASERJET FONTS
 No.  Typeface Style  No.  Typeface Style
 Line Printer Fixed 17   Optima Proportional 
 Pica Fixed 18   Garamondi Proportional
 Elite Fixed 19   Cooper Black Proportional
 Courier Fixed 20   Coronet Bold Proportional
 Helvetica Proportional  21   Broadway Proportional
 Times Roman Proportional 22   Bauer Bodini Black Condensed  Proportional
 Letter Gothic Fixed 23   Century Schoolbook Proportional
 Prestige Fixed 24   University Roman Proportional
11   Presentations  Fixed  

 

POSTSCRIPT FONTS
 No.  Typeface  Bold   No.  Typeface  Bold 
 CourierY 32   Times ItalicY
 HelveticaY 33   Avante Garde Demi 
 Times RomanY 34   Avante Garde Book Oblique 
 Avante Garde Book  35   Avante Garde Demi Oblique 
 Palatino RomanY 36   Palitino ObliqueY
11   Symbol  37   New Century Schoolbook Italic Y
12   Zapf Dingbats  38   Helvetica NarrowY
17   Zapf Chancery Medium Italic  39   Helvetica Narrow ObliqueY
18   Bookman Light  40   Bookman Demi 
23   New Century Schoolbook Roman Y 41   Bookman Light Italic 
30   Courier ObliqueY 42   Bookman Demi Italic 
31   Helvetica ObliqueY  

 

WINDOWS FONTS
  No.  Typeface Style   No.  Typeface Style
 Courier New Fixed 11   Symbol 
 Arial Proportional  300   Courier New Bold 
 Times New Roman  Proportional 400   Arial Bold
1  AvantGarde Proportional 500   Times New Roman  Bold
1  Palatino Proportional 1800   Palatino Bold
1Note: Fonts 6, 8, and 800 are in the ADOBE type Manager (ATM) and not included with Windows.

 

Default DATE-TIME Formats

 Database  Version Format
 ALL  +2.0, *   DD-Mon-YYYY HH:MI PM 

 

Input Status

 Value   Indicates
 0  Successful.
 1  Bad type (did not match TYPE parameter).
 2  Too long (longer than MAXLEN or INTEGER out of range). 

 

-M File Entries

Entry Default Max Size Is The Maximum ...
 BREAKS+2.26 100  64K-1  4  breaks per EVALUATE or IF command.
 DYNAMICARGS+2.26 70 32K-1  12   no. of dynamic SQL arguments.
 EXPRESSIONSPACE+2.26  2048 64K-1 1  byte length for LET temporary string storage.
 FORWARDREFS+2.26 200 32K-1 8  no. of &column forward references.
 NUMVARIABLES* 240 32K 8  no. of numeric variables and literals.
 ONBREAKS 30 64K-1 8  no. of ON-BREAK LEVEL= values per set.
 POSITIONS 900 64K-1 13  no. of position parameters.
 PROGLINEPARS 9000 64K-1 2  no. of arguments for all program lines. Generally 3 or 4 times the PROGLINES value.
 PROGLINES 2500 32K-1 8  no. of program lines (SQR commands).
 QUERIES 60 32K-1 60  no. of BEGIN-SQL and BEGIN-SELECT paragraphs. Database dependent and can vary.
 QUERYARGS 240 64K-1 6  no. of arguments (bind vars) for all SQL and SELECT statements. Value must be 1 more than number used.
 SQLSIZE 4000 64K-1 1  length of a SQL statement in characters
 STRINGSPACE 10000 64K-1 1  size in bytes of string space for program line arguments.
 SUBVARS+2.26 100 32K-1 8  no. of run-time substitution variables.
 VARIABLES 750 64K-1 14  no. of variables, literals, and database columns.
 WHENS+2.26 70 64K-1 4  no. of WHEN arguments per EVALUATE command.

 

Hardcoded Limits

Number Of... Limit
 Arrays (maximum) 20
 Array fields per array (maximum) 20
 Bytes for INPUT buffer 511
 Characters for SQR line (maximum)   255 
 Concurrent open cursors (maximum)  30
 Document markers (maximum) 120
 Document paragraphs (maximum) 20
 Dynamic SQL arguments (maximum)-2.26 70
 Forward references (maximum)-2.26 200
 OPEN files (maximum) 12
 Substitution variables (maximum)-2.26 100

 

Top
Copyright © 1999, Ray Ontko & Co. Last updated: Tuesday, February 27, 2007
Careers Library Products Services Site Map Search Contact Us About Us News