VisualPQL

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

VisualPQL

Accessing Tables

There are commands that create table rows, that update table variables and that access data stored in tables.

The structure and contents of tables and tabfiles is discussed in Tabfiles and Tables.

Tables in tabfiles may be accessed in any VisualPQL routine (program, retrieval or subroutine). Options on these routine commands affect tabfile and table processing.

Before a routine can be compiled or executed, the tabfile must be connected.

A program can connect a tabfile at execution time with the PQL CONNECT TABFILE command.

Table processing differs slightly from database record:

Tables may have indexes that can be used to access the table rows. Accessing rows through an index determines the sequence in which the rows are retrieved.
The only commands that deal directly with variables in a table are GET VARS and PUT VARS. When retrieving a row of a table, move the values of the variables into local variables with a GET VARS. Make any modifications to the local variables. To update the values of variables in a table row, move the local variables into the table row with a PUT VARS. Values of key fields of the index being used may not be updated with PUT VARS.
The names of the variables, indexes and tables may be up to 32 characters long.

Use the OPEN TABLE and CLOSE TABLE to open and close tables. If these are not used, the tables are opened automatically when referenced.

Row Processing Commands

Row processing commands access a specific table. These commands define a block of commands that is delimited with the END ROW command. Retrieve required row variables using the GET VARS command to make the variables available to other VisualPQL commands within the block. There are two commands that process rows:

PROCESS ROW retrieves a specific set of rows and updates these if required.
ROW IS (and the variants NEW ROW IS , OLD ROW IS) retrieves or creates a single row.
Any updates to the table, including the creation of new rows, require the TUPDATE keyword either on the RETRIEVAL command or on the row processing command. Create new rows with a ROW IS or NEW ROW IS block. Existing rows may be accessed with the other types of row blocks.

Indexes

Tables can have Indexes that may uniquely identify a row or may identify a subset of rows. Options on the row block commands specify a subset of the rows by specifying an index and a range of index values. A table may have more than one index and more than one variable in an index. VisualPQL locates individual rows through the index.

Commands in ROW blocks

Any command, including other row, case and record block commands, may be used within a row block. The following commands may only be used in row blocks:

DELETE ROW Deletes the current row.
EXIT ROW Terminates processing of the row block.
NEXT ROW Retrieves the next row in a PROCESS ROWS block.
PREVIOUS ROW Retrieves the previous row in a PROCESS ROWS block.

ROW functions

Specify these functions after the ROW IS block to which they apply:

SYSTEM(27) Returns a 1 if the last ROW IS, NEW ROW IS, or OLD ROW IS block was executed. It returns 0 (zero) if the last ROW IS block was not executed.
SYSTEM(28) Returns a 1 if the last ROW IS or NEW ROW IS block created a new row. It returns 0 (zero) if the block did not create a new row in the table.

OPEN TABLE

OPEN TABLE tabfile_name.table_name [ MODE mode_num ]

Opens the specified table.

Specify the tabfile name and table name either as variables that contain the name or as quoted strings. Ensure that the names have the correct use of upper and lower case letters as both are allowed in tabfile and table names and thus no automatic conversions are done.

The MODE clause specifies whether the table is opened for read or write access. Specify the mode_num as a numeric variable or constant. 1 specifies READ mode, 2 specifies WRITE mode. The default if MODE is not specified is READ mode.

If the tabfile is not connected or the table does not exist, a run time error is issued.

OPEN TABLE "TESTFILE"."TABLE1" MODE 2

CLOSE TABLE

CLOSE TABLE tabfile_name.table_name

Closes the specified table.

Specify the tabfile name and table name as variables that contain the name or quoted strings. Ensure that the names have the correct use of upper and lower case letters as both are allowed in tabfile and table names and thus no automatic conversions are done.

PQL CONNECT TABFILE

PQL CONNECT TABFILE tabfile_name_exp
    [ FILENAME filename_exp | attribute_exp ]
    [ MODE {varname | constant} ]
    [ SECURITY exp,exp,exp,exp ]
    [ IOSTAT = varname ]

Connects the specified tabfile at execution time. All of the parameters are expressions; enclose names in quotes if specifying a constant. When assigning string values to expressions, ensure names are upper case.

tabfile name
The internal name of the tabfile. Must be the same name as used when the tabfile was created.

FILENAME
The name of the operating system file if different to the internal tabfile name plus the .tbf suffix.

MODE
Specifies if the tabfile is opened for READ or WRITE. If MODE is not specified, it is connected for READ. Specify 1 for READ, 2 for WRITE.

SECURITY
Specifies Group Name, Group Password, User Name and User Password in this order.

IOSTAT
Specifies a variable to receive the return code generated by the file open operation. A return code of 0 (zero) indicates successful connection.
-7001 (Host error message number) indicates that the tabfile could not be opened.

Note: Because this command connects the tabfile at execution time, the tabfile may not be connected at compile time. If the are subsequent references to the tabfile in this VisualPQL program then they may not compile. You need to connect the tabfile before compiling.

PQL DISCONNECT TABFILE

PQL DISCONNECT TABFILE tabfile_name_exp [IOSTAT = varname]

Disconnects a tabfile.

IOSTAT
Specifies a variable to receive the return code generated by the file close operation. A return code of 0 (zero) indicates successful disconnection as specified. -88 (DBMS error message number) indicates that the tabfile could not be disconnected.

DELETE ROW

DELETE ROW

Deletes the current row. To delete a row, use the DELETE ROW command in a ROW IS or PROCESS ROW block. This command may only be used in TUPDATE mode.

END ROW

END ROW [IS]
END PROCESS ROW

Terminates ROW IS and PROCESS ROW blocks.

END ROW IS terminates ROW IS blocks.

END PROCESS ROW terminates PROCESS ROW blocks.

EXIT ROW

EXIT ROW

Terminates processing of the row block.

NEXT ROW

NEXT ROW

Retrieves the next row in a PROCESS ROWS block.

PREVIOUS ROW

PREVIOUS ROW

Retrieves the previous row in a PROCESS ROWS block.

PROCESS ROWS

PROCESS ROWS [ tabfile.]tablename
   [ INDEXED BY indexname]
   [ CLOSETABLE num_val ]
   [ COUNT = total [,increment [,start ]]]
   [ ONETIME ]
   [ REVERSE]
   [ SAMPLE= fraction [,seed]]
   [ UPDATE | TUPDATE]
   [ 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) ]

Defines a row processing block for the specified table. The commands within the block (that is terminated with END ROW) are executed once for each row accessed. If a tabfile name is not specified, the default tabfile is used.

INDEXED BY Names the index to use. If this is not specified, the records are read sequentially as stored on the table.
CLOSETABLE Specifies whether the table is closed when the block is exited. A value of 0 (zero) or undefined leaves the table open and is the default. Any other value closes the table. When a table is closed, the memory used to hold the table is released for other use. Unless memory problems are encountered, avoid using this option. See also the CLOSE TABLE command.
COUNT Specifies the number of rows to retrieve. The values for total, increment and start are integer constants.
total
Specifies the maximum number of rows to retrieve. If more rows are requested than are available, the program retrieves all that exist. e.g. To process the first 5 rows in the table:
PROCESS ROWS TRIALTF.TABLE1 COUNT = 5
increment
Specifies the "skipping factor" for retrieving rows. An increment of 3 retrieves every third row. The default increment is 1 (one). e.g. To access a total of 5 rows, retrieving every tenth row:
PROCESS ROWS TRIALTF.TABLE1 COUNT = 5 , 10
start
Specifies the first row processed. The default start is 1, the first row. For example, 3 starts retrieving at the third row.
REVERSE Processes the specified rows of the table in reverse order.
SAMPLE=fraction Retrieves a random sample of rows from the table. The fraction specifies the portion of cases to select. The number specified is a decimal number between 0 (zero) and 1 (one). For each row, a random number between 0 and 1 is generated. If it is between 0 and the specified number, the row is retrieved. Each row is evaluated for inclusion independently, and therefore the sample may not be exactly the requested size particularly for tables with a small number of rows. Sampling is done before COUNT takes effect (i.e. "SAMPLE .5/ COUNT 2" retrieves the first 2 of a 50% sample). e.g. To process 25% of the rows in the table:
PROCESS ROWS TRIALTF.TABLE1 SAMPLE = .25
seed
Specifies the starting seed for the random number generator. A given seed guarantees that the same set of random numbers is generated. If a seed is not specified, a default seed is used.
PROCESS ROWS TRIALTF.TABLE1 SAMPLE =.25,13579

TUPDATE Specifies that the program can update data in the rows of this table. Use the PUT VARS command to update the row from local variables. TUPDATE need not be specified on the PROCESS ROWS command if it has already been specified on the routine command. UPDATE is a synonym for TUPDATE.
ONETIME Forces the PROCESS ROW block to be entered at least once, even if no rows within the specified range exist. If no rows exist, without this keyword, the block is skipped. The values of the row variables are set to undefined if the block is executed and no rows exist.
value list A list of values expressed as constants, variable names or array references. Each element in the list represents a value for an index key field. The values are matched with values of keyfield variables in the order defined for the index.
The value list may omit lower level keys. If a key is omitted, no lower keys can be specified. During execution, if a value is undefined or missing, the value list is treated as if it were terminated with the keyfield previous to the undefined value. N.B. This differs from the behaviour in version 2.n. of the software where an execution warning was reported and the block skipped.
AFTER Specifies the key value to start processing at but not to include. This selects rows whose key value is greater than the key specified by the value list. Specify THRU or UNTIL to select a range of keys.
FROM Specifies the key value to start processing and to include. This selects rows whose key value is greater than or equal to the key specified by the value list. Specify THRU or UNTIL to select a range of keys.
THRU Specifies the key value to process up to and to include in the retrieved subset. This selects rows whose key value is less than or equal to the key specified by the value list. Specify AFTER or FROM to specify a beginning row for processing.
UNTIL Specifies the key value to process up to but not to include in the retrieved subset. This selects rows whose key value is less than the key specified by the value list. Specify AFTER or FROM to specify a beginning row 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.

Using Indexes

The keywords AFTER, FROM, THRU, UNTIL and VIA specify a subset of rows by specifying values of the keyfields of the table.

If an index has been defined for the table and is referenced on the INDEXED BY clause, each row is identified by its key. The key is a composite of the values of the key fields as defined for the index.

VisualPQL locates individual rows through the index that points to the location of a row within the tabfile. The value list specified with the keywords on PROCESS ROW supplies values of the keys that VisualPQL uses to perform an indexed search for the records. The values in the list are matched to values of index key variables in the rows being processed.

The order of the values determines the keyfields to which the values refer. The keyfields and their order are passed to the PROCESS ROW list from the index definition.

For example, consider a table called REVIEW that has variables called JOBCODE, BOSSNAME and RATING and an index defined as:

CREATE UNIQUE INDEX REVIDX ON REVIEW (JOBCODE,BOSSNAME)

A PROCESS ROW to select those records of an employee who was reviewed for job 3 by Supervisor Jones might be as follows. The retrieval translates the values 3 and JONES as being values for keyfieldsJOBCODE and BOSSNAME:

PROCESS ROW REVIEW INDEXED BY REVIDX VIA (3,'JONES')

If multiple keyfields are defined for the table index, leading keyfields must be specified. Trailing keyfields can be omitted, but intervening keyfields must be specified. If any entries in the list are out of range or contain missing or undefined values, the valid portion of the list up to the first undefined value is used. For example, if A, B, and C represent the keyfields on an index, then:

VIA (A, B, C)   is legal
VIA (A)         is legal
VIA (A, B)      is legal
VIA (,,C)       is invalid, needs A and B
VIA (A,,C)      is invalid, needs B
VIA (, B, C)    is invalid, needs A

ROW IS

[ OLD | NEW ] ROW IS [tabfile_name.]table_name
                     [ INDEXED BY index_name (value list) ]
                     [ TUPDATE ]
                     [ CLOSETABLE num_value ]
                     [ AT (block,pos) ]

The ROW IS commands access a single row from the specified table. In update mode rows can be modified or created. Specify update mode with the TUPDATE keyword on the ROW IS or on the routine command.

tabfile_name.table name Specifies the table to access. This table must be connected at compile time and at run time.
ROW IS Accesses the row specified by the index key specified on the key field value list. This value list is a list of values matched with key fields of the index named on the INDEXED BY clause.
When in update mode, a new row is created if it does not exist. When not in update mode and when an index is not specified, the first record in the table is accessed.
OLD ROW IS Accesses the first row of the table or the row specified by the INDEXED BY clause and its key field value list. If the specified row does not exist, the block is skipped. A new row is never created.
NEW ROW IS Creates a new row. NEW ROW IS is only allowed in update mode. If the index specified is a unique index, the block is skipped if the row exists. If the index is not unique or if an INDEXED BY clause is not used, a new row is created if not restricted by another unique index on the table.
TUPDATE Allows the table to be updated. This keyword is required on ROW IS blocks that update the table if TUPDATE is not specified on the PROGRAM or RETRIEVAL command. UPDATE is a synonym.
INDEXED BY Names the index used for accessing rows.

CLOSETABLE Specifies whether the table is closed when the block is exited. A value of 0 (zero) or undefined leaves the table open and is the default. Any other value closes the table. See also the CLOSE TABLE command.

AT (block,pos) The AT can only be used with the OLD ROW IS construct. It re-retrieves a row that was previously retrieved from the saved block and position. These can be got when the row is initially retrieved with the SYSTEM functions 18 and 19. This allows the program to exit a block and find the row again even where duplicate keys are allowed in the index.

`SYSTEM(27)`	Returns a 1 if the last `ROW IS, NEW ROW IS`, or `OLD ROW IS` block was executed. It returns 0 (zero) if the last `ROW IS` block was not executed.
`SYSTEM(28)`	Returns a 1 if the last `ROW IS` or `NEW ROW IS` block created a new row. It returns 0 (zero) if the block did not create a new row in the table.

`INDEXED BY`	Names the index to use. If this is not specified, the records are read sequentially as stored on the table.
`CLOSETABLE`	Specifies whether the table is closed when the block is exited. A value of 0 (zero) or undefined leaves the table open and is the default. Any other value closes the table. When a table is closed, the memory used to hold the table is released for other use. Unless memory problems are encountered, avoid using this option. See also the `CLOSE TABLE` command.
`COUNT`	Specifies the number of rows to retrieve. The values for total, increment and start are integer constants. `total` Specifies the maximum number of rows to retrieve. If more rows are requested than are available, the program retrieves all that exist. e.g. To process the first 5 rows in the table: PROCESS ROWS TRIALTF.TABLE1 COUNT = 5 `increment` Specifies the "skipping factor" for retrieving rows. An increment of 3 retrieves every third row. The default increment is 1 (one). e.g. To access a total of 5 rows, retrieving every tenth row: PROCESS ROWS TRIALTF.TABLE1 COUNT = 5 , 10 `start` Specifies the first row processed. The default start is 1, the first row. For example, 3 starts retrieving at the third row.
`REVERSE`	Processes the specified rows of the table in reverse order.
`SAMPLE=fraction`	Retrieves a random sample of rows from the table. The fraction specifies the portion of cases to select. The number specified is a decimal number between 0 (zero) and 1 (one). For each row, a random number between 0 and 1 is generated. If it is between 0 and the specified number, the row is retrieved. Each row is evaluated for inclusion independently, and therefore the sample may not be exactly the requested size particularly for tables with a small number of rows. Sampling is done before `COUNT` takes effect (i.e. "`SAMPLE .5/ COUNT 2`" retrieves the first 2 of a 50% sample). e.g. To process 25% of the rows in the table: PROCESS ROWS TRIALTF.TABLE1 SAMPLE = .25 `seed` Specifies the starting seed for the random number generator. A given seed guarantees that the same set of random numbers is generated. If a seed is not specified, a default seed is used. PROCESS ROWS TRIALTF.TABLE1 SAMPLE =.25,13579
`TUPDATE`	Specifies that the program can update data in the rows of this table. Use the `PUT VARS` command to update the row from local variables. `TUPDATE` need not be specified on the `PROCESS ROWS` command if it has already been specified on the routine command. `UPDATE` is a synonym for `TUPDATE`.
`ONETIME`	Forces the `PROCESS ROW` block to be entered at least once, even if no rows within the specified range exist. If no rows exist, without this keyword, the block is skipped. The values of the row variables are set to undefined if the block is executed and no rows exist.
`value list`	A list of values expressed as constants, variable names or array references. Each element in the list represents a value for an index key field. The values are matched with values of keyfield variables in the order defined for the index. The value list may omit lower level keys. If a key is omitted, no lower keys can be specified. During execution, if a value is undefined or missing, the value list is treated as if it were terminated with the keyfield previous to the undefined value. N.B. This differs from the behaviour in version 2.n. of the software where an execution warning was reported and the block skipped.
`AFTER`	Specifies the key value to start processing at but not to include. This selects rows whose key value is greater than the key specified by the value list. Specify `THRU` or `UNTIL` to select a range of keys.
`FROM`	Specifies the key value to start processing and to include. This selects rows whose key value is greater than or equal to the key specified by the value list. Specify `THRU` or `UNTIL` to select a range of keys.
`THRU`	Specifies the key value to process up to and to include in the retrieved subset. This selects rows whose key value is less than or equal to the key specified by the value list. Specify `AFTER` or `FROM` to specify a beginning row for processing.
`UNTIL`	Specifies the key value to process up to but not to include in the retrieved subset. This selects rows whose key value is less than the key specified by the value list. Specify `AFTER` or `FROM` to specify a beginning row 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`.

`tabfile_name.table name`	Specifies the table to access. This table must be connected at compile time and at run time.
`ROW IS`	Accesses the row specified by the index key specified on the key field value list. This value list is a list of values matched with key fields of the index named on the `INDEXED BY` clause. When in update mode, a new row is created if it does not exist. When not in update mode and when an index is not specified, the first record in the table is accessed.
`OLD ROW IS`	Accesses the first row of the table or the row specified by the `INDEXED BY` clause and its key field value list. If the specified row does not exist, the block is skipped. A new row is never created.
`NEW ROW IS`	Creates a new row. `NEW ROW IS` is only allowed in update mode. If the index specified is a unique index, the block is skipped if the row exists. If the index is not unique or if an `INDEXED BY` clause is not used, a new row is created if not restricted by another unique index on the table.
`TUPDATE`	Allows the table to be updated. This keyword is required on `ROW IS` blocks that update the table if `TUPDATE` is not specified on the `PROGRAM` or `RETRIEVAL` command. `UPDATE` is a synonym.
`INDEXED BY`	Names the index used for accessing rows.
`CLOSETABLE`	Specifies whether the table is closed when the block is exited. A value of 0 (zero) or undefined leaves the table open and is the default. Any other value closes the table. See also the `CLOSE TABLE` command.
`AT (block,pos)`	The `AT` can only be used with the `OLD ROW IS` construct. It re-retrieves a row that was previously retrieved from the saved block and position. These can be got when the row is initially retrieved with the `SYSTEM` functions 18 and 19. This allows the program to exit a block and find the row again even where duplicate keys are allowed in the index.