VisualPQL

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

VisualPQL

Database Access

There are VisualPQL commands that access and update data stored in a Database.

Any VisualPQL program that uses CASE or RECORD processing commands must begin with the RETRIEVAL command. This implicitly opens the current default database for access by the retrieval. By default, the database is opened for read, meaning that the retrieval can get data from the database but cannot add, delete or modify data. Retrievals that create, modify or delete database data must use the UPDATE option on the RETRIEVAL command.

By default, the last database connected is the default.

If a VisualPQL program references a database, it must be connected when the retrieval is compiled and must be connected when the retrieval is executed.

There are commands to connect and disconnect databases. A VisualPQL retrieval can access a specified database and then all references are to variables in that database.

A SIR/XS session can be started with an MST= parameter, in which case any database access by VisualPQL programs is through the concurrent MASTER process. A session can logon and logoff to Master as necessary. VisualPQL database access is either local or through Master depending on the current status of the master setting.

When operating in concurrent mode, locks on individual records may be specified. If a retrieval does not specify locks, defaults are used. If a retrieval specifies locks and does not run through master, any locking is ignored. An identical VisualPQL retrieval can run concurrently and independently. Even if there are processes accessing the database through MASTER, a retrieval can be run in a SIR/XS stand-alone session and use read only mode against the same database.

Data availability during retrieval

During the execution of a retrieval, data can be in local variables, case or common variables and record variables. The same name may be used for local, case and record variables and the actual variable referenced depends on the placement of the command and its scope.

Local Variables

Local variables include the variables and arrays declared explicitly or implicitly in the routine and any variables included with an INCLUDE EXTERNAL VARIABLE BLOCK.

Case variables

If the database is a case structured database, each case in the database has one CIR or Common Information Record. The CIR contains common variables including the Case Identifier variable that uniquely identifies each case. The common variables are defined when the database schema is created.

During execution, a retrieval accesses a CIR with one of the Case Processing commands. A case processing command defines a block of commands, a Case Block. The common variables are available at any point in the case block, including within record processing blocks. When a case block is executed the case variables are read and other commands within the block can use the common variables. When the case block is exited or when another case is read, if the CIR has been modified it is replaced in the database.

Record variables

Databases are made up of multiple Record Types. Each record type contains a set of variables defined during database schema definition. Some of these variables may be key fields that, in combination with the case identifier variable, uniquely identify an individual record. The structure of the record type cannot be altered through a retrieval.

During execution, the retrieval accesses records with one of the Record Processing commands. A record processing command defines a block of commands, a Record Block. Within a record block other commands may get values from or put values into the record variables. When a record block is executed the record variables are read and other commands within the block can use these variables. When the record block is exited or when a new record is read, if the record has been modified it is replaced in the database.

If a record block is nested within another record block, the variables for the outer record are restored when the inner block is exited.

Priority of Access to Data

At any given point during retrieval execution, a retrieval potentially has access to one set of common variables, one set of record variables and the local variables. It is possible, even likely, that a local variable has the same name as a common or record variable in the database. If the referenced variable exists both as a database variable and a local variable, VisualPQL uses the following rules to decide which variable to use:

If the reference is outside a case or record block, the local variable is used.
If the reference is within a case block, the case variable is used rather than a local variable of the same name.
If the reference is within a record block, the record variable is used rather than a local variable of the same name.
If the reference is within a record block and the CIR and the record both contain a variable of the same name, the record variable is used. If the variable is updated, both the record variable and the CIR variable are updated.

Skipping blocks

Commands specify a particular record or record type to retrieve. If there are no matching records, then the block of commands is skipped completely. When developing a retrieval, this must be taken into account. For example:

RETRIEVAL
PROCESS CASES ALL
OLD REC IS EMPLOYEE
. GET VARS ALL
. PROCESS REC 2
.   GET VARS ALL
.   WRITE ID NAME SALARY
. END PROCESS REC
END REC IS
END PROCESS CASE
END RETRIEVAL

The WRITE command is not executed if there are no record type 2 for an employee and thus that employee does not appear on the output. Any variables that are updated within the block, are not reset. The AUTOSET command can be used to reset variables in this instance.

PQL CONNECT DATABASE

PQL CONNECT DATABASE database_name_exp
[PREFIX prefix_exp]
[SECURITY exp,exp,exp]
[IOSTAT varname]

Connects the specified database at execution time. Sets this as the default database. Does not automatically run any SYSTEM procedures.

All the parameters, except the IOSTAT varname are expressions; enclose any name constants in quotes.

There is a table of connected databases, one of which may be the current default database. By default, the last database connected is the default and is the first database referenced by a RETRIEVAL.

If a VisualPQL retrieval references a database, it must be connected when the program is compiled and must be connected before it is executed. This means that the PQL CONNECT DATABASE command cannot be used to connect and compile or connect and execute within one VisualPQL process.

A database can also be connected with the SIR/XS command CONNECT DATABASE.

SECURITY Three expressions separated with commas. Specify the database password, then the read password then the write password.

IOSTAT A numeric variable that returns zero if the database is already connected, the database connection number a new database is connected or a negative number (in the range -2001 to -2058) if there is a problem with the connection. See error messages.

PQL DISCONNECT DATABASE

PQL DISCONNECT DATABASE database_name_exp
[IOSTAT varname]

Disconnects the named database. If PQL DISCONNECT is executed on the default database, the default is set to zero and SYSPROC is set as the procfile.

IOSTAT A numeric variable that returns a negative number (in the range -2001 to -2058) if there is a problem with the connection. See error messages.

DATABASE IS

DATABASE [IS] dbname [UPDATE|NOUPDATE]

Starts a block that accesses a specified database. May only be used in a RETRIEVAL.

Inside this block, all references are to variables in the new database. Any standard VisualPQL commands can be used in this block. (This is not a looping block so NEXT cannot be specified.)

Within a RETRIEVAL, the initial database is the default database.

Note the database name in this command is a constant e.g. DATABASE IS COMPANY not an expression as the name is required at compile time as well as during execution.

END DATABASE IS

END DATABASE [IS]

Ends definition of a database block. References outside this block are to the original database. When the block is exited, if there was an original database, it is made current.

Case Processing Commands

The case processing commands define a block of commands that is delimited with the END CASE command. These commands are not valid for caseless databases. Each time a case is accessed with one of these commands, the common CIR variables are available to other commands within the block. There are two commands that process cases:

PROCESS CASES retrieves a specific set of cases and updates these cases if required.
CASE IS (and the variants NEW CASE IS and OLD CASE IS) retrieves or creates a single case with a specified case id.

All updates to the database, including the creation of a new case, require the UPDATE keyword on the RETRIEVAL command. New cases are created with the CASE IS or NEW CASE IS block. Existing cases may be accessed with the other types of case blocks.

Commands in CASE Blocks

All commands(except AFTER RETRIEVAL), including other case block commands, may be used within a case block. The following commands may only be used within a case block:

Be aware of how commands transfer values from the CIR to local variables and vice-versa:

Any VisualPQL command in a case block that assigns a value to a variable assigns the value to a common variable if a common variable of the specified name exists.
The value of the case identifier variable can never be modified from within a case block.

COMPUTE can be used within case blocks to update database variables. If the computed variable is a CIR variable, the value of the expression is assigned to it and the database value is modified. For example, in a database that has a common variable called COMMVAR, the following retrieval allows the user to modify its value.

RETRIEVAL UPDATE
CASE IS 5
WRITE 'Current Value of COMMVAR is ' COMMVAR
COMPUTE COMMVAR = SREAD('Enter New Value for COMMVAR')
END CASE
END RETRIEVAL

GET VARS transfers the value of a CIR variable to a local variable. When a CIR variable is referenced within a case block, the value of the CIR variable is used (even if a local variable of the same name exists).

GET VARS can implicitly define a local variable with the definition of the database variable as well transferring the value, whereas COMPUTE simply assigns the value.

PUT VARS transfers values of local variables into database variables. PUT VARS may only be used in update mode.

The following example assigns the value of a CIR variable to a local variable that is accessed later from outside the case block.

RETRIEVAL
. PROCESS CASES REVERSE COUNT = 1 | find the last case
. GET VARS COMMVAR                | get value of COMMVAR
. END CASE
WRITE COMMVAR                     | display value of COMMVAR
END RETRIEVAL

Case Functions

`COUNT(rt_num)`	Returns the number of records of the specified record type belonging to the current case. If the specified record type is not defined, an undefined value is returned. (Use in the case block.)
`SYSTEM(14)`	Returns a 1 if the last `CASE IS, NEW CASE IS`, or `OLD CASE IS` block was executed. It returns 0 (zero) if the last block was not executed.
`SYSTEM(15)`	Returns a 1 if the last `CASE IS` or `NEW CASE IS` block created a new case. It returns 0 (zero) if the block did not create a new case. (Use the `SYSTEM` functions after the case block.)

CASE IS

[ NEW | OLD ] CASE IS  caseid [LOCK =  num]

CASE IS defines a block that accesses the single case specified by the case id. The case id value may be a constant or local variable, including an array reference.

CASE IS Accesses a single case in the database. If the case does not exist, then in UPDATE mode, a new case is created; if the retrieval is not in UPDATE mode and the case does not exist, the CASE IS block is skipped.

OLD CASE IS Accesses an existing case. If the case does not exist, the CASE IS block is skipped.
NEW CASE IS This command is only allowed in a RETRIEVAL UPDATE and creates a new case with the specified case identifier value. If the specified case already exists, the NEW CASE IS block is skipped and no new case is created.
LOCK Specifies case level locking for concurrent operations.

DELETE CASE

DELETE CASE [ KEEPCIR ]

Deletes the current case (CIR) and all records belonging to the case. This command is only allowed in UPDATE mode. Only users with the highest read and write security passwords for the database may delete cases and records.

KEEPCIR Deletes all records belonging to the case but does not delete the Common Information Record.

The following example deletes all cases that do not have any record type 1 records.

RETRIEVAL UPDATE
PROCESS CASES
IF(COUNT(1) EQ 0) DELETE CASE
END CASE
END RETRIEVAL

END CASE

END CASE

Terminates CASE IS and PROCESS CASE blocks.

END CASE IS terminates CASE IS blocks only.

END PROCESS CASE terminates PROCESS CASE blocks only.

EXIT CASE

EXIT CASE

Terminates processing of the current case block and transfers control to the first statement following the END CASE.

NEXT CASE

NEXT CASE

Terminates processing of the current case and retrieves the next case if there is one that meets the PROCESS CASE specification.

PREVIOUS CASE

PREVIOUS CASE

Terminates processing of the current case and retrieves the previous case if there is one that meets the PROCESS CASE specification. Use of this with SAMPLE or COUNT yields unpredictable results.

PROCESS CASE

PROCESS CASES [ ALL ]
              [ COUNT  = total [,inc [,start] ]]
              [ LIST   = caseid list ]
              [ LOCK = num]
              [ REVERSE ]
              [ SAMPLE = fraction [ ,seed ] ]

Defines the beginning of a case processing block that is delimited by the END CASE command. PROCESS CASE and PROCESS CIR are synonyms.
The options on PROCESS CASES define the set of cases that are stepped through. The commands in this block are executed once for each case within the specified set.

If there is no PROCESS CASE command in the retrieval and the database has a case structure, a PROCESS CASES ALL command is generated before the first executable command in the retrieval. The NOAUTOCASE option on the RETRIEVAL command suppresses this.

ALL Processes all cases in the database. This is the default.
COUNT Specifies the number of cases to process. The values for total, increment and start are variables or expressions which should resolve to positive integer values. A single integer number is a valid expression.
total
Specifies the maximum number of cases to retrieve. If more cases are requested than are available, the retrieval reads as many as exist. For example, to process the first 5 cases in the database:
PROCESS CASES COUNT = 5
increment
Specifies the "skipping factor" for retrieving cases. An increment of 3 produces every third case. For example, to access a total of 5 cases, retrieving every tenth case:
PROCESS CASES COUNT = 5 , 10
start
Specifies the ordinal of the first case processed. For example, 3 starts retrieving at the third case.
LIST Specifies a list of case identifier values of the cases to process. The list may be composed of constants or variables. The THRU keyword specifies an inclusive range of case id values. For example:
PROCESS CASES LIST = 1,2,8,17 WRITE IDNUM END CASE PROCESS CASES LIST = 1,5 THRU 10,18,20 WRITE IDNUM END CASE SET FVAR LVAR (5,10) PROCESS CASES LIST = FVAR THRU LVAR WRITE IDNUM END CASE

LOCK Specifies case level locking for concurrent operations.
REVERSE Specifies that the cases are processed in reverse order. Note that if you specify a list of specific cases, the list order is the order of processing regardless of this setting.
SAMPLE Retrieves a random sample of cases from the database. The specified values for fraction and seed are variables or expressions which should resolve to positive numbers. A specific decimal number or positive integer is a valid expression.
fraction
Specify a number between 0 (zero) and 1 (one). The retrieval generates a random number between 0 and 1 for each case. If the number falls between 0 and the specified number, the case is retrieved, otherwise processing goes on to the next case. Since each case is evaluated for inclusion independently, the actual sample may not be exactly the requested size particularly for databases with a limited number of cases. For example, to process 25% of the cases:
PROCESS CASES SAMPLE = .25
seed
Specify the starting seed for the random number generator. A given seed guarantees that the same set of random numbers is generated. Note that the PQLProcedures may also use samples and the SAMPLE option here, resets the seed for any sampling. It is recommended that the seed is set using the SEED option on the retrieval command and that all subsequent sampling in the retrieval uses the random numbers generated from that. If a seed is not specified, the random number generator is not reset. For example
PROCESS CASES SAMPLE = .25 , 13579

RESTORE CIR

RESTORE CIR

Re-reads the CIR from the database. When a case block is first executed, a CIR is read. Updates to common variables are performed in memory. The modified record is re-written when the case block is exited, another CIR is accessed or when a BACKUP command forces a write. A RESTORE CIR before the data is re-written cancels all modifications.

Record Processing Commands

Record processing commands access a specific record type. These commands define a block of commands that is delimited with the END RECORD command. When a record is accessed, the record variables are available to other VisualPQL commands within the record block. On case structured databases, record blocks must be nested within a case block. There are two commands that process records:

PROCESS RECORD retrieves a specific set of records and updates these records if required.
RECORD IS (and the variants NEW RECORD IS , OLD RECORD IS) retrieves or creates a single record with a specified record key.

To perform any updates to the database, including the creation of new records, the retrieval must be in update mode. New records are created with a RECORD IS or NEW RECORD IS block. Existing records may be accessed with the other types of record blocks.

Record Functions

`RECLEVEL(0)`	Returns the update level at which this record was last written to the database. Can only be used in a record block.
`SYSTEM(3)`	Returns the update level at which a record was last written to the database. The record referred to is the record from the last record block executed.
`SYSTEM(16)`	Returns a 1 if the last `REC IS, NEW REC IS`, or `OLD REC IS` block was executed. It returns 0 (zero) if the last block was not executed.
`SYSTEM(17)`	Returns a 1 if the last `REC IS` or `NEW REC IS` block created a new record. It returns 0 (zero) if the block did not create a new record. (Use the `SYSTEM` functions after the record block.)

Commands in RECORD Blocks

All commands, including other record block commands, may be used within a record block. The following commands may only be used within a record block:

DELETE RECORD
EXIT RECORD
NEXT RECORD
RESTORE RECORD
RETRY RECORD
BACKUP.

Be aware of how commands transfer values from the record to local variables and vice-versa:

Any command in a record block that assigns a value to a variable assign the value to a database record variable if a record variable of the specified name exists.
The values of the case identifier variable and record type keyfield variables can never be modified from within a record block.

COMPUTE or PUT VARS can be used in record blocks to update database variables. The database variables can only be updated in a retrieval in update mode. If the computed variable is a common or record variable, the value of the expression is assigned to it and the database value is modified.

COMPUTE or GET VARS can be used to transfer the value of a database variable to a local variable. GET VARS implicitly defines a local variable with the same definition as the database variable as well transferring the value, whereas COMPUTE simply assigns the value. When a record variable is referred to in an expression, the record variable is used even if a local variable of the same name exists.

RECORD IS

[ OLD | NEW ] RECORD IS {name | number} (value list)

Defines a record block that accesses a single record. The value list must specify a valid value for every keyfield of the record type. If any keyfield is missing or undefined, the block is skipped. RECORD and REC are synonyms. In a case structured database, record blocks occur within case blocks and the records accessed belong to the current case.

RECORD IS Accesses a record if it exists. If it does not exist and the retrieval is not in UPDATE mode, the RECORD IS block is skipped. In UPDATE mode, a new record is created.
OLD RECORD IS Accesses an existing record. If the specified record does not exist, the OLD REC IS block is skipped.
NEW RECORD IS Creates a new record with the specified key values. Only allowed in a RETRIEVAL UPDATE. If the specified record exists, the NEW REC IS block is skipped.
name | number The record name or number. This must be specified.
value list A list of values expressed as constants, variable names or array references. Each element in the list represents a value for a keyfield. Specify the values in sequence to match keyfields in the order defined in the schema. Specify a valid value for every keyfield of the record type. If any keyfield is missing or undefined, the record block is skipped. If the record type being accessed has no keyfields, specify the command without a value list.
LOCK Specifies record level locking for concurrent operations.

DELETE RECORD

DELETE RECORD Deletes the current record.

A record can only be deleted in UPDATE mode. Deleting a record requires write security at an equal or higher level to the record write security level. It also requires write security at an equal or higher level to the highest write security level of any variable in the record. For example, the following deletes all record type 3 records that were updated at update level 47:

RETRIEVAL UPDATE
PROCESS CASES
. PROCESS REC 3
. IF(RECLEVEL(0) EQ 47 )DELETE REC
. END REC
END CASE
END RETRIEVAL

END RECORD

END RECORD [IS]
END PROCESS RECORD
END PROCESS JOURNAL
END JOURNAL RECORD

Terminates RECORD IS, PROCESS RECORD blocks and PROCESS JOURNAL, JOURNAL RECORD blocks.

END RECORD IS terminates RECORD IS blocks.

END PROCESS RECORD terminates PROCESS RECORD blocks.

END PROCESS JOURNAL terminates PROCESS JOURNAL blocks.

END JOURNAL RECORD terminates JOURNAL RECORD blocks.

REC is a synonym for RECORD.

EXIT RECORD

EXIT RECORD

Terminates processing of the current record block and transfers control to the first statement following the END RECORD.

REC is a synonym for RECORD.

NEXT RECORD

NEXT RECORD

Terminates processing of the current record and retrieves the next record if it exists. REC is a synonym for RECORD. The following example processes only the males in the database.

RETRIEVAL
PROCESS CASES
. PROCESS RECORD EMPLOYEE
.   IFNOT (GENDER = 1) NEXT REC  |- go to next rec if not male
.   WRITE NAME SSN BIRTHDAY
. END REC
END CASE
END RETRIEVAL

PREVIOUS RECORD

PREVIOUS RECORD

Terminates processing of the current record and retrieves the previous record if it exists. REC is a synonym for RECORD. Use of this with SAMPLE or COUNT yields unpredictable results.

PROCESS REC

PROCESS RECORD  name | num
               [ LOCK = num ]
               [ INDEXED BY index_name ]
               [ ONETIME ]
               [ REVERSE ]
               [ CASEFUNR ]
               [ AFTER (value list) ]
               [ AFTER (value list) THRU (value list) ]
               [ AFTER (value list) UNTIL(value list) ]
               [ FROM  (value list) ]
               [ FROM  (value list) THRU (value list) ]
               [ FROM  (value list) UNTIL(value list) ]
               [ THRU  (value list) ]
               [ UNTIL (value list) ]
               [ VIA   (value list) ]

PROCESS RECORD, (PROCESS REC is a synonym), defines a block of commands that is executed repeatedly, once for each record of the specified type within the specified range. If the command does not use the INDEXED BY construct, then, in a case structured database, the command must be inside a case block and the records accessed are those belonging to the current case.

Note: Specifying a record selection clause (e.g. AFTER, FROM, THRU, etc.) on the PROCESS REC locates records through the database index, which is an efficient way to process subsets of records. Use record selection clauses whenever possible.

name | num The name or number of the record type to retrieve. This is required.

LOCK Specifies record level locking for concurrent operations.

INDEXED BY Specifies the name of the index to use to retrieve records. All record selection clauses can be used in conjunction with INDEXED BY. When an index is used, the key values are those values used for the index.

ONETIME By default, when no records exist within the specified range, the block is skipped. ONETIME forces the block to be entered with the values of the record variables set to undefined, when there are no matching records.
REVERSE Processes the records in reverse order. If used with a record selection clause, processes the selected subset in reverse order. If specifying a range of record keys to select, specify these in the normal way (i.e. the FROM key has a lower value than the UNTIL key).
CASEFUNR When used with the Across record functions this keyword caused these functions to calculate statistics across all cases. That is, the statitics are reset at the start of the outer case block rather than the start of the record block.
AFTER Selects records whose key value is greater than that specified by the value list. AFTER can be used in combination with THRU or UNTIL to specify a range of keys.
FROM Selects records whose key value is greater than or equal to the key specified by the value list. FROM can be used in combination with THRU or UNTIL to specify a range of keys.
THRU Specifies the key value to process to and include in the retrieved subset. Use AFTER or FROM to specify a beginning record for processing.
UNTIL Specifies the key value to process up to but not include in the retrieved subset. Use AFTER or FROM to specify a beginning record for processing.

VIA Selects records whose key value matches (equals) the key specified by the value list. If a partial key value list is specified, all records matching the partial list are selected. WITH is a synonym for VIA.
value list A list of values for keyfields. These may be expressed as constants, variable names or array references. The list is matched with values of keyfields in the order defined in the schema or the order defined in the index. The value list need not list values for the entire set of keyfields. Low level keys may be omitted, but not higher levels. For example, if A, B, and C represent a record's keyfields, then:
VIA (A, B, C) legal VIA (A) legal VIA (A, B) legal VIA (,,C) invalid, needs A and B VIA (A,,C) invalid, needs B
During execution, if a value is undefined or missing, the value list is treated as if it were terminated with the value previous to the undefined value.
Note: In earlier versions of the software, undefined values caused an execution error.

RESTORE REC

RESTORE REC

Re-reads the current record from the database. When a record block is first executed, a data record is read into memory. Updates to record variables are performed in memory. The modified record is re-written to the database when the block is exited. A RESTORE REC cancels all modifications when done before the record is re-written.

RETRY RECORD is a synonym.

BACKUP

BACKUP

Writes modified database record or CIR to the database.

During a Retrieval Update, updates are performed in memory. The modified CIR or record is copied to the database when the processing block is exited or before another CIR or record occurrence is accessed.

BACKUP forces a write and is very seldom needed.

This is only allowed in retrievals in update mode.

PROCESS DATA

PROCESS DATA defines a block of commands that is executed repeatedly, once for each record of every type. In a case structured database the command must be inside a case block and the records accessed are those belonging to the current case.

The VisualPQL funtion CURREC returns the record type number currently being processed.

Note: As the record type is not fixed inside this loop, the set of database variables available is also not fixed. Because of this you cannot hard-code variables in this block and you must use indirect functions like VARGET and VARPUT to access these variables.

RETRIEVAL
. PROCESS CASE ALL
.   PROCESS DATA 
.     COMPUTE RECNO = CURREC(0)
.     IFNOT (RECNO EQ 1) EXIT DATA
.     COMPUTE N = NVARS(RECNO)
.     FOR I = 1,N
.       COMPUTE VN = VARNAME(RECNO,I)
.       COMPUTE VVAL = VARGET (VN)
.       WRITE VN VVAL
.     END FOR
.   END PROCESS DATA
. END PROCESS CASE
END RETRIEVAL

Processing Database Journals

A database journal is a record of updated records on the database. (A database unload file is in identical format and can also be processed in VisualPQL with these commands.)

The journal file consists of a linked set of entries, one entry per update run. Each entry consists of a set of images of updated records in that run. The images consist of before and after images of updated records.

The PROCESS JOURNAL command allows you to get information about the various entries on the journal and to select one or more entries to process. When processing through an entry, data records are read in sequence from the earliest to the latest. Within the PROCESS JOURNAL block, a JOURNAL RECORD IS record_type names the record that is of interest. This block is given control when a record of that type is read. Within this block, you can use normal VisualPQL to access the data from the journaled record using the record variable names. e.g.

PROCESS JOURNAL
. JOURNAL RECORD IS record_type
.   PQL access to record variables
. END JOURNAL RECORD IS
END PROCESS JOURNAL

You can specify a PROCESS JOURNAL in a program and it can run with no database attached to examine the headers on the file. However a database schema is needed to interpret the data and to compile any JOURNAL RECORD IS record_type commands and so the JOURNAL RECORD IS record_type can only appear in a retrieval (you may want to specify NOAUTOCASE). In a retrieval, the file being processed must match the current database both at compile time and at execution time.

If you are processing a journal for a case structured database, note that the journal entries for individual records do not have any non-key common variables; these are on a separate journal for the CIR. The common vars can only be referred to in a JOURNAL RECORD IS CIR block and not within the individual record blocks. Further, the journal holds a sequential series of records which is written as the records are updated. If records in a case are updated but no common vars are updated, then there will not be a journal entry for the CIR. If some common vars are updated, the CIR journal entry follows the individual record journal entries. If only common vars are updated, then there will not be a journal entry for the individual record type.

Do not specify a JOURNAL RECORD IS record_type block inside another JOURNAL RECORD IS record_type block. Since the block is only entered for the specified record type, the inner block with a different record type is never executed.

You can compile JOURNAL RECORD IS record_type blocks which are not physically in a PROCESS JOURNAL block so they might be in a sub-routine or sub-procedure. If a JOURNAL RECORD IS record_type block is executed that is not in an executing PROCESS JOURNAL block, it is simply skipped.

PROCESS JOURNAL

PROCESS JOURNAL
  [FILENAME= fname_expression ] (sr5 is the default)
  [FROM = updlevel | START = date [,time] ]
  [THRU = updlevel | END   = date [,time] ]
  [REVERSE]
 Return Data
  [DATE = varname] [ENDDATE = varname]
  [TIME = varname] [ENDTIME = varname]
  [LEVEL = varname]
  [RECORD = varname]
  [TYPE  = varname]
  [USER  = varname]

PROCESS JOURNAL, defines a block of commands that is executed repeatedly, once for each journal record within the specified range. Some records are headers that identify the update run and some are data records that contain information about a particular record type that was updated in the run.

The PROCESS JOURNAL command has two sets of keyword specifications. The first set specify a filename, which journal entries to process and whether to go from earliest to latest or in reverse. Selecting entries to process can be on the basis of update levels or date and time and can specify either start points, end points or both. All of these specifications are expressions which evaluate to the value to use. Typically these are specified as a variable name which holds the value.

The second set of specifications name a number of variables that are then used by the process to return information to the program. If you are selecting multiple entries, then information may be needed about which entry is being processed. When processing the potentially multiple records within an entry, information may be needed about the individual record image that is above and beyond the actual data in the record.

The named file is processed until a header matches the PROCESS JOURNAL specification. Control is then passed to the VisualPQL inside the block for each record until a new header is reached that does not match the specification and the block is exited.

FILENAME = fname_expression The journal file to process. If not specified, the default is the current journal file for the default database (the .sr5 file). Specify the name as an expression, that is a string variable or other string expression. If you are specifying a known filename, you can simply enclose it in quotes e.g.
FILENAME = 'COMPANY.UNL'

FROM = updlevel The first update level to start processing journal entries. If not specified, processing starts at the first journal entry on the file.

THRU = updlevel The last update level to process. If not specified, processing stops after processing the last journal entry on the file.

START = date [,time] The date and, optionally the time, of the first journal entry to start processing. Date is an expression that resolves to a date in format MMIDDIYY. Time, if specified, is an expression that resolves to a time in format HHIMMISS. Use either (or neither) a start time or a from update level, do not specify both.

END = date [,time] The date, and optionally the time, of the last journal entry to process. Date is an expression that resolves to a date in format MMIDDIYY. Time, if specified, is an expression that resolves to a time in format HHIMMISS. Use either (or neither) an end time or a thru update level, do not specify both.

REVERSE Specifies that the journal is processed in reverse sequence. This only effects the sequence of entries not the sequence of records presented within entries. i.e. If the journal holds entries relating to updates that took the database from update level 5 to 6, 6 to 7 and 7 to 8, REVERSE presents 7 to 8, then 6 to 7 then 5 to 6. This also affects the way that you specify selection - specify the level/date/time to start that is higher/later than the one to finish.

DATE = varname Specify a variable name. If specified, this contains the start date of the journal entry currently being processed.

ENDDATE = varname Specify a variable name. If specified, this contains the end date of the journal entry currently being processed.

TIME = varname Specify a variable name. If specified, this contains the start time of the journal entry currently being processed.

ENDTIME = varname Specify a variable name. If specified, this contains the end time of the journal entry currently being processed.

LEVEL = varname Specify a numeric variable name. If specified, this contains the update level of the journal entry currently being processed.

RECORD = varname Specify a numeric variable name. If specified, this contains the record type of the journal data record currently being processed.

TYPE = varname Specify a numeric variable name. If specified, this contains the type of the journal record currently being processed. The journal type is a positive number for data records and a negative number for journal headers. Types are:
1 New record written (This is the type of all data on an unload file.)
2 Before existing record updated
3 After existing record updated. Note that these before and after records are a pair and are written together.
4 Before Record deleted
-1 Journal Data header
-2 Unload Schema header
-3 Unload Data header
-4 Journal Schema header
-5 User header

USER = varname Specify a character variable name capable of holding a 32 byte name. If specified, returns the name of the user responsible for the update. This is taken from the SIRUSER if it is specified on start up, or from the system environment variables (from sir.ini) USERNAME or USER. The username can be set during a session by the SIRUSER PQL function.

See Processing Journals for more details.

JOURNAL RECORD IS

JOURNAL RECORD IS {name | number}

Defines a record block that is entered when the journal records being processed match the specified record type.If there is a JOURNAL RECORD IS record inside the PROCESS JOURNAL block and the journal record being processed matches the record type specified then, when that block is reached, the block is processed. If the journal record does not match a JOURNAL RECORD IS record that block is skipped.

Specify either a record number or a record name. To process the CIR specify either 0 or CIR.

Within the block, you can use normal record variable names to process the data from the journal record. You can use these for reports or can use them for other database manipulation. You can nest other blocks e.g. database access, if required.

See Processing Journals for more details.

EXIT JOURNAL IS

EXIT JOURNAL IS

Terminates processing of the current journal record and exits the journal record is block.

EXIT PROCESS JOURNAL

EXIT PROCESS JOURNAL

Terminates processing of the current journal record and exits the process journal block.

NEXT PROCESS JOURNAL

NEXT PROCESS JOURNAL

Terminates processing of the current journal record and retrieves the next journal record. This may be a data record or may be a new header.

NEXT PROCESS HEADER

NEXT PROCESS HEADER

Terminates processing of the current journal set of records and retrieves the next journal header. If processing a large journal update or an unload, this is much more efficient than processing through every data record looking for the next header.

Concurrent VisualPQL

Use the MST= parameter when starting a SIR/XS session, or use the SET MASTER command to use Master and thus use concurrent VisualPQL. VisualPQL programs run concurrently may update the database concurrently with other products using Master.

MASTER must be running when a client tries to use it and all retrievals then run through MASTER until the use of Master is turned off with a CLEAR MASTER command.

Read only retrievals run much faster when run in stand alone mode rather than through MASTER. Retrievals execute properly in either mode.

Utilities ignore Master settings and may require exclusive access to the database.

Locking

The LOCK = keyword on the database access commands, apply a lock to the case or record being accessed for concurrent operations. The lock type is a numeric value and may be specified as a constant or as an integer variable. Lock values are:

0 - Null. The lock is not specified and takes the default (exclusive in updates, concurrent read in retrievals). Same as not specifying a lock clause.

1 - Exclusive. Same as 6.

2 - Concurrent Read. Anyone else may read or write this record. This process intends to read this record. This is the default in retrievals.

3 - Concurrent Write. Anyone else may read or write this record. This process intends to write this record.

4 - Protected Read. Anyone else may read this record. No-one may write this record. This process intends to read this record.

5 - Protected Write. Anyone else may read this record. No-one may write this record. This process intends to write this record.

6 - Exclusive. No-one else may read or write this record. This is the default in updates.

Changing Locks

Once a record or case has been retrieved, it is possible to alter the locktype held on that record with the CASELOCK and RECLOCK functions. Specify the new locktype on the function. If the change is successful, the record is written to the database and re-retrieved with the new locktype and the function returns a 1. The function returns a zero if the change could not be made because of other locks on the record.

Lock Conflicts

During concurrent execution, the retrieval may encounter a record that is locked by another user.

	Requested Lock
Current Lock	Null	1	2	3	4	5
EX	Locked	Locked	Locked	Locked	Locked	Locked
CR	Read	Locked	Write	Write	Write	Write
CW	Read	Locked	Write	Write	Locked	Locked
PR	Read	Locked	Write	Locked	Write	Locked
PW	Read	Locked	Write	Locked	Locked	Locked

If the case/record is locked (see table above) then:

the current case/record variables are all set to undefined;
a flag is set that can be tested with two functions: SYSTEM(36 ) for records and SYSTEM (37) for cases ;
control is passed to the first statement in the block.

If the record is not available, the retrieval could wait to try accessing it again by using the RETRY RECORD or RESTORE REC command. e.g.

PROCESS RECORD PATIENT LOCK = 4    | get the patient record
. LOOP
.  IF(SYSTEM(36) = 1)  EXIT LOOP  | exit if we get the record
.  WRITE 'Waiting for locked record' at  24,5
.  WAIT 5                         | wait half a second
.  RETRY RECORD                   | try to get the record
. END LOOP
...
END PROCESS RECORD

LOOKUP

LOOKUP {RECORD dbname.recname | TABLE tabfile.table}
       [FORWARD | BACKWARD]
       [GET VARS { ALL|
                   target_varlist |
                   local_varlist = target_varlist}]
       [INDEXED BY indexname]
       [RESULT num_varname]
       [USING caseid,keylist | VIA keylist]
       [WHERE (condition)]

LOOKUP accesses a single database record or table row if one exists that matches keys and/or conditions and returns data as specified. The RECORD or TABLE clause must be specified. Unless further clauses are specified, the command does not achieve anything. The command may be specified in a PROGRAM, RETRIEVAL or SUBROUTINE at any point. It does not affect other database or table access processes.

RECORD [dbname.]recname | TABLE [tabfile.]table Specify either a record or table to use for the lookup. Specify the database or tabfile containing the record or row unless the default. The database or tabfile must be connected both at compile time and at execution time.

FORWARD | BACKWARD Specify either FORWARD or BACKWARD to control the direction of search. FORWARD is the default.

GET VARS ALL | target_varlist | local_varlist = target_varlist Specify GET VARS clause to pass back values if found.
The keyword ALL specifies all the matching record or table variables are assigned to local variables of the same name.
A single list of variables creates a set of local variables with the same names as the database or table variable list. Use the TO keyword to specify a set of variables. Note that ALL or a single list can only be used where table variables have valid local variable names.
A list of local variables can be equated to a list of variables from the target record or table and the local variables are assigned the values of the database or table variables. The two lists must be of equal length and the value assignments are performed listwise.

INDEXED BY indexname Specify an index to use if necessary.

RESULT num_varname Specify a RESULT numeric variable to return positive for record found, negative for not found. A negative number is an error code and associated text can be retrieved with the MSGTXT function.

USING | VIA Specify either USING or VIA to lookup using particular key values. On a case structured database, not using an index, USING specifies the case key first, then record keys. VIA specifies keys in sequence either from an index or from the current case. Where a key is specified, it is matched exactly. If all keys are not specified, the subset of records identified by the partial key is used. The values specified may be constants or expressions. If expressions use database record variables, these are from the current context not from the record being looked for.

WHERE (condition) Specify a WHERE condition to test prospective records (either the record that matched specified keys exactly or the subset identified by partial keys). The first to satisfy the condition is returned. Variables used in the condition clause may either be local or from the looked up record.

`CASE IS`	Accesses a single case in the database. If the case does not exist, then in `UPDATE` mode, a new case is created; if the retrieval is not in `UPDATE` mode and the case does not exist, the `CASE IS` block is skipped.
`OLD CASE IS`	Accesses an existing case. If the case does not exist, the `CASE IS` block is skipped.
`NEW CASE IS`	This command is only allowed in a `RETRIEVAL UPDATE` and creates a new case with the specified case identifier value. If the specified case already exists, the `NEW CASE IS` block is skipped and no new case is created.
`LOCK`	Specifies case level locking for concurrent operations.

`ALL`	Processes all cases in the database. This is the default.
`COUNT`	Specifies the number of cases to process. The values for total, increment and start are variables or expressions which should resolve to positive integer values. A single integer number is a valid expression. `total` Specifies the maximum number of cases to retrieve. If more cases are requested than are available, the retrieval reads as many as exist. For example, to process the first 5 cases in the database: PROCESS CASES COUNT = 5 `increment` Specifies the "skipping factor" for retrieving cases. An increment of 3 produces every third case. For example, to access a total of 5 cases, retrieving every tenth case: PROCESS CASES COUNT = 5 , 10 `start` Specifies the ordinal of the first case processed. For example, 3 starts retrieving at the third case.
`LIST`	Specifies a list of case identifier values of the cases to process. The list may be composed of constants or variables. The `THRU` keyword specifies an inclusive range of case id values. For example: PROCESS CASES LIST = 1,2,8,17 WRITE IDNUM END CASE PROCESS CASES LIST = 1,5 THRU 10,18,20 WRITE IDNUM END CASE SET FVAR LVAR (5,10) PROCESS CASES LIST = FVAR THRU LVAR WRITE IDNUM END CASE
`LOCK`	Specifies case level locking for concurrent operations.
`REVERSE`	Specifies that the cases are processed in reverse order. Note that if you specify a list of specific cases, the list order is the order of processing regardless of this setting.
`SAMPLE`	Retrieves a random sample of cases from the database. The specified values for fraction and seed are variables or expressions which should resolve to positive numbers. A specific decimal number or positive integer is a valid expression. `fraction` Specify a number between 0 (zero) and 1 (one). The retrieval generates a random number between 0 and 1 for each case. If the number falls between 0 and the specified number, the case is retrieved, otherwise processing goes on to the next case. Since each case is evaluated for inclusion independently, the actual sample may not be exactly the requested size particularly for databases with a limited number of cases. For example, to process 25% of the cases: `PROCESS CASES SAMPLE = .25` `seed` Specify the starting seed for the random number generator. A given seed guarantees that the same set of random numbers is generated. Note that the PQLProcedures may also use samples and the `SAMPLE` option here, resets the seed for any sampling. It is recommended that the seed is set using the `SEED` option on the retrieval command and that all subsequent sampling in the retrieval uses the random numbers generated from that. If a seed is not specified, the random number generator is not reset. For example `PROCESS CASES SAMPLE = .25 , 13579`

`RECORD IS`	Accesses a record if it exists. If it does not exist and the retrieval is not in `UPDATE` mode, the `RECORD IS` block is skipped. In `UPDATE` mode, a new record is created.
`OLD RECORD IS`	Accesses an existing record. If the specified record does not exist, the `OLD REC IS` block is skipped.
`NEW RECORD IS`	Creates a new record with the specified key values. Only allowed in a `RETRIEVAL UPDATE`. If the specified record exists, the `NEW REC IS` block is skipped.
`name \| number`	The record name or number. This must be specified.
`value list`	A list of values expressed as constants, variable names or array references. Each element in the list represents a value for a keyfield. Specify the values in sequence to match keyfields in the order defined in the schema. Specify a valid value for every keyfield of the record type. If any keyfield is missing or undefined, the record block is skipped. If the record type being accessed has no keyfields, specify the command without a value list.
`LOCK`	Specifies record level locking for concurrent operations.

`name \| num`	The name or number of the record type to retrieve. This is required.
`LOCK`	Specifies record level locking for concurrent operations.
`INDEXED BY`	Specifies the name of the index to use to retrieve records. All record selection clauses can be used in conjunction with `INDEXED BY`. When an index is used, the key values are those values used for the index.
`ONETIME`	By default, when no records exist within the specified range, the block is skipped. `ONETIME` forces the block to be entered with the values of the record variables set to undefined, when there are no matching records.
`REVERSE`	Processes the records in reverse order. If used with a record selection clause, processes the selected subset in reverse order. If specifying a range of record keys to select, specify these in the normal way (i.e. the `FROM` key has a lower value than the `UNTIL` key).
`CASEFUNR`	When used with the Across record functions this keyword caused these functions to calculate statistics across all cases. That is, the statitics are reset at the start of the outer case block rather than the start of the record block.
`AFTER`	Selects records whose key value is greater than that specified by the value list. `AFTER` can be used in combination with `THRU` or `UNTIL` to specify a range of keys.
`FROM`	Selects records whose key value is greater than or equal to the key specified by the value list. `FROM` can be used in combination with `THRU` or `UNTIL` to specify a range of keys.
`THRU`	Specifies the key value to process to and include in the retrieved subset. Use `AFTER` or `FROM` to specify a beginning record for processing.
`UNTIL`	Specifies the key value to process up to but not include in the retrieved subset. Use `AFTER` or `FROM` to specify a beginning record for processing.
`VIA`	Selects records whose key value matches (equals) the key specified by the value list. If a partial key value list is specified, all records matching the partial list are selected. `WITH` is a synonym for `VIA`.
`value list`	A list of values for keyfields. These may be expressed as constants, variable names or array references. The list is matched with values of keyfields in the order defined in the schema or the order defined in the index. The value list need not list values for the entire set of keyfields. Low level keys may be omitted, but not higher levels. For example, if A, B, and C represent a record's keyfields, then: VIA (A, B, C) legal VIA (A) legal VIA (A, B) legal VIA (,,C) invalid, needs A and B VIA (A,,C) invalid, needs B During execution, if a value is undefined or missing, the value list is treated as if it were terminated with the value previous to the undefined value. Note: In earlier versions of the software, undefined values caused an execution error.

`FILENAME = fname_expression`	The journal file to process. If not specified, the default is the current journal file for the default database (the .sr5 file). Specify the name as an expression, that is a string variable or other string expression. If you are specifying a known filename, you can simply enclose it in quotes e.g. FILENAME = 'COMPANY.UNL'
`FROM = updlevel`	The first update level to start processing journal entries. If not specified, processing starts at the first journal entry on the file.
`THRU = updlevel`	The last update level to process. If not specified, processing stops after processing the last journal entry on the file.
`START = date [,time]`	The date and, optionally the time, of the first journal entry to start processing. Date is an expression that resolves to a date in format `MMIDDIYY`. Time, if specified, is an expression that resolves to a time in format `HHIMMISS`. Use either (or neither) a start time or a from update level, do not specify both.
`END = date [,time]`	The date, and optionally the time, of the last journal entry to process. Date is an expression that resolves to a date in format `MMIDDIYY`. Time, if specified, is an expression that resolves to a time in format `HHIMMISS`. Use either (or neither) an end time or a thru update level, do not specify both.
`REVERSE`	Specifies that the journal is processed in reverse sequence. This only effects the sequence of entries not the sequence of records presented within entries. i.e. If the journal holds entries relating to updates that took the database from update level 5 to 6, 6 to 7 and 7 to 8, `REVERSE` presents 7 to 8, then 6 to 7 then 5 to 6. This also affects the way that you specify selection - specify the level/date/time to start that is higher/later than the one to finish.
`DATE = varname`	Specify a variable name. If specified, this contains the start date of the journal entry currently being processed.
`ENDDATE = varname`	Specify a variable name. If specified, this contains the end date of the journal entry currently being processed.
`TIME = varname`	Specify a variable name. If specified, this contains the start time of the journal entry currently being processed.
`ENDTIME = varname`	Specify a variable name. If specified, this contains the end time of the journal entry currently being processed.
`LEVEL = varname`	Specify a numeric variable name. If specified, this contains the update level of the journal entry currently being processed.
`RECORD = varname`	Specify a numeric variable name. If specified, this contains the record type of the journal data record currently being processed.
`TYPE = varname`	Specify a numeric variable name. If specified, this contains the type of the journal record currently being processed. The journal type is a positive number for data records and a negative number for journal headers. Types are: 1 New record written (This is the type of all data on an unload file.) 2 Before existing record updated 3 After existing record updated. Note that these before and after records are a pair and are written together. 4 Before Record deleted -1 Journal Data header -2 Unload Schema header -3 Unload Data header -4 Journal Schema header -5 User header
`USER = varname`	Specify a character variable name capable of holding a 32 byte name. If specified, returns the name of the user responsible for the update. This is taken from the `SIRUSER` if it is specified on start up, or from the system environment variables (from `sir.ini)` `USERNAME` or `USER`. The username can be set during a session by the `SIRUSER` PQL function.

`RECORD [dbname.]recname \| TABLE [tabfile.]table`	Specify either a record or table to use for the lookup. Specify the database or tabfile containing the record or row unless the default. The database or tabfile must be connected both at compile time and at execution time.
`FORWARD \| BACKWARD`	Specify either `FORWARD` or `BACKWARD` to control the direction of search. `FORWARD` is the default.
`GET VARS ALL \| target_varlist \| local_varlist = target_varlist`	Specify `GET VARS` clause to pass back values if found. The keyword `ALL` specifies all the matching record or table variables are assigned to local variables of the same name. A single list of variables creates a set of local variables with the same names as the database or table variable list. Use the `TO` keyword to specify a set of variables. Note that `ALL` or a single list can only be used where table variables have valid local variable names. A list of local variables can be equated to a list of variables from the target record or table and the local variables are assigned the values of the database or table variables. The two lists must be of equal length and the value assignments are performed listwise.
`INDEXED BY indexname`	Specify an index to use if necessary.
`RESULT num_varname`	Specify a `RESULT` numeric variable to return positive for record found, negative for not found. A negative number is an error code and associated text can be retrieved with the `MSGTXT` function.
`USING \| VIA`	Specify either `USING` or `VIA` to lookup using particular key values. On a case structured database, not using an index, `USING` specifies the case key first, then record keys. `VIA` specifies keys in sequence either from an index or from the current case. Where a key is specified, it is matched exactly. If all keys are not specified, the subset of records identified by the partial key is used. The values specified may be constants or expressions. If expressions use database record variables, these are from the current context not from the record being looked for.
`WHERE (condition)`	Specify a `WHERE` condition to test prospective records (either the record that matched specified keys exactly or the subset identified by partial keys). The first to satisfy the condition is returned. Variables used in the condition clause may either be local or from the looked up record.