When you create a SELECT statement dynamically, you cannot use
the INTO host_var clause of FETCH because you cannot name the
host variables in the prepared statement. To fetch column values into an sqlda structure,
use the USING DESCRIPTOR clause of FETCH instead of the INTO clause.
The FETCH...USING DESCRIPTOR statement puts each column value into
the sqldata field of its sqlvar_struct structure.
Using the FETCH...USING
DESCRIPTOR statement assumes that a cursor is associated with the
prepared statement. You must always use a cursor for SELECT statements
and cursor functions (EXECUTE FUNCTION statements that return multiple
rows). However, if either of these statements returns only one row,
you can omit the cursor and retrieve the column values into an
sqlda structure
with the EXECUTE...INTO DESCRIPTOR statement.
Important: If
you execute a SELECT statement or user-defined function that returns
more than one row and do not associate the statement with a cursor,
your program generates a runtime error. When you associate a singleton
SELECT (or EXECUTE FUNCTION) statement with a cursor, does not generate
an error. Therefore, it is a good practice always to associate a dynamic
SELECT or EXECUTE FUNCTION statement with a cursor and to use a FETCH...USING
DESCRIPTOR statement to retrieve the column values from this cursor
into the sqlda structure.
Once the column values are in the sqlda structure, you can
transfer the values from the sqldata fields to the appropriate
host variables. You must use the sqllen and sqltype fields
to determine, at run time, the data types for the host variables. You
might need to perform data type or length conversions between the
SQL data types in the sqltype fields and the data types that
are needed for host variables that hold the returned value.