Host Variable Array

Host Variable Array

The following two Embedded SQL examples use a host variable array to return the non-hidden data field values from a row. In these examples %ID is specified in the select-item list, because, by default, SELECT * does not return the RowId (though it does for Sample.Person); the RowId is always field 1. Note in Sample.Person, fields 4 and 9 can take NULL, field 5 is not a data field (it references Sample.Address), and field 10 is hidden.

The first example returns a specified number of fields (firstflds); hidden and non-data fields are included in this count, though not displayed. Using firstflds would be appropriate when returning a row from a table with many fields. Note that this example can return Field 0, which is the parent reference. Sample.Person is not a child table, so tflds(0) is undefined:

  &sql(SELECT *,%ID INTO :tflds()   
        FROM Sample.Person )
   IF SQLCODE=0 {
     SET firstflds=14
     FOR i=0:1:firstflds { 
       IF $DATA(tflds(i)) {
       WRITE "field ",i," = ",tflds(i),! }
     } }
   ELSE {WRITE "SQLCODE error=",SQLCODE,! }

The second example returns all the non-hidden data fields in Sample.Person. Note that this example does not attempt to return Field 0, the parent reference, because in Sample.Person tflds(0) is undefined, and would therefore generate an <UNDEFINED> error:

  &sql(SELECT *,%ID INTO :tflds()   
        FROM Sample.Person )
  IF SQLCODE=0 {
      SET x=1
      WHILE x '="" {
      WRITE "field ",x," = ",tflds(x),!
      SET x=$ORDER(tflds(x)) }
      }
  ELSE { WRITE "SQLCODE error=",SQLCODE,! }

The following Embedded SQL example combines a comma-separated host variable list (for non-field values) and a host variable array (for field values):

  &sql(SELECT %TABLENAME,Name,Age,AVG(Age)
        INTO :tname,:tflds(),:ageavg
        FROM Sample.Person
        WHERE Age > 50 )
   IF SQLCODE=0 {
     WRITE "Table name is = ",tname,!
     FOR i=0:1:25 { 
       IF $DATA(tflds(i)) {
       WRITE "field ",i," = ",tflds(i),! }
     } 
       WRITE "Average age is = ",ageavg,! }
   ELSE {WRITE "SQLCODE=",SQLCODE,! }