Setting Variables from the Data Server Responses

All of the standard syntax for setting variables is available for use within Query. In addition, Query incorporates extra syntax for setting variables to data values returned from a data server.

To set a variable to the next row of data resulting from a data server query, use the following syntax:

VAR varname EXTERnal GET token NEXT

Here, token is the token for the communication channel which was supplied by a successful EXTERNAL OPEN command (and stored in the token_var variable). The variable varname will be interpreted automatically as an array variable and each data item will be stored in a separate element of the array. (The PML SPLIT command is not required here; splitting is carried out automatically.)

Note:
No data field should be longer than 120 characters, since this is the maximum permitted length that can be stored in a variable. If a data field exceeds 120 characters, nothing will be returned by the query.

As an example, the querying command:

EXTERNAL SEND $!!MAINT ’select refno, servint, lastserv

from maintdata’ VAR !datarow EXTERNAL GET $!!MAINT NEXT

This might return the following results:

Repeating the command will retrieve the next data record, which will progressively overwrite the current settings of the array element elements.

VAR !datarow EXTERNAL GET $!!MAINT NEXT

For example:

And so on for subsequent data records.

If the newly read record has less completed fields than the preceding record, some elements of the array variable will remain with their previous settings unchanged.

The User can control the writing of data to an array variable explicitly by using the variable-setting syntax. For example, to read in a record such that its first field is stored in element 10 of the array variable, rather than starting from element 1 by default, the User could use the syntax:

VAR !datarow[10] EXTERNAL GET $!!MAINT NEXT

To read a record and append the resulting data to the array variable, rather than overwriting the current settings of the array elements, the User could use the syntax:

VAR !datarow APPEND EXTERNAL GET $!!MAINT NEXT

The most convenient way to retrieve multiple data records is usually to incorporate the NEXT command into a PML ‘infinite’ DO loop construct, reading one data record during each cycle of the loop. When the last record has been read in, the next cycle of the loop will generate the message:

(79, 10) No more data

This can be dealt with by using the PML HANDLE facility. For example:

EXTERNAL SEND $!!MAINT ’select * from maintdata’

DO

VAR !datarow EXTERNAL GET $!!MAINT NEXT

HANDLE (79, 10)

BREAK

ENDHANDLE

PML commands

...

ENDDO

To set the column headings returned by a data server query in an array variable, use either version of the following syntax:

VAR varname EXTERnal GET token RESult

VAR varname EXTERnal GET token HEADer

These commands always return the first row of the result of the last request sent to the data server identified by token (typically, but not necessarily, header information resulting from an SQL DB SELECT command).

For example, the querying command:

EXTERNAL SEND $!!MAINT ’select * from maintdata’

VAR !header EXTERNAL GET $!!MAINT RESULT

This might return the following results:

Note:
As with the data fields, no heading should be longer than 120 characters. If a heading exceeds 120 characters, nothing will be returned by the query.