Using a Host Variable Array

A host variable array uses a single subscripted variable to contain all of the selected field values. This array is populated according to the order of field definition in the table, not the order of fields in the select-item list.

The following rules apply when using a host variable array in the INTO clause:

The fields specified in the select-item list are selected into subscripts of a single host variable. Therefore, you do not have to match the number of items in the select-item list with the host variable count.
The host variable subscripts are populated by the corresponding field position in the table definition. For example, the 6th field, as defined in the table definition, corresponds to mydata(6). All subscripts that do not correspond to a specified select-item remain undefined. The order of the items in the select-item has no effect on how subscripts are populated.
A host variable array can only return field values from a single table.
A host variable array can only return field values. It cannot return an aggregate value (such as a count, sum, or average), a function value, or a %CLASSNAME or %TABLENAME value. (You can return these by specifying a host variable argument that combines host variable list items with the host variable array.)
If the SELECT query does not return SQLCODE=0, the host variable array is unchanged. No data in the array is changed, added, or removed.

The following example selects four fields into a host variable array:

  &sql(SELECT %ID,Home_City,Name,SSN
        INTO :mydata()   
        FROM Sample.Person
        WHERE Home_State='MA' )
   IF SQLCODE=0 {
     FOR i=0:1:15 { 
       IF $DATA(mydata(i)) {
       WRITE "field ",i," = ",mydata(i),! }
     } }
   ELSE {WRITE "SQLCODE=",SQLCODE,! }

For further examples refer to Host Variable Array Examples, below.

For further details, refer to Host Variable as a Subscripted Array.

Using a Host Variable List