When the Result Column Data Types Are Known
If the program knows the data types of the resulting columns and of the result variables used to store the column values, the program can execute the SELECT statement using the EXECUTE IMMEDIATE statement with the INTO clause.
In the following example, a database contains several password tables, each having one column and one row and containing a password value. An application connected to this database requires a user to correctly enter two passwords before continuing. The first password entered is actually the name of a password table and the second is the password value in that table.
The following code uses the EXECUTE IMMEDIATE statement to execute the dynamically defined select built by the application to check these passwords:
...
exec frs prompt noecho ('First Password: ',
:table_password);
exec frs prompt noecho ('Second Password: ',
:value_password);
select_stmt = 'select column1 from ' +
table_password;
exec sql execute immediate :select_stmt
into :result_password;
if (sqlcode < 0) or (value_password <>
result_password)
then
exec frs message 'Password authorization failure';
endif
...
Because the application's developer knows the data type of the column in the password table (although not which password table will be selected), the developer can execute the dynamic select with the EXECUTE IMMEDIATE statement and the INTO clause.
The syntax of EXECUTE IMMEDIATE in this context is shown here:
EXEC SQL EXECUTE IMMEDIATE select_statement
INTO variable {, variable};
[EXEC SQL BEGIN;
host_code
EXEC SQL END;]
This syntax retrieves the results of the select into the specified host variables. The BEGIN and END statements define a select loop that processes each row returned by the SELECT statement and terminates when there are no more rows to process. If a select loop is used, your program cannot issue any other OpenSQL statements for the duration of the loop.
If the select loop is not included in the statement, OpenSQL assumes that the SELECT statement is a singleton select returning only one row and, if more than one row is returned, issues an error.
Last modified date: 01/30/2023