Skip to main content

INTO (SQL)

A SELECT clause that specifies the storing of selected values in host variables.

Synopsis

INTO :hostvar1 [,:hostvar2]...

Description

The INTO clause and host variables are only used in Embedded SQL. They are not used in Dynamic SQL. In Dynamic SQL, similar functionality for output variables is provided by the %SQL.StatementOpens in a new tab class. Specifying an INTO clause in a SELECT query processed via ODBC, JDBC, or Dynamic SQL results in an SQLCODE -422 error.

An INTO clause can be used in a SELECT, DECLARE, or FETCH statement. The INTO clause is identical for all three statements; examples on this page all refer to the SELECT statement. For usage with DECLARE and FETCH, refer to SQL Cursors.

The INTO clause uses the values retrieved (or calculated) in the SELECT select-item list to set corresponding output host variables, making these returned data values available to ObjectScript. In a SELECT the optional INTO clause appears after the select-item list and before the FROM clause.

Caution:

Output host variables are initialized to the empty string when Embedded SQL is compiled. This prevents <UNDEFINED> errors at execution time. Therefore, host variables only contain meaningful values when SQLCODE=0. Always check SQLCODE before using an output host variable value. Do not use these variable values when SQLCODE=100 or when SQLCODE is a negative number.

Host Variables

A host variable can contain only a single value. Therefore, a SELECT in embedded SQL only retrieves one row of data. This defaults to the first row of the table. You can, of course, retrieve data from some other row of the table by limiting the eligible rows using a WHERE condition.

In embedded SQL you can return data from multiple rows by declaring a cursor and then issuing a FETCH for each successive row. The INTO clause host variables can be specified in the DECLARE query or specified in the FETCH.

INTO clause host variables can be specified in either of two ways (or a combination of both):

For important restrictions on the use of host variable values in the containing program, refer to Host Variables.

Note:

If the host language declares data types for variables, all host variables must be declared in the host language before invoking the SELECT statement. The data types of the retrieved field values must match the host variable declarations. (ObjectScript does not declare data types for variables.)

Using a Host Variable List

The following rules apply when you specify a host variable list in the INTO clause:

  • The number of host variables in the INTO clause must match the number of fields specified in the select-item list. If the number of selected fields and host variables differs, SQL returns a “cardinality mismatch” error.

  • Selected fields and host variables are matched by relative position. Therefore, the corresponding items in these two lists must appear in the same sequence.

  • The listed host variables may be any combination of unsubscripted or subscripted variables.

  • A listed host variable can return an aggregate value (such as a count, sum, or average) or a function value.

  • A listed host variable can return %CLASSNAME and %TABLENAME values.

  • Listed host variables can return field values from a SELECT involving multiple tables, or return values from a SELECT with no FROM clause.

The following example selects four fields into a list of four host variables. The host variables in this example are subscripted:

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

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

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.

Arguments

:hostvar1

An output host variable that has been declared in the host language. When specified in an INTO clause, the variable name is preceded by a colon (:). A host variable can be a local variable (unsubscripted or subscripted) or an object property. You can specify multiple variables as a comma-separated list, as a single subscripted array variable, or a combination of a comma-separated list and a single subscripted array variable.

Host Variable Returning Field Values

The following Embedded SQL example selects three fields from the first record in the table (Embedded SQL always retrieves a single record), and uses INTO to set three corresponding unsubscripted host variables. These variables are then used by the ObjectScript WRITE commands. It is considered good program practice to immediately test the SQLCODE variable upon returning from Embedded SQL. If SQLCODE is not equal to 0, the values of output host variables are initialized to the empty string.

   WRITE !,"Going to get the first record"
   &sql(SELECT Home_State, Name, Age 
        INTO :state, :name, :age   
        FROM Sample.Person)
   IF SQLCODE=0 {
     WRITE !,"  Name=",name
     WRITE !,"  Age=",age
     WRITE !,"  Home State=",state }
   ELSE {
     WRITE !,"SQL error ",SQLCODE  }

The following Embedded SQL example returns field values from a row resulting from the join of two tables. You must use a host variable list when returning fields from more than one table:

    &sql(SELECT P.Name,E.Title,E.Name,P.%TABLENAME,E.%TABLENAME 
        INTO :name(1),:title,:name(2),:ptname,:etname
        FROM Sample.Person AS P LEFT JOIN
             Sample.Employee AS E ON E.Name %STARTSWITH 'B'
        WHERE P.Name %STARTSWITH 'A')
   IF SQLCODE=0 {
        WRITE ptname," = ",name(1),!
        WRITE etname," = ",title,!
        WRITE etname," = ",name(2) }
   ELSE {
        WRITE !,"SQL error ",SQLCODE  }   

For restrictions on the use of input and output host variable values, refer to Host Variables.

Host Variables Returning Literal and Aggregate Values

Because output host variables are only valid when SQLCODE=0, it is important to avoid using the results of a query that issues an SQLCODE=100 (query returns no table data). SQLCODE=100 defaults all output host variables to the empty string, including returned literals and COUNT aggregates.

The following Embedded SQL example passes a host variable (today) into the SELECT statement, where a calculation results in the INTO clause variable value (:tomorrow). This host variable is passed out to the containing program. This query does not reference table fields and therefore does not specify a FROM clause. An Embedded SQL query without a FROM clause cannot issue SQLCODE=100. An Embedded SQL query with a FROM clause can issue SQLCODE=100, which would define all output variables to default null string values, including those such as :tomorrow that are not table field values.

   SET today=$HOROLOG
   &sql(SELECT :today+1
        INTO :tomorrow )
   IF SQLCODE=0 {
        WRITE !,"Tomorrow is: ",$ZDATE(tomorrow) }
   ELSE {
        WRITE !,"SQL error ",SQLCODE  }

The following Embedded SQL example returns aggregate values. It uses the COUNT aggregate function to count the records in a table and AVG to average the Salary field values. The INTO clause returns these values to ObjectScript as two subscripted host variables.

Because both select-items are aggregates, this program always issues SQLCODE=0, even when the specified table contains no data. In this case, COUNT(*)=0 and AVG(Salary) is the default empty string.

   WRITE !,"Counting the records"
   &sql(SELECT COUNT(*),AVG(Salary)
        INTO :agg(1),:agg(2)
        FROM Sample.Employee)
   IF SQLCODE=0 {
        WRITE !,"Total Employee records= ",agg(1)
        WRITE !,"Average Employee salary= ",agg(2) }
   ELSEIF SQLCODE=100 {
        WRITE !,"Total Employee records= ",agg(1) }
   ELSE {
        WRITE !,"SQL error ",SQLCODE  }   

The following Embedded SQL example is the same as the previous example, except it also returns a field value. Because the select-items includes a field value, this program can issue SQLCODE=100 when the specified table contains no data. In this example, if SQLCODE=100, COUNT(*) is the default empty string, not 0:

   WRITE !,"Counting the records"
   &sql(SELECT COUNT(*),AVG(Salary),Salary
        INTO :agg(1),:agg(2),:pay
        FROM Sample.Employee)
   IF SQLCODE=0 {
        WRITE !,"Total Employee records= ",agg(1)
        WRITE !,"Average Employee salary= ",agg(2)
        WRITE !,"Sample Employee salary=",pay }
   ELSE {
        WRITE !,"SQL error ",SQLCODE  }   

For restrictions on the use of input and output host variable values, refer to Host Variables.

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,! }

See Also

FeedbackOpens in a new tab