Unlike other executable SQL statements, SELECT returns information beyond statement responses and return codes to the requester.
Because the requesting application needs to know how much (if any) data will be returned by a dynamically prepared SELECT, you must use an additional SQL statement, DESCRIBE, to make the application aware of the demographics of the data to be returned by the SELECT statement.
DESCRIBE writes this information to the SQLDA declared for the SELECT statement as follows.
|THIS information …||IS written to this field of SQLDA …|
|number of values to be returned||SQLN|
|column name or label of nth value||SQLVAR
(nth row in the SQLVAR(n) array)
|column data type of nth value|
|column length of nth value|
- Declare a dynamic cursor for the SELECT in the form:
DECLARE cursor_name CURSOR FOR sql_statement_name
- Declare the SQLDA, preferably using an INCLUDE SQLDA statement.
- Build and PREPARE the SELECT statement.
- Issue a DESCRIBE statement in the form:
DESCRIBE sql_statement_name INTO SQLDADESCRIBE performs the following actions:
This step is bypassed if any of the following occurs:
- Interrogate the database for the demographics of the expected results.
- Write the addresses of the target variables to receive those results to the SQLDA.
- The request does not return any data.
- An INTO clause was present in the PREPARE statement.
- The statement returns known columns and the INTO clause is used on the corresponding FETCH statement.
- The application code defines the SQLDA.
- Allocate storage for target variables to receive the returned data based on the demographics reported by DESCRIBE.
- Retrieve the result rows using the following SQL cursor control statements:
- OPEN cursor_name
- FETCH cursor_name USING DESCRIPTOR SQLDA
- CLOSE cursor_name
In this step, results tables are examined one row at a time using the selection cursor. This is because client programming languages do not support data in terms of sets, but only as individual records.
For more information about the DESCRIBE statement, see Teradata Vantage™ - SQL Stored Procedures and Embedded SQL, B035-1148.