Setting Variables from the Data Server Responses

Query Reference Manual

Communicating Using Query : 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 syntax


VAR varname EXTERnal GET token NEXT

where 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

might return the following results:


$!datarow[1]	C1101	(first value of field refno)
$!datarow[2]	182	(first value of field servint)
$!datarow[3]	22-JAN-10	(first value of field lastserv)

Repeating the command

VAR !datarow EXTERNAL GET $!!MAINT NEXT

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


$!datarow[1]	E1201	(second value of field refno)
$!datarow[2]	31	(second value of field servint)
$!datarow[3]	07-JAN-10	(second value of field lastserv)

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

and so on.

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

which 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 database SELECT command).

For example, the querying command

EXTERNAL SEND $!!MAINT ’select * from maintdata’
VAR !header EXTERNAL GET $!!MAINT RESULT

might return the following results:


$!header[1]	refno
$!header[2]	servint
$!header[3]	lastserv

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.