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.

1. 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 = 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.

     You 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

2. To read a record and append the resulting data to the array variable, rather than overwriting the current settings of the array elements, 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

3. 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.