Host Variables

Host Variable Examples

In the following ObjectScript example, an Embedded SQL statement uses output host variables to return a name and home state address from an SQL query to ObjectScript:

   &sql(SELECT Name,Home_State
        INTO :CName,:CAddr
        FROM Sample.Person)
        IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
        ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
      WRITE !,"Name is: ",CName
      WRITE !,"State is: ",CAddr

The Embedded SQL uses an INTO clause that specifies the host variables :CName and :CAddr to return the selected customer’s name in the local variable CName, and home state in the local variable CAddr.

The following example performs the same operation, using subscripted local variables:

   &sql(SELECT Name,Home_State
        INTO :CInfo(1),:CInfo(2)
        FROM Sample.Person)
        IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
        ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
      WRITE !,"Name is: ",CInfo(1)
      WRITE !,"State is: ",CInfo(2)

These host variables are simple local variables with user-supplied subscripts (:CInfo(1)). However, if you omit the subscript (:CInfo()), InterSystems IRIS populates the host variable subscripted array using SqlColumnNumber, as described below.

In the following ObjectScript example, an Embedded SQL statement uses both input host variables (in the WHERE clause) and output host variables (in the INTO clause):

  SET minval = 10000
  SET maxval = 50000
  &sql(SELECT Name,Salary INTO :outname, :outsalary
       FROM MyApp.Employee
       WHERE Salary > :minval AND Salary < :maxval)
        IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
        ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
      WRITE !,"Name is: ",outname
      WRITE !,"Salary is: ",outsalary

The following example performs “light normalization” on an input host variable. Note that InterSystems IRIS treats the input variable value as a string and does not normalize it, but Embedded SQL normalizes this number to 65 to perform the equality comparison in the WHERE clause:

  SET x="+065.000"
  &sql(SELECT Name,Age
       INTO :a,:b
       FROM Sample.Person
       WHERE Age=:x)
        IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
        ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
  WRITE !,"Input value is: ",x
  WRITE !,"Name value is: ",a
  WRITE !,"Age value is: ",b

In the following ObjectScript example, an Embedded SQL statement uses object properties as host variables:

   &sql(SELECT Name, Title INTO :obj.Name, :obj.Title
        FROM MyApp.Employee
        WHERE %ID = :id )

In this case, obj must be a valid reference to an object that has mutable (that is, they can be modified) properties Name and Title. Note that if a query includes an INTO statement and no data is returned (that is, that SQLCODE is 100), then executing the query may result in the value of the host variable being modified.

Host Variable Subscripted by Column Number

If the FROM clause contains a single table, you can specify a subscripted host variable for fields selected from that table; for example, the local array :myvar(). The local array is populated by InterSystems IRIS, using each field’s SqlColumnNumber as the numeric subscript. Note that SqlColumnNumber is the column number in the table definition, not the select-list sequence. (You cannot use a subscripted host variable for fields of a view.)

A host variable array must be a local array that has its lowest level subscript omitted. Therefore, :myvar(), :myvar(5,), and :myvar(5,2,) are all valid host variable subscripted arrays.

• A host variable subscripted array may be used for input in an INSERT, UPDATE, or INSERT OR UPDATE statement VALUES clause. When used in an INSERT or UPDATE statement, a host variable array allows you to define which columns are being updated at runtime, rather than at compile time.

• A host variable subscripted array may be used for output in a SELECT or DECLARE statement INTO clause. Subscripted array usage in SELECT is shown in the examples that follow.

In the following example, the SELECT populates the Cdata array with the values of the specified fields. The elements of Cdata() correspond to the table column definition, not the SELECT elements. Therefore, the Name field is column 6, the Age field is column 2, and the date of birth (DOB) field is column 3 in Sample.Person:

   &sql(SELECT Name,Age,DOB
        INTO :Cdata()
        FROM Sample.Person)
          IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
          ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
      WRITE !,"Name is: ",Cdata(6)
      WRITE !,"Age is: ",Cdata(2)
      WRITE !,"DOB is: ",$ZDATE(Cdata(3),1)

The following example uses a subscripted array host variable to return all of the field values of a row:

   &sql(SELECT * INTO :Allfields()
        FROM Sample.Person)
          IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
          ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
      SET x=1
      WHILE x '="" {
      WRITE !,x," field is ",Allfields(x)
      SET x=$ORDER(Allfields(x))
      }

Note that this WHILE loop is incremented using $ORDER rather than a simple x=x+1. This is because in many tables (such as Sample.Person) there may be hidden columns. These cause the column number sequence to be discontinuous.

If the SELECT list contains items that are not fields from that table, such as expressions or arrow-syntax fields, the INTO clause must also contain comma-separated non-array host variables. The following example combines a subscripted array host variable to return values that correspond to defined table columns, and host variables to return values that do not correspond to defined table columns:

   &sql(SELECT Name,Home_City,{fn NOW},Age,($HOROLOG-DOB)/365.25,Home_State
        INTO :Allfields(),:timestmp('now'),:exactage
        FROM Sample.Person)
          IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
          ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
      SET x=$ORDER(Allfields(""))
      WHILE x '="" {
      WRITE !,x," field is ",Allfields(x)
      SET x=$ORDER(Allfields(x)) }
      WRITE !,"date & time now is ",timestmp("now")
      WRITE !,"exact age is ",exactage

Note that the non-array host variables must match the non-column SELECT items in number and sequence.

The use of a host variable as a subscripted array is subject to the following restrictions:

• A subscripted list can only be used when selecting fields from a single table in the FROM clause. This is because when selecting fields from multiple tables, the SqlColumnNumber values may conflict.

• A subscripted list can only be used when selecting table fields. It cannot be used for expressions or aggregate fields. This is because these select-list items do not have an SqlColumnNumber value.

For further details on using a host variable array, see INTO Clause.

NULL and Undefined Host Variables

If you specify an input host variable that is not defined, Embedded SQL treats its value as NULL.

   NEW x
   &sql(SELECT Home_State,:x
        INTO :a,:b
        FROM Sample.Person)
          IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
          ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
     WRITE !,"The length of Home_State is: ",$LENGTH(a)
     WRITE !,"The length of x is: ",$LENGTH(b)

The SQL NULL is equivalent to the ObjectScript "" string (a zero-length string).

When Embedded SQL is compiled, all INTO clause output host variables are defined as the ObjectScript "" string (a zero-length string). If you output a NULL to a host variable, Embedded SQL treats its value as the ObjectScript "" string (a zero-length string). For example, some records in Sample.Person have a NULL Spouse field. After executing this query:

 &sql(SELECT Name,Spouse
    INTO :name, :spouse
    FROM Sample.Person
    WHERE Spouse IS NULL)
        IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
        ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
    WRITE !,"Name: ",name," of length ",$LENGTH(name)," defined: ",$DATA(name)
    WRITE !,"Spouse: ",spouse," of length ",$LENGTH(spouse)," defined: ",$DATA(spouse)

The host variable, spouse, will be set to "" (a zero-length string) to indicate a NULL value. Therefore, the ObjectScript $DATA function cannot be used to determine if an SQL field is NULL. $DATA returns true (variable is defined) when passed an output host variable for an SQL field with a NULL value.

In the rare case that a table field contains an SQL zero-length string (''), such as if an application explicitly set the field to an SQL '' string, the host variable will contain the special marker value, $CHAR(0) (a string of length 1, containing only a single, ASCII 0 character), which is the ObjectScript representation for the SQL zero-length string. Use of SQL zero-length strings is strongly discouraged.

The following example compares host variables output from an SQL NULL and an SQL zero-length string:

 &sql(SELECT '',Spouse
    INTO :zls, :spouse
    FROM Sample.Person
    WHERE Spouse IS NULL)
        IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
        ELSEIF SQLCODE=100 {WRITE "Query returns no results"  QUIT}
    WRITE "In ObjectScript"
    WRITE !,"ZLS is of length ",$LENGTH(zls)," defined: ",$DATA(zls)
       /* Length=1, Defined=1 */
    WRITE !,"NULL is of length ",$LENGTH(spouse)," defined: ",$DATA(spouse)
       /* Length=0, Defined=1 */

Note that this host variable NULL behavior is only true within server-based queries (Embedded SQL and Dynamic SQL). Within ODBC and JDBC, NULL values are explicitly specified using the ODBC or JDBC interface.

Validity of Host Variables

• Input host variables are never modified by Embedded SQL.

• Output host variables are only reliably valid after Embedded SQL when SQLCODE = 0.

For example, the following use of OutVal is not reliably valid:

InvalidExample
   SET InVal = "1234"
   SET OutVal = "None"
   &sql(SELECT Name
        INTO :OutVal
        FROM Sample.Person
        WHERE %ID=:InVal)
   IF OutVal="None" {           ; Improper Use 
   WRITE !,"No data returned"
   WRITE !,"SQLCODE=",SQLCODE }
   ELSE {
   WRITE !,"Name is: ",OutVal }

The value of OutVal set before invoking Embedded SQL should not be referenced by the IF command after returning from Embedded SQL.

Instead, you should code this example as follows, using the SQLCODE variable:

ValidExample
   SET InVal = "1234"
   &sql(SELECT Name
        INTO :OutVal
        FROM Sample.Person
        WHERE %ID=:InVal)
   IF SQLCODE'=0 { SET OutVal="None" 
      IF OutVal="None" {
      WRITE !,"No data returned"
      WRITE !,"SQLCODE=",SQLCODE } }
   ELSE {
   WRITE !,"Name is: ",OutVal }

The Embedded SQL sets the SQLCODE variable to 0 to indicate the successful retrieval of an output row. An SQLCODE value of 100 indicates that no row was found that matches the SELECT criteria. An SQLCODE negative number value indicates a SQL error condition.

Host Variables and Procedure Blocks

If your Embedded SQL is within a procedure block, all input and output host variables must be public. This can be done by declaring them in the PUBLIC section at the beginning of the procedure block, or by naming them with an initial % character (which automatically makes them public). Note, however, that a user-defined % host variable is automatically public, but is not automatically NEWed. It is the user’s responsibility to perform a NEW on such variables, as desired. Some SQL % variables, such as %ROWCOUNT, %ROWID, and %msg, are both automatically public and automatically NEWed, as described in Embedded SQL Variables. You must declare SQLCODE as public. For further details on the SQLCODE variable, see Embedded SQL Variables.

In the following procedure block example, the host variables zip, city, and state, as well as the SQLCODE variable are declared as PUBLIC. The SQL system variables %ROWCOUNT, %ROWID, and %msg are already public, because their names begin with a % character. The procedure code then performs a NEW on SQLCODE, the other SQL system variables, and the state local variable:

UpdateTest(zip,city)
  [SQLCODE,zip,city,state] PUBLIC {
  NEW SQLCODE,%ROWCOUNT,%ROWID,%msg,state
  SET state="MA"
  &sql(UPDATE Sample.Person
       SET Home_City = :city, Home_State = :state
       WHERE Home_Zip = :zip)
        IF SQLCODE<0 {WRITE "SQLCODE error ",SQLCODE," ",%msg  QUIT}
        QUIT %ROWCOUNT
  }