Specifying Host Variables with INTO

Go Up to Understanding Data Retrieval with SELECT

A singleton select returns data to a list of host-language variables specified by an INTO clause in the SELECT statement. The INTO clause immediately follows the list of table columns from which data is to be extracted. Each host variable in the list must be preceded by a colon (:) and separated from the next by a comma.

The host-language variables in the INTO clause must already have been declared before they can be used. The number, order, and data type of ­host­-­language variables must correspond to the number, order, and data type of the columns retrieved. Otherwise, overflow or data conversion errors may occur.

For example, the following C program fragment declares three host variables, lname, fname, and salary. Two, lname, and fname, are declared as character arrays; salary is declared as a long integer. The SELECT statement specifies that three columns of data are to be retrieved, while the INTO clause specifies the host variables into which the data should be read.

. . .
EXEC SQL
BEGIN DECLARE SECTION;
long salary;
char lname[20], fname[15];
EXEC SQL
END DECLARE SECTION;
. . .
EXEC SQL
SELECT LAST_NAME, FIRST_NAME, SALARY
INTO :lanem, :fname, :salary
FROM EMPLOYEE
WHERE LNAME = 'Smith';
. . .

Note: In a multi-row select, the INTO clause is part of the FETCH statement, not the SELECT statement. For more information about the INTO clause in FETCH, see Fetching Rows with a Cursor.