VisualPQL

Home	Starting	Environment	DBMS	VisualPQL	Procedures	SQL	Forms	Host/API	Index

VisualPQL

Introduction

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

The source of a VisualPQL program is a set of commands that is typically either a member (with a :T suffix) in the procedure file or a text file. Use a text editor in SIR/XS to create and modify programs.

Main Routines

A program has a single main routine that 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 that are specified at run time.

Subroutines

A subroutine is an independent routine that 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 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 :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 default procedure variables are all the program variables of the main routine excluding arrays. An alternate set of procedure variables can be specified with the DEFINE PROCEDURE VARIABLES command. Only variables available in the main routine can be included in the Procedure Table. Every time the PERFORM PROCS command is issued, a set of values is copied into the procedure table.

It is possible to specify a list of variables on the procedure definition itself. If this is not done, the procedure operates on all the variables in the procedure table.

VisualPQL Syntax

The syntax rules for VisualPQL are:

Begin each new command in the first position on a line. There is no special character that indicates 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 that indicates the command is being continued on the next line. You may split lines on blanks except where the command itself contains blanks e.g. PROCESS CASES - do not split 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 that conflict with commands. Even names that do not conflict now may conflict in subsequent releases so it is always safer to specify the command word COMPUTE if a program is going to continue to be run on a recurrent basis. 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

There are various types of entities in SIR/XS such as databases, records, variables, etc., each of which must have a name. Standard names do not begin with a number and may contain letters, numbers and the four characters $, #, @, and _. Standard names contain up to 32 characters and are translated to upper case.

You can also use non-standard names by enclosing the name in curly brackets ({}). A non-standard name can contain up to 30 characters and may use any character including blanks; no translations are performed on non-standard names.

When specifying commands, keywords and standard names, upper and lower case text are treated identically. For example the following two lines are identical:

COMPUTE A = B

compute a = b

The following preserves the lower case a for a name:

COMPUTE {a} = B

In an executing program, names are most frequently for variables. For example the expression:

COMPUTE A = B

This means take the contents of variable B and make these the contents of variable A.

When referring to other entities in a command, it may not always be as obvious. For example:

CLEAR BUFFER BUFNAME

The name BUFNAME could either be the name of a buffer or the name of a variable in the program that holds the name of the buffer. In fact, in the buffer manipulation commands, the name is a variable name or string expression not directly the buffer name. However, just as a command might be:

COMPUTE myname = 'Fred'

So a very simple string expression can be used to specify a buffer name e.g.

CLEAR BUFFER 'Previous Command'

Where a command uses expressions rather than directly naming an entity, it means that the name is not known until the program is run and, since many commands need to know names during compilation, this is not allowed everywhere. The syntax of each command specifies if this is allowed.

Some commands that normally require a name specified directly may also allow expressions where you have to enclose the expression in square brackets [] so that the compiler can recognise that an expression is being used to derive the name. Again the syntax of each command specifies if this is allowed.

e.g.

EXECUTE SUBROUTINE { member_name | mem_name_exp_in_brackets } ....

So the following are identical:

EXECUTE SUBROUTINE OPENF
COMPUTE SUBNAME = 'OPENF'
EXECUTE SUBROUTINE [SUBNAME]

In particular, the WRITE command allows a list of variables to be written but expressions can be used by specifying them in square brackets, which can be very convenient and avoids the need for new intermediate variable names e.g.

WRITE [capital(name)]

Note
Be careful if using non-standard names in commands that allow either a variable name or a string in quotes as a name specification. If specifying a non-standard name in quotes, do not specify the curly brackets e.g.
CLEAR BUFFER 'Previous Command' Not
CLEAR BUFFER '{Previous Command}'
If you specify CLEAR BUFFER {Previous Command}, this looks for a local variable called Previous Command which is expected to contain the name of the buffer.
Similarly, be careful when manipulating non-standard names in a program. If your program is passing names to the software as strings at execution time, then it must pass the name without the curly brackets.
Also note that if a program gets back non-standard names from functions, they are not wrapped in curly brackets. If you are constructing commands or other processing where you would need curly brackets around any non-standard name, use the STDNAME function to do this.

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 a date integer is the number of days since the beginning of the Gregorian calendar. October 15, 1582 is day 1. The date format defines the input and output format. See date formats for a complete description.

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 a time variable is the number of seconds since midnight. The time format defines the input and output format. See time formats for a complete description.
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 that is normally 32 characters but this can be altered with the

STRING LENGTH command.

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 that specifically test the presence of missing values.)

Declaring and using Arrays

Arrays can be defined. Each array is named and is one of the basic INTEGER, REAL, STRING, DATE or TIME variable types and has one or more dimensions. Array names cannot be the same as any of the VisualPQL function names. 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 that is 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 expression. 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 sequence 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 that starts the block and an END command that 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.

Control 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 that are not block structured. When true, these commands execute command(s) that 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; 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 contain readable characters together with 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. Any 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 that 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 that 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 that 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 that 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, that is referred to as the CIR. The CIR contains the common variables including the case identifier that 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 that reads cases serially through the database or the CASE IS command that reads a specific case if it exists and can create 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 that 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 that 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 that 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 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.

SIR/XS allows other packages to access SIR/XS 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 SIR/XS supported architectures providing these are networked using tcp/ip.

Graphical User Interface

When SIR/XS starts, it invokes a main VisualPQL program that 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 SIR/XS 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. You can modify the main menu program or create a customised version and run that when you start the system.

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

The Dialog Painter helps create 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.

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 with minimal programming. The user can choose to use a familiar standard editor or the SIR/XS internal editor (a simple GUI style text dialog).

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 that compute statistics for a number of records or rows that 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

SIR/XS has a number of features that 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 a date integer is the number of days since the beginning of the Gregorian calendar. October 15, 1582 is day 1. The date format defines the input and output format. See date formats for a complete description.
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 a time variable is the number of seconds since midnight. The time format defines the input and output format. See time formats for a complete description.