INTO Clause--Retrieve Values into Host Language Variables
The INTO clause specifies the host program variables into which the values retrieved by the select are loaded. There must be a one-to-one correspondence between expressions in the SELECT clause and the variables in the INTO clause. If the statement does not retrieve any rows, the variables are not modified. If the number of values retrieved from the database is different from the number of columns, an error is issued and the sqlwarn3 variable of the SQLCA is assigned the value W. Each result variable can have an indicator variable for null data.
Host language variables can be used as expressions in the SELECT clause and the search_condition, in addition to their use in the INTO clause. Variables used in search_conditions must denote constant values and cannot represent names of database columns or include any operators. Host string variables can also substitute for the complete search condition.
The INTO clause can include a structure that substitutes for some or all of the variables. The structure is expanded by the preprocessor into the names of its individual variables. Therefore, placing a structure name in the INTO clause is equivalent to enumerating all members of the structure in the order in which they were declared.
If using SELECT * to retrieve into a structure, ensure that the members of the structure have a one-to-one correspondence to the columns in the result table.
Retrieving Long Varchar and Long Byte Values
To retrieve long varchar and long byte columns, specify a DATAHANDLER clause in place of the host language variable in the INTO clause. For details about data handler routines, see the chapter “Embedded SQL” and the Embedded SQL Companion Guide. The syntax for the DATAHANDLER clause is as follows:
DATAHANDLER(handler_routine ([handler_arg]))[:indicator_var]
Use of Host Language Variables in a Union
When SELECT statements are combined using the UNION clause, the INTO clause must appear only after the first list of select result expressions, because all result rows of the SELECT statements that are combined by the UNION clause must be identical. The following example shows the correct use of host language variables in a union; result variables are specified only for the first SELECT statement:
EXEC SQL SELECT ename, enumber
INTO :name, :number
FROM employee
UNION
SELECT dname, dnumber
FROM directors
WHERE dnumber < 100;