Caché SQL Reference
INTO
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

A SELECT clause that specifies the storing of selected values in host variables.
Synopsis
INTO :hostvar1 [,:hostvar2]...
Arguments
:hostvar1 A 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.
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.Statement class.
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 in the “Using Embedded SQL” chapter of Using Caché SQL.
The INTO clause uses the values retrieved (or calculated) in the SELECT select-item list to set corresponding 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.
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 the Host Variables section of the “Embedded SQL” chapter of Using Caché SQL.
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 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 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” in the “Using Embedded SQL” chapter of Using Caché SQL.
Host Variable List Examples
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 indeterminate.
   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 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 SQL query does not require a FROM clause.
   SET today=$HOROLOG
   &sql(SELECT :today+1
        INTO :tomorrow )
   IF SQLCODE=0 {
        WRITE !,"Tomorrow is: ",$ZDATE(tomorrow) }
   ELSE {
        WRITE !,"SQL error ",SQLCODE  }
 
For restrictions on the use of input and output host variable values, refer to the Host Variables section of the “Embedded SQL” chapter of Using Caché SQL.
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:
   WRITE !,"Counting the records"
   &sql(SELECT COUNT(*),AVG(Salary)
        INTO :agg(1),:agg(2)
        FROM Sample.Employee)
   IF SQLCODE=0 {
        WRITE !,"Total Eymployee records= ",agg(1)
        WRITE !,"Average Employee salary= ",agg(2) }
   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  }   
 
Host Variable Array Examples
The following two Embedded SQL examples uses 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