
VISUALPQL		Introduction

Introduction

VisualPQL (Visual Procedural Query Language) is a structured programming and application development language which allows you to develop complete applications. You have full control of your application logic together with numerous high-level, non-procedural features and special constructs for accessing data in a SIR2002 relational database.

The source of a VisualPQL program is a set of commands which are typically either a member in the procedure file or a text file. Use a text editor in SIR2002 to create and modify programs.

Main Routines

A program has a single main routine which may optionally reference subroutines. A main routine can begin with a PROGRAM command. A main routine that accesses any database begins with a RETRIEVAL command. The main routine ends with an END PROGRAM or END RETRIEVAL command. For example, a simple program might be:

PROGRAM
WRITE 'Hello World'
END PROGRAM

In addition to programs and subroutines, VisualPQL provides a system for the creation and maintenance of data entry screens known as PQLForms. A PQLForms main routine begins with a FORM command and ends with an END FORM command.

Main routines can be re-compiled each time they are run, or can be compiled and saved as an executable member with an :E suffix. A program may use input parameters which are specified at run time.

Subroutines

A subroutine is an independent routine which is executed from the main routine or from another subroutine. Subroutines begin with the SUBROUTINE command and end with the END SUBROUTINE command.

Subroutines must be pre-complied before they are referenced in an executing program. When a subroutine is compiled, it creates a member with an :O suffix.

A PQLForm can be saved as a subroutine.

External Variable Blocks

An external variable block is a block of variables used by several routines. An external variable block begins with the EXTERNAL VARIABLE BLOCK command and ends with the END EXTERNAL VARIABLE BLOCK command. External variable blocks must be precompiled before they are referenced in a compilation or execution of a program. When an external variable block is compiled, it creates a member with a :V suffix.

The five commands, PROGRAM, RETRIEVAL, FORM, SUBROUTINE and EXTERNAL VARIABLE BLOCK begin a routine. The corresponding END commands end the routine. All other VisualPQL commands must be included in one of these routines.

Compiling and executing

To compile and execute the program from the menu system, select RUN from the Member or File dialogs.

When a program is run, it executes, creates any files or other outputs and displays any messages or interactive output in the scrolled output window. When the run is complete, the next command is read from the input source. If there are no more commands, control is returned to the user.

Options on the RETRIEVAL, PROGRAM and FORM commands determine whether routines are compiled, saved or executed.

Running a program with no special options on the initial command, compiles it and then executes it. The NOEXECUTE option compiles without executing. The SAVE option, together with the name of a member with an a :E suffix, saves the executable version. Specify the REPLACE option to allow an existing member of the same name to be overwritten.

VisualPQL Procedures

Main Program and Retrieval routines may use one or more VisualPQL Procedures. The program creates the data for the procedure with the PERFORM PROCS command. The procedure specifications determine how the data is then output. Multiple procedures can be included in a single program so that one pass of the database produces multiple outputs.

Some procedures create output text files, others create files in specific formats that are directly useable by other software packages. All procedures, except the Full Report procedure, are single commands with option keywords.

The Procedure Table

The Procedure Table is the internal table that is built as the program processes the data and contains a set of data records. Each record in the table is made up of the procedure variables and contains a value for each variable. The procedure variables, are either those specified with the DEFINE PROCEDURE VARIABLES command or are the default, which is all the program variables of the main routine excluding arrays. Every time the PERFORM PROCS command is issued, a set of values is copied into the procedure table. Only variables available in the main routine may be included in the Procedure Table.

Within most procedures, it is possible to specify a list of variables on the procedure definition itself. If this is not done , the procedure operates on the default variables.

VisualPQL Syntax

The syntax rules for VisualPQL are:

Begin each new command in the first position on a line. There is no special character for the end of a command.
To continue a command on the next line, leave the first position blank and begin the continuation text in any other position. Commands can be continued for as many lines as required. There is no special character at the end of the line to be continued. You may split lines on blanks or special characters except where the command itself contains blanks e.g. PROCESS CASES; - do not split these command words across lines. Do not split commands, keywords, names or strings in quotes across physical lines. Otherwise the components of a command may be split as necessary. For example:
```
WRITE ID
      NAME
      SALARY
```
To indent commands for readability, specify a period (.) in the first position of a line, followed by any number of spaces and then the command. For example:
```
PROCESS CASES
. PROCESS REC EMPLOYEE
.   WRITE ID NAME BIRTHDAY SALARY
. END REC
END CASE
```
If you wish to specify more than one command on a single, physical line, use a semi-colon (;) to separate the commands. Use of multiple commands on a line is not recommended since it makes the reading and modification of source more difficult.
Any text on a line following a vertical bar ( | ) is treated as comments. Vertical bar comments are not continued to the next line. The COMMENT command specifies that the whole line is a comment.
Commands and keywords cannot be abbreviated although there are synonyms for some commands. For example, PROCESS REC is a synonym for PROCESS RECORD; C is a synonym for COMMENT.
A new command that does not begin with a valid command name or synonym, is taken to be an implicit COMPUTE. Be careful when relying on implicit compute statements and avoid using names for variables which conflict with commands. Even names which do not conflict now may conflict in subsequent releases which would necessitate amending the program. It is always safer to specify the command word COMPUTE. Any variable name can be used in conjunction with the command; there are no specific reserved words. The following two statements are identical.
```
COMPUTE TOTAL = 10 + 15
TOTAL = 10 + 15
```

Names

Variables, database record types and various other entities have names. Names must begin with a letter and may contain letters, numbers and the special characters $, #, @, and _. Names may contain no more than eight characters and are translated to upper case so upper and lower case text are treated identically for all commands, names and keywords. If a command syntax requires a name, simply write it using upper or lower case:

COMPUTE A = B

To specify a string constant, use either single or double quotes. There is no conversion of upper or lower case in string constants.

Tabfiles, tables, buffers, sub-procedures, database secondary indexes and PQLForms screens may have names with up to 32 characters with no spaces. The first character must be alphabetic. Characters can be letters, digits or special characters ($, #, @, _). Names are translated to upper case so uppercase and lowercase letters are equal. If you wish to use a name which does not conform to these rules, the name must be enclosed in quotes using correct upper and lower case letters as necessary.

CLEAR BUFFER 'Previous_Command_Buffer'
SCREEN RECORD 'Employee Details' /COMPANY.EMPLOYEE

Some commands allow string expressions where normally a name is required. (This means that the name is not known until the program is run and thus is not allowed everywhere since many commands need to know the value during compilation.) The syntax of each command specifies if this is allowed. Enclose the expression in square brackets [] to specify that an expression is being used to derive the name. e.g.

EXECUTE SUBROUTINE [string expression]....

Variables

Variables may be defined explicitly by command or implicitly by use. There are five types of simple local variables :

DATE Date variables are four byte integers, the value of which is a number of days since the beginning of the Gregorian calendar. (October 15, 1582 is day 1). The date format defines the default input and output format. The date format may contain the letters M, D, Y and I representing Months, Days, Years and Ignore.

INTEGER Integer variables are 1, 2 or 4 byte integers. 4 bytes is the default. The value ranges are :
INTEGER*1 -128 to 123;
INTEGER*2 -32,768 to 32,763;
INTEGER*4 -2,147,483,648 to 2,147,483,643

REAL Real variables are floating point numbers allowing a fractional component. REAL*4 (single precision) and REAL*8 (double precision) are allowed. Double precision is the default.

STRING String variables are strings of a specified length from 1 to 4094. If more characters than the declared string length are assigned to a variable, the string is truncated to the declared length.

TIME Time variables are four byte integers, the value of which is a number of seconds since midnight. The time format defines the default input and output format. Input times are in 24 hour time notation. The time format may contain the letters H, M, S and I, representing Hours, Minutes, Seconds and Ignore.
To define a variable explicitly, specify the variable type followed by a list of variable names. For example:

INTEGER*4  month1 month2 month3
STRING*40  name1 surname
REAL*8     tot1 to tot9
DATE       birthday ('DDIMMIYYYY')
TIME       minutes  ('MM')

To define a variable implicitly, assign a value to an undefined name. This creates the variable. Implicit numeric variables are REAL*8. Implicit string variables are a default length, normally 20 characters.

Dates and Times

If dates or times are assigned to another variable, the definition of that variable determines the value received. If the variable is numeric, it receives the numeric value; if a string, it receives the formatted date or time string. If the receiving variable is undefined, a numeric variable is implicitly created.

Missing Values

Variables may contain Missing values. A variable has a missing value if it is undefined or allocated a value defined to be a missing value. If any variable in a computation contains missing values, then the result is missing values. (Other than those functions which specifically test the presence of missing values.)

Declaring and using Arrays

Arrays can be defined. Each array is of the basic INTEGER, REAL, STRING, DATE or TIME types and has one or more dimensions. Specify the number of variables in each dimension. There is no limit to the number of dimensions nor the number of variables in any dimensions (other than memory or other machine limitations). An array must be explicitly declared by a command. For example:

INTEGER*4 ARRAY monthtot (12)
STRING*10 ARRAY sname (8)
REAL*8    ARRAY sum tsum(10,20)
DATE      ARRAY fdays (12) ('DDDD')
TIME      ARRAY minutes (24,60) ('MM')

Array dimensions normally start at 1 and proceed for the number of entries specified. An alternative start dimension can be specified where more natural or convenient using a 'from:to' syntax e.g.

INTEGER*4 ARRAY years (1900:2099)

This specifies an array with 200 entries which will be referenced by values from 1900 thru to 2099.

Array dimensions can be redefined 'on the fly' with the REDEFINE ARRAY command. This allows you to grow, shrink or redimension any array programmatically.

Array entries can be sorted with the SORT command.

Array Element Reference

In general, a subscripted array element can be used wherever an equivalent simple variable can be specified. A subscripted array element consists of the array name and the element locations for each dimension in parentheses. The subscript may be a constant or a numeric expressions. For example:

COMPUTE MONTHTOT(12) = TOTAL
COMPUTE TOTAL = MONTHTOT(MONTH)
COMPUTE JAN01 = DAILYTOT(1,1)

The SET and PRESET commands can operate on whole arrays or on specific elements. For example:

SET MONTHTOT *   (0)            |  whole array
SET MONTHTOT (1) (0)            |  specific element

Control Flow

Program logic (the way in which commands are executed) is determined by how data matches specified logical conditions. Complex conditions can be specified by using connectors such as AND or OR. For example:

IFTHEN (A EQ B)
WHILE  ((A EQ B) AND (C NE D))
IF     (NOT E LT F)

Block Structures

VisualPQL is primarily a block structured language. That is, the execution of a complete block of commands depends on the results of conditions. The various block structures are specified by a command which starts the block and an END command which ends the block. For example LOOP/END LOOP, IFTHEN/END IF.

Blocks may be nested inside other blocks. A block must be completely inside another block. Overlapping blocks are not allowed.

Special commands in blocks

EXIT blocktype

An EXIT command stops execution of the block at that point and transfers control to the first command following the end of the block. An EXIT can be used in any block. A blocktype is normally specified on the EXIT command and this exits the innermost block of that type. An EXIT without a blocktype exits the innermost block.

NEXT blocktype

Many blocks are looping structures. That is, the commands within the block are executed repeatedly until some controlling condition is met. Commands such as WHILE iterate while a specific condition is true. Commands such as PROCESS REC retrieve a new record on each loop until the end of that set of records.

In looping blocks, the NEXT command transfers control to the first command in the block at the next iteration. A blocktype can be specified on the NEXT command and this transfers control to the innermost block of that type. A NEXT without a blocktype transfers control to the innermost looping block.

For example:

RETRIEVAL
PROCESS CASES ALL
. PROCESS RECORD EMPLOYEE
.  IF (GENDER NE 1) NEXT RECORD
.  GET VARS ALL
.  PERFORM PROCS
. END PROCESS RECORD
END PROCESS CASE
REPORT ......
END RETRIEVAL

IF and IFNOT

IF and IFNOT are conditional commands which are not block structured. When true, these commands execute command(s) which are specified as continuations of the IF, IFNOT command itself. The next new command (i.e. command starting in column 1) finishes the condition. If specifying multiple commands, separate each by a semi-colon (;).

Most commands can be specified with the IF command except:

other IF,IFNOT commands. (Use the block structured IFTHEN if you need to nest conditions.)
data definition commands
block definition commands
compiler directives

For example:

PROCESS CASES
. PROCESS RECORD EMPLOYEE
.   IF (GENDER EQ 1 ) WRITE NAME
. END PROCESS RECORD
END PROCESS CASES

File I/O

A program can READ and WRITE files.

Files can be opened and closed with the OPEN and CLOSE commands respectively. If a file is not opened or closed explicitly, the first occurrence of a READ or WRITE opens the file with default settings, and reaching the end of the program closes the file.

Binary Files

Normally files read or written by explicit reads and writes in VisualPQL are text files; that is they contain readable characters together with special end of record characters and can be viewed with a text editor. VisualPQL can also read and write binary files, that is files in internal non-text formats. Every file can be read as a binary file and the program is able to process the data exactly as it is on the file if the format is known. For example, a VisualPQL program could copy an image file or an executable or a library.

Format Specifications

The READ command reads input from the file and assigns values read from the input to program variables. READ formats input data according to an input specification which is a list containing variable names and their formats. The formats can be fixed-field, free-field and can contain positional parameters.

READ is not a block control statement and simply executes without looping. In order to read through a complete file, it is necessary to enclose the READ in a looping block, typically a WHILE block which tests an I/O return code and finishes when the end of file is reached.

The WRITE command writes output formatted according to an output specification which is a list containing variable names and their formats. The formats can be fixed-field, free-field, or pictures, and can contain positional parameters. If an output format is not specified, defaults are used.

Typical input/output specifications might be:

write (test.out) value1(f5.2) 2x code(A2) ',' value2(i*)
read (test.out, iostat=status) input1(f5.4) 2x input2(i*) input3(i*)

Database Access

Begin a program that accesses the database with the RETRIEVAL command. By default, this opens the database for read access only. Specify the UPDATE option on the RETRIEVAL command to open the database for write access.

Multiple Database Access

The VisualPQL commands PQL CONNECT DATABASE and PQL DISCONNECT DATABASE connect and disconnect databases and set the default. A VisualPQL retrieval can reference more than one database. A retrieval can access a specified database with a DATABASE IS which starts a block of commands. Inside this block, all references are to variables in the new database. Any standard commands can be used in this block. When the block is exited, the original database is made current.

Case Blocks

If the database is a Case Structured database, each case in the database has a Common Information Record, which is referred to as the CIR. The CIR contains the common variables including the case identifier which uniquely identifies each case.

Specify one of the Case Processing commands to access cases. A case processing command defines a block of commands, a Case Block. The block is terminated with an END CASE command. Within a case block, other commands may get values from or put values into common variables. As a case block is executed, a CIR is read into memory and other commands within the block use this. When the case block is exited or when a new CIR is called for, the record is replaced in the database if it has been modified and is overwritten with the new data. Each time a case is accessed with one of these commands, the CIR is available to other commands within the block.

Process cases using either the PROCESS CASES command which reads cases serially through the database or the CASE IS command which reads a specific case if it exists and can creates a new case if it does not already exist. Use the NEW CASE IS and OLD CASE IS constructs to control processing depending on whether a case exists or not. NEW CASE IS creates a new case if one does not exist and skips the block if the case already exists. OLD CASE IS reads a specific case and skips the block if the case does not exist.

If a retrieval is run on a case structured database without a case processing command, an automatic PROCESS CASES ALL is generated.

Record Blocks

Databases contain Record Types. Specify one of the Record Processing commands to access records. On case structured databases, record processing must be nested within a case block unless the record is accessed using a secondary index. A record processing command begins a Record Block. The END RECORD command ends a record block. Within a record block, other commands may get values from or put values into the variables in that record. As a record block is executed, a record is read into memory and other commands within the block use this. When the record block is exited or when a new record is read, the record is replaced in the database (if it has been modified) and is purged from memory.

Process records either using the PROCESS RECORD command which reads and selects records serially through a single case (on a case structured database), through the whole database or through a secondary index or using the RECORD IS command which reads a specific record if it exists and can create a record if it does not already exist. Use the NEW RECORD IS and OLD RECORD IS constructs to control processing depending on whether a record exists or not. NEW RECORD IS creates a new record if one does not exist and skips the block if the record already exists. OLD RECORD IS reads a specific record and skips the block if the record does not exist.

The record processing commands specify a record type and may specify a particular record or subset of records to retrieve. If there are no matching records, then the block of commands is skipped.

In the following example, the WRITE is not executed if there is no record type 2 for an employee and thus that employee does not appear in the output:

RETRIEVAL
PROCESS CASES ALL
OLD RECORD IS EMPLOYEE
. GET VARS ALL
. PROCESS RECORD 2
.  GET VARS ALL
.  WRITE ID NAME CURRPOS STARTSAL
. END PROCESS RECORD
END RECORD IS
END PROCESS CASE
END RETRIEVAL

Table Access

A Table is analogous to a database record type and a Row is analogous to a record. These offer an alternative storage mechanism. Tables are stored on Tabfiles. Tables may be accessed from within either programs or retrievals. Multiple tables on multiple tabfiles may be accessed in a single program.

Table processing differs slightly from record processing as follows:

Tables are maintained in creation sequence rather than in a key sequence.
The only commands which deal directly with variables in a table are GET VARS and PUT VARS. This means that when retrieving a row of a table, the values of the variables must be moved into local variables with GET VARS. To update the values of variables in a table row, the local variables are moved into the table row with a PUT VARS.
The names of the variables, indexes and tables may be up to 32 characters.
The PROCESS ROWS and ROW IS are analogous to the record commands and there are also the OLD ROW IS and NEW ROW IS constructs. Each of these commands defines a block of commands, a row block, that is terminated with END ROW.
Tabfiles must be connected prior to the compilation of the program or subroutine either through the menu or the CONNECT TABFILE command. Tabfiles accessed during execution of a program or subroutine must be connected. The PQL CONNECT TABFILE may be used to connect tabfiles during execution.

ODBC

Open DataBase Connectivity is a Windows based standard to allow communication between software from different vendors. Queries are done using SQL syntax. VisualPQL can set up ODBC connections, perform SQL queries, retrieve information on the results of the query and then retrieve the data.

SIR2002 allows other packages to access SIR2002 data through the SirSQLServer and VisualPQL can query this as any other ODBC source. VisualPQL can also query the SirSQLServer in a more direct fashion eliminating some of the ODBC overheads or allowing VisualPQL clients to operate on non-Windows platforms. Communication between client and server is machine-independent so allowing communication between any of the SIR2002 supported architectures providing these are networked using tcp/ip.

Graphical User Interface

When SIR2002 starts, it invokes a main VisualPQL program which defines a main window and menu system. This program receives control when the user selects a lowest level menu item. It can deal directly with the requested function, call sub-routines, use sub-procedures or any VisualPQL construct and can call other VisualPQL programs and SIR2002 functions. The program can enable, disable, check or uncheck menu items as necessary.

The complete source code for the user interface is supplied with the system and the menus and dialogs can be used as examples for application development. The menu program can be modified or a customised version created which is then run when the system is started.

Once the system is running, any VisualPQL program can output information into the main window (such as title and status) and put text in the window using the normal WRITE command. Text output is scrolled and a line can be up to 4000 characters wide. Programs can also save, print or clear the main window.

VisualPQL programs can display and get information through dialogs. There are commands and functions to define a dialog and to interact with the user through the dialog.

There are commands which directly pop-up boxes to which the user must respond, for example to display an error message or to ask for an OK or Cancel response. There are also commands to display a file browse box appropriate to the operating system when opening or saving files and commands to print files displaying a print box to alter print specifications as necessary.

The Dialog Painter helps create new VisualPQL dialogs. This gives a developer an interactive means of creating dialogs and of generating appropriate message processing blocks.

PQLForms

PQLForms is an extension to VisualPQL that creates all the necessary logic for sets of linked, interactive dialogs for data entry, retrieval and update. A complete set of dialogs is a single VisualPQL routine known as a Form.

A Form can be created and maintained completely through the Forms Painter and this is the recommended way to develop forms.

There are additional commands that are only valid within a PQLForm. These define what variables are on each dialog, how they are displayed and edited, how the dialog is to look, and how dialogs are linked together. A PQLForm has built in buttons and associated logic to allow the user to navigate through a set of records and to display, edit and insert data according to the database description. A developer can use all standard VisualPQL commands as necessary and these are executed at appropriate places in the form.

A PQLform is run in the same way as any other VisualPQL routine either directly or from a menu.

Once a form has been developed, it can be used by many people for data entry or for querying data.

Execution Window

VisualPQL supports a text style, fixed window 30 rows by 80 columns known as the Execution Window that provides compatibility for old applications. This should not be used for new development as support for this feature will be discontinued at some point. A program can use the execution window as a full screen and display at given positions on it. There are various commands to display text and data on the execution window and running a program which includes any of these commands means that the execution window is displayed. If multiple programs are run without control returning to the system menus, the execution window remains in place once invoked.

Editor

A program can invoke an editor for the user to enter text. Once the editor is invoked, control does not return to the program until the user exits the editor. The editor can use buffers to store data and there are VisualPQL commands to create, read and manipulate the contents of a buffer. This allows the use of buffers to enter and edit unlimited amounts of text which are edited and then stored in the database with minimal programming. The user can set the editor to use and can use a standard editor or a SIR2002 internal editor (either a gui style editor or an execution window based editor).

Functions

Functions return a single numeric or string result derived from the arguments of the function. In general, the functions can appear in any string, arithmetic or logical expressions in a program. There are various types of functions such as Trigonometric, Mathematical, Date and Time, etc. For example, the function CAPITAL (string) capitalises the first alphabetic character of the string and the first alphabetic character following a blank. All other characters remain unedited.

PROGRAM
STRING * 50 NAME
NAME = 'this  is  the  first  day  of  the  week'
NAME = CAPITAL(NAME)
WRITE NAME
END PROGRAM

The first character of every word in the string variable NAME is capitalised producing the following output:

This  Is  The  First  Day  Of  The  Week

As another example, FORMAT (X) converts a number to a string in free-field format. The following gives the string '1.3':

XST = FORMAT(1.3)

There are a set of "across-records/rows" functions which compute statistics for a number of records or rows and are treated in a special way. They may only appear in PROCESS REC or PROCESS ROW blocks. They use the values of a variable during the processing of a PROCESS REC or PROCESS ROW loop and produce a single value such as a total or an average. They ignore values that are missing or undefined.

Source Commands

SIR2002 has a number of features which can assist when developing VisualPQL programs. These include features to:

Document programs with comments;
Include sets of code from various sources;
Substitute Global variables;
Generate code to compile;
Specify conditional compilation rules for sets of code.

DATE	Date variables are four byte integers, the value of which is a number of days since the beginning of the Gregorian calendar. (October 15, 1582 is day 1). The date format defines the default input and output format. The date format may contain the letters M, D, Y and I representing Months, Days, Years and Ignore.
INTEGER	Integer variables are 1, 2 or 4 byte integers. 4 bytes is the default. The value ranges are : INTEGER1 -128 to 123; INTEGER2 -32,768 to 32,763; INTEGER*4 -2,147,483,648 to 2,147,483,643
REAL	Real variables are floating point numbers allowing a fractional component. `REAL4` (single precision) and `REAL8` (double precision) are allowed. Double precision is the default.
STRING	String variables are strings of a specified length from 1 to 4094. If more characters than the declared string length are assigned to a variable, the string is truncated to the declared length.
TIME	Time variables are four byte integers, the value of which is a number of seconds since midnight. The time format defines the default input and output format. Input times are in 24 hour time notation. The time format may contain the letters H, M, S and I, representing Hours, Minutes, Seconds and Ignore.