Chapter 57 SQL command INTO

A SELECT clause that specifies that the selected value is stored in the host variable.

outline

INTO :hostvar1 [,:hostvar2]...

parameter

• : hostvar1 - the output host variable declared in the host language.
  When specified in the INTO clause, the variable name is preceded by a colon (:).
  Host variables can be local variables (non subscript or subscript) or object properties.
  You can specify multiple variables as a comma separated list, a single subscript array variable, or a combination of a comma separated list and a single subscript array variable.

describe

The INTO clause and host variables are used only in embedded SQL. They are not used in dynamic SQL. In dynamic SQL, the% SQL.Statement class provides similar functionality for output variables. Specifying the INTO clause in a SELECT query processed through ODBC, JDBC, or dynamic SQL causes an SQLCODE-422 error.

The INTO clause can be used in SELECT, DECLARE, or FETCH statements. The INTO clause is the same for all three statements; The examples on this page refer to the SELECT statement.

The INTO clause uses the values retrieved (or calculated) in the SELECT-ITEM list to set the corresponding output host variables so that these returned data values can be used in ObjectScript. In SELECT, the optional INTO clause appears after the SELECT-ITEM list and before the FROM clause.

Note: when compiling embedded SQL, the output host variable will be initialized to an empty string. This prevents < unclared > errors during execution. Therefore, host variables contain meaningful values only when SQLCODE=0. Always check SQLCODE before using output host variable values. Do not use these variable values when SQLCODE=100 or SQLCODE is negative.

Host variable

Host variables can only contain a single value. Therefore, SELECT in embedded SQL retrieves only one row of data. This defaults to the first row of the table. Of course, you can retrieve data from other rows of the table by using the WHERE condition to restrict eligible rows.

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

• The host variable list consists of a comma separated host variable list, and each option corresponds to a host variable list.
• Host variable array, consisting of a single subscript host variable.

Note: if the host language declares the data type of the variable, all host variables must be declared in the host language before calling the SELECT statement. The data type of the retrieved field value must match the host variable declaration. (ObjectScript does not declare the data type of a variable.)

Use host variable list

When specifying a list of host variables in the INTO clause, the following rules apply:

• 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 is different, SQL will return a cardinality mismatch error.
• The selected fields and host variables match in relative positions. Therefore, the corresponding items in the two lists must appear in the same order.
• The host variables listed can be non subscript variables or any combination of subscript variables.
• The host variables listed can return aggregate values (such as count, sum, or average) or function values.
• The host variables listed can return% CLASSNAME and% TABLENAME values.
• The host variables listed can return field values FROM a SELECT involving multiple tables or FROM a SELECT without a FROM clause.

The following example selects four fields from a list of four host variables. The host variable in this example has a subscript:

ClassMethod Into()
{
	&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 $d(mydata(i)) {
				w "field ",i," = ",mydata(i),! 
			}
		} 
	} else {
		w "SQLCODE=",SQLCODE,! 
	}
}

Use host variable array

The host variable array uses a single subscript variable to contain all selected field values. This array is populated according to the order defined by the fields in the table, not the order of the fields in the selection list.

When using host variable arrays in the INTO clause, the following rules apply:

• The field specified in the selection list is selected into the subscript of a single host variable. Therefore, it is not necessary to match the number of items in the selection list with the host variable COUNT.
• The host variable subscript is filled by the corresponding field position in the table definition. For example, the sixth field defined in the table definition corresponds to mydata(6). All subscripts that do not correspond to the specified selection have not been defined. The order of items in a selection has no effect on how subscripts are filled.
• Host variable arrays can only return field values from a single table.
• Host variable array can only return field values. It cannot return aggregate values such as COUNT, SUM, or average, function values, or% CLASSNAME or% TABLENAME values. (you can return these parameters by specifying the host variable parameters that combine the host variable list item with the host variable array.)

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

ClassMethod Into1()
{
	&sql(
		SELECT %ID,Home_City,Name,SSN
		INTO :mydata()   
		FROM Sample.Person
		WHERE Home_State='MA' 
	)
	if SQLCODE = 0 {
		for i = 1 : 1 : 15 { 
			if $d(mydata(i)) {
				w "field ",i," = ",mydata(i),! 
			}
		} 
	} else {
		w "SQLCODE=",SQLCODE,! 
	}
}

Host variables that return field values

The following embedded SQL example selects three fields from the first record of the table (embedded SQL always retrieves a single record) and uses INTO to set three corresponding non subscript host variables. These variables are then used by the ObjectScript write command. Testing SQLCODE variables immediately upon return from embedded SQL is considered a good programming practice. If SQLCODE is not equal to 0, the value of the output host variable is initialized to an empty string.

ClassMethod Into2()
{
	&sql(
		SELECT Home_State, Name, Age 
		INTO :state, :name, :age   
		FROM Sample.Person
	)
	if SQLCODE=0 {
		w !,"  Name=",name
		w !,"  Age=",age
		w !,"  Home State=",state 
	} else {
		w !,"SQL error ",SQLCODE  
	}
}

The following embedded SQL example returns the field values in the row generated by the join of two tables. When returning fields from multiple tables, you must use the host variable list:

ClassMethod Into3()
{
	&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 {
		w ptname," = ",name(1),!
		w etname," = ",title,!
		w etname," = ",name(2) 
	} else {
		w !,"SQL error ",SQLCODE  
	}
}

Host variables that return literal and aggregate values

Since the output host variable is only valid when SQLCODE=0, it is very important to avoid using the query results that issue SQLCODE=100 (the query does not return table data). SQLCODE=100 defaults all output host variables to empty strings, including the returned text and count aggregation.

The following embedded SQL example passes a host variable (TODAY) to the SELECT statement, where the calculation result is the INTO clause variable VALUE(: TODAY). The host variable is passed to the program containing the host. This query does not reference table fields, so the FROM clause is not specified. 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 will define all output variables as the default empty string values, including those variables that are not table field values, such as Tomorrow.

ClassMethod Into4()
{
	s today = $h
	&sql(
		SELECT :today+1
		INTO :tomorrow 
	)
	if SQLCODE=0 {
		w !,"Tomorrow is: ",$ZDATE(tomorrow) 
	} else {
		w !,"SQL error ",SQLCODE  
	}
}

The following embedded SQL example returns aggregate values. It uses the COUNT aggregate function to COUNT the records in the table and AVG to average the salary field values. The INTO clause returns these values to ObjectScript as two subscript host variables.

Because both select items are aggregated, the program always issues SQLCODE=0 even if the specified table does not contain data. In this example, count(*)=0, AVG(Salary) is the default empty string.

ClassMethod Into5()
{
	&sql(
		SELECT COUNT(*),AVG(Salary)
		INTO :agg(1),:agg(2)
		FROM Sample.Employee)
	if SQLCODE = 0 {
		w !,"Total Employee records= ",agg(1)
		w !,"Average Employee salary= ",agg(2) 
	} elseif SQLCODE=100 {
		w !,"Total Employee records= ",agg(1) 
	} else {
		w !,"SQL error ",SQLCODE  
	}
}

The following embedded SQL example is the same as the previous example, except that it also returns a field value. Because SELECT-ITEMS includes a field value, the program can issue SQLCODE=100 when the specified table does not contain data. In this example, if SQLCODE=100, COUNT(*) is the default empty string instead of 0:

ClassMethod Into6()
{
	&sql(
		SELECT COUNT(*),AVG(Salary),Salary
		INTO :agg(1),:agg(2),:pay
		FROM Sample.Employee
	)
	if SQLCODE = 0 {
		w !,"Total Employee records= ",agg(1)
		w !,"Average Employee salary= ",agg(2)
		w !,"Sample Employee salary=",pay 
	} else {
		w !,"SQL error ",SQLCODE  
	}
}

Host variable array

The following two embedded SQL examples use an array of host variables to return 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 RowId (although it returns for Sample.Person); RowId is always field 1. Note that Sample.Person fields 4 and 9 can be empty, field 5 is not a data field (it refers to Sample.Address), and field 10 is hidden.

The first example returns a specified number of fields (FirstFld); Hidden and non data fields are included in this count, but are not displayed. When returning rows from a table that contains multiple fields, it is appropriate to use firstfld. Note that this example can return the field 0 as a parent reference. Sample.Person is not a child table, so tflds(0) is not defined:

ClassMethod Into7()
{
	&sql(
		SELECT *,%ID INTO :tflds()   
		FROM Sample.Person 
	)
	if SQLCODE = 0 {
		s firstflds = 14
		for i = 0 : 1 : firstflds { 
			if $d(tflds(i)) {
				w "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 the parent reference Field 0, because tflds(0) is undefined in Sample.Person, so a < undefined > error is generated:

ClassMethod Into8()
{
	&sql(
		SELECT *,%ID INTO :tflds()   
		FROM Sample.Person 
	)
	if SQLCODE=0 {
		s x = 1
		while x '="" {
			w "field ",x," = ",tflds(x),!
			s x= $ORDER(tflds(x)) 
		}
	} else { 
		w "SQLCODE error=",SQLCODE,! 
	}
}

The following embedded SQL example combines a comma separated list of host variables (for non field values) and an array of host variables (for field values):

ClassMethod Into9()
{
	&sql(
		SELECT %TABLENAME, Name, Age, AVG(Age)
		INTO :tname, :tflds(), :ageavg
		FROM Sample.Person
		WHERE Age > 50 
	)
	if SQLCODE = 0 {
		w "Table name is = ",tname,!
		for i = 0 : 1 : 25 { 
			if $d(tflds(i)) {
				w "field ",i," = ",tflds(i),! 
			}
		} 
		w "Average age is = ",ageavg,! 
	} else {
		w "SQLCODE=",SQLCODE,! 
	}
}