
Getting Started		VisualPQL

VisualPQL

VisualPQL is a structured programming and application development language. It is a high level language with a large range of features including:

Programs and Subroutines
Variables and arrays
Logical Block Structures
Access to files
Special constructs for accessing data in a SIR2002 relational database
Special constructs for accessing data in tabfiles
Building dialogs and menus for use in a graphical user environment
Building applications to access data a record at a time in a graphical user environment
A wide range (300+) of built in functions
Built in Procedures which create output in various formats such as reports, cross-tabulations or output files for other software packages such as SPSS. A VisualPQL program can include one or more procedures, and can collect and select the required data from the database or from other sources and then use the procedures to produce sorted output, cross tabulations or other analysis.

VisualPQL is the major application development tool within SIR2002 and is extremely powerful. VisualPQL can be used for numerous tasks from simply analysing some data to building a complex, menu driven application.

A first program

The only way to learn a new language is to write programs in it. The first program in all languages is the same. Print the words: Hello World

In some languages, this is a big hurdle. However with SIR2002, it is easy and straightforward. The VisualPQL program to display "Hello World" is:

PROGRAM
WRITE 'Hello World'
END PROGRAM

All you have to do is to create this text and keep it and you can do this either as a:

Member - The database has a set of facilities for keeping and managing text, typically programs but sometimes instructions and other documentation. This is known as the Procedure file. When the database is exported or unloaded, procedures are kept with the database so this is a permanent place for text associated with a database.
File - You can keep the source of your programs as standard operating system files which can be edited.

Assuming that you have the COMPANY database attached, you might put your test programs in a new family of procedures called, say, TEST

You use an editor to create the text. The system allows you to use any editor of your choice and also has two internal editors. Use whatever program you would normally use to edit text and that you are comfortable using.

To check which editor is being used, go to the Settings - Preferences dialog. This has a check box for external editor and the name of your selected external editor. If the box is not checked, the name is greyed out and the system uses the the selected internal editor. If you do not have access to an external text editor, use the SIR GUI editor. If you select an external editor, the system saves this from session to session. When using the external editor option ensure that the path and the name are correct otherwise trying to edit a program will not work.

Steps to run a first program

Start SIR2002. Attach the COMPANY database.
Select the Program menu.
Select Members.
Possibly add a new family for your test programs.
Select new and give a member name, say PROG1.
Use the editor to type in your program. Exit the editor and save the program.
Position to the new member and select 'Run'. The program is compiled and then executes. It displays the messages
```
Start program compilation
Start program execution
Hello World
End program execution
```

Congratulations. You have created and run that vital first program!

N.B. If you do not want to type this program in, it is supplied as member EXAMPLE.HELLO.

VisualPQL Syntax

See VisualPQL syntax for a full description. Briefly the rules for VisualPQL are:

Each new command begins in the first text position of a line. For example, each line in the following program is a new command:
```
RETRIEVAL
PROCESS CASES
PROCESS REC EMPLOYEE
WRITE ID NAME
BIRTHDAY SALARY
END REC
END CASE
END RETRIEVAL
```
Leave the first position blank to continue the previous command. Put a period ( . ) in position one to specify that this is a new command but that the command is indented for easier reading.
```
RETRIEVAL
. PROCESS CASES
.  PROCESS REC EMPLOYEE
.  WRITE ID NAME BIRTHDAY SALARY
.  END REC
. END CASE
END RETRIEVAL
```
Generally, commands and keywords cannot be abbreviated but some commands have synonyms. For example, PROCESS REC is a synonym for PROCESS RECORD.
Comments can be put at the end of a line. Put a vertical bar ( | ) character after the command, before the comment.
There is also a COMMENT command (synonym C or CC). This specifies that the whole line is a comment.
Upper and lower case letters are treated identically for all commands, names, keywords, etc. The only place where upper and lower case makes a difference is in character strings (in quotes).
A VisualPQL routine begins with a command such as PROGRAM and ends with a matching END command. The commands to begin a routine are:
- PROGRAM - any main routine
- RETRIEVAL - a main routine that accesses a database
- FORM - a main routine that creates screens to do record at a time browsing
- SUBROUTINE - an independent routine that is executed from other routines

Example VisualPQL programs

The mechanics of creating and running the programs are going to be taken as understood from now on. Before doing too much typing in, try saving and restoring data from members or files and you should be comfortable with the mechanics of using an editor.

The following program uses a formula to calculate Fahrenheit and Centigrade temperature equivalents. The program in its simplest form is:

PROGRAM
FOR FAHR = 0,300,20
COMPUTE CELSIUS = 5/9 * (FAHR - 32)
WRITE FAHR CELSIUS
END FOR
END PROGRAM

VisualPQL is a block structured language and the block used here is a FOR block. This assigns an initial value to a variable and then performs the commands in the block a number of times, incrementing the initial variable each time until the final value is reached. In this case, these values are expressed as constants with a start of 0, an end of 300 and an increment of 20. The end of the block is indicated by the END FOR command.

Because neither the FAHR nor CELSIUS variables were explicitly declared, they are implicitly declared. In the context they were used they had to be numeric.

Try running the temperature program (supplied as EXAMPLES.TEMP1) and look at the results. There is a default output format for all variables which may not be precisely what was wanted, but allows a very quick result to be produced. There are no comments in the program, nor any indentation to indicate which commands are in the loop and which are not. A slightly improved version of the program (EXAMPLES.TEMP2) might be:

PROGRAM
C** This program computes Fahrenheit and Celsius equivalents
C** between 0 and 300 fahrenheit
WRITE 'FAHRENHEIT CELSIUS'
FOR FAHR = 0,300,20
. COMPUTE CELSIUS = 5/9 * (FAHR - 32)
. WRITE FAHR(f6.2) 12T CELSIUS(f6.2)
END FOR
END PROGRAM

Note the use of lines starting with a period for indentation. All commands start in column one and periods are used to indent for readability. Indentation and the use of one command per line is recommended. The C in column one indicates a comment. C** is often used conventionally.

The WRITE can specify output formats (enclosed in parentheses after the variable) to align the output. The (f6.2) format indicates a floating point format 6 characters long with 2 decimal places. The 12T specification specifies that the next field starts in position 12. Two WRITE statements have been used, one to do a brief heading and one to output each line of results. A WRITE automatically writes a new line.

The following version (EXAMPLES.TEMP3) of the temperature conversion program uses variables to control the FOR loop and uses the SET command to initialise these. It uses the SIMPLE REPORT procedure and writes to a file rather than to the screen.

PROGRAM
C** This program computes fahrenheit and celsius equivalents
C** between a lower limit, and an upper limit in intervals
INTEGER*2 lower upper interval
SET lower,upper,interval(0,300,20)
FOR FAHR = lower,upper,interval
. CELSIUS = 5/9 * (FAHR - 32)
. PERFORM PROCS
END FOR
REPORT FILENAME = TEMP.LIS /
       PRINT  = FAHR CELSIUS /
       NOTOTALS
END PROGRAM

More VisualPQL

Logical Conditions

Some commands are executed depending on particular logical conditions. Specify logical conditions in brackets. You can construct compound conditions using AND, OR, etc.

Because conditional tests are frequently required for very simple computations or actions, there is an IF command which is not block structured; it simply determines whether or not the command is executed. The action to be taken is expressed as a continuation of the IF command itself. For example:

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

Database Access

Begin a program that accesses the database with the RETRIEVAL command which opens the default database (if more than one database is connected, this is the last database connected or it can be set explicitly).

If the retrieval needs to create, modify or delete data on database records, specify the UPDATE option on the RETRIEVAL command. This opens the database for write access. Retrievals without the UPDATE option can get data from the database but cannot add, delete or modify data.

A very simple program which accesses the database might be as follows. This program lists the name of all employees:

RETRIEVAL
PROCESS CASES
. PROCESS RECORD EMPLOYEE
.  WRITE NAME
. END PROCESS RECORD
END PROCESS CASES
END RETRIEVAL

Things to note about this program:

The initial command is RETRIEVAL (as opposed to PROGRAM) indicating that it uses the database.
The PROCESS RECORD / END PROCESS RECORD is a block structure and the block of commands within the PROCESS REC block is executed for each EMPLOYEE record that is retrieved.
NAME is a string variable on the EMPLOYEE record.
The Company database is a case structured database. This means that all of the data records for a single employee are grouped together. One employee is one case. This is frequently a good way of organising data.
The PROCESS CASE / END PROCESS CASE block processes every case. If you leave this block out, the program will still work but will give you a warning message.

Accessing Data from the Database

During execution, a retrieval accesses data with one of the Case or Record commands. These processing commands define a block of commands. Within a block, other commands may get values from or put values into the database. The processing commands are:

PROCESS CASE | PROCESS RECORD

Retrieves a set of cases or records depending on specified key values.

CASE IS | RECORD IS

Either retrieves or creates the specified case or record. The complete key must be specified.

NEW CASE IS | NEW RECORD IS
Is a variant which creates a new case or record if one does not already exist.

OLD CASE IS | OLD RECORD IS
Is a variant which retrieves an existing single case or record.

These blocks are terminated with the END CASE/RECORD command. On case structured databases, record blocks are nested within a case block.

The PROCESS commands are a looping structure and retrieve all matching occurrences of data. The "IS" commands access a single occurrence.

The record processing commands specify a record type and may specify a particular record or subset of records to retrieve. This is done by specifying values which are matched to the record keys or keys in a secondary index.

For example, the OCCUP record has a key of POSITION. To get the OCCUP record which corresponds to the current position value:

RETRIEVAL
PROCESS CASES ALL
OLD RECORD IS EMPLOYEE
. GET VARS currpos
. OLD REC IS OCCUP (currpos)
.   GET VARS ALL
.   WRITE ID NAME CURRPOS STARTSAL ...
. END RECORD IS
END RECORD IS
END RETRIEVAL

If there are no matching records, then the block of commands is skipped completely. In the previous example, the WRITE is not executed if there is no matching OCCUP record for an employee and thus that employee does not appear in the output. Selection criteria may be specified as constants or variables.

On a PROCESS CASE command there are options to select counts, samples or lists of cases. On a PROCESS RECORD command, there are a number of ways of specifying records to be selected. These include selecting records with keys from a specified value, to a specified value and in a given range. Records can have multiple key fields and the selection may be applied to a subset of keys. In a case structured database, the case id is implicitly used as the first key on record keys (not on secondary indexes). For example to select a sample of employees and find an average of their starting salaries in position grades 4 through 10:

RETRIEVAL
PROCESS CASES SAMPLE = .25
. PROCESS RECORD  OCCUP FROM (4) THRU (10)
.   COMPUTE AVGSAL = MEANR (STARTSAL);
            STDD   = STDEVR (STARTSAL)
. END PROCESS RECORD
END PROCESS CASE
WRITE 'Average Salary = ' AVGSAL '  Std. Dev = ' STDD
END RETRIEVAL

To retrieve all employees by name assuming a secondary index called NAME_INDEX has been defined on name:

RETRIEVAL
PROCESS RECORD EMPLOYEE INDEXED BY NAME_INDEX
WRITE NAME 25T ID
END PROCESS REC
END RETRIEVAL