15.00 - Using SELECT With Dynamic SQL - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Using SELECT With Dynamic SQL

Unlike other executable SQL statements, SELECT returns information beyond statement responses and return codes to the requester.

DESCRIBE Statement

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 (see “DESCRIBE” in SQL Stored Procedures and Embedded SQL).

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

General Procedure

An application must use the following general procedure to set up, execute, and retrieve the results of a SELECT statement invoked as dynamic SQL.

1 Declare a dynamic cursor for the SELECT in the form:

  DECLARE cursor_name CURSOR FOR sql_statement_name

2 Declare the SQLDA, preferably using an INCLUDE SQLDA statement.

3 Build and PREPARE the SELECT statement.

4 Issue a DESCRIBE statement in the form:

  DESCRIBE sql_statement_name INTO SQLDA

DESCRIBE performs the following actions:

a Interrogate the database for the demographics of the expected results.

b Write the addresses of the target variables to receive those results to the SQLDA.

This step is bypassed if any of the following occurs:

  • 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.
  • 5 Allocate storage for target variables to receive the returned data based on the demographics reported by DESCRIBE.

    6 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.