Dynamic FETCH Statement

The dynamic FETCH statement retrieves the rows of a dynamic cursor similar to the way a static FETCH statement retrieves the rows of a static cursor. The preprocessor generates a call to sqldynftch() for this statement.

Following is the syntax for the dynamic FETCH statement:

SYNTAX
EXEC SQL FETCH cursor_name
 { USING SQL DESCRIPTOR structure-name | INTO host-var-ref
 [ [ INDICATOR ] ind-var-ref ] , ... } ; 

cursor-name

A cursor name previously defined in a DECLARE CURSOR statement.

structure-name

A previously declared SQLDA_T structure variable name used to retrieve the values for the row being fetched.

host-var-ref

Host language variable references (including any indicator variables) are used to retrieve column data. These variables must appear in the SQL Declare section of your ESQL application. For more information, see the "Host Language Variables" and "Indicator Variables" sections in this chapter.

ind-var-ref

An indicator variable provides information about a host language variable that is not representable in the host language variable value itself. You declare indicator variables like host language variables in an SQL Declare section, and declare them as C numeric types. Indicator types can be long, short, sqldec, float, or double. For information on declaring host language variables, see the "Host Language Variables" section in this chapter.

When using the SQLDA_T structure, the FETCH statement expects the DATA member of the SQLVAR_T structure for each column to point to the target storage for the data value returned. It also expects the INDICATOR member to point to a long (which you can set to 0 or -1), if the program allocates storage for INDICATOR.

CAUTION: Any bad pointer values in DATA or INDICATOR members can lead to unpredictable application behavior.

When using host language variable references, the preprocessor automatically allocates and initializes an SQLDA_T structure to refer to the specified host language and indicator variables for the fetch.

The ESQL-LIB interface returns data to your application based on the values in the SQLDA_T structure at the time of the fetch, and it assigns the row values to the respective targets based on the addresses in DATA and INDICATOR, and the values for TYPE and LENGTH at that time.

Table 2–15 lists the assumed data types for target storage associated with the SQL data types specified in the SQLDA_T structure for a FETCH statement.

Table 2–15: Data Types for Target Storage  
Data Type
Target
CHARACTER
Type char, and LENGTH length-1 (allowing for a null terminator). LENGTH is ignored for all other data types.
INTEGER
C long
SMALLINT
C short
DECIMAL, or NUMERIC
ESQL sqldec
REAL, or FLOAT
C float
DOUBLE PRECISION
C double
LOGICAL
ESQL sqlbool
DATE
ESQL sqldate

The Progress implementation of dynamic ESQL allows you to change the values of TYPE, SCALE, PRECISION, and LENGTH between a DESCRIBE and corresponding FETCH statement. This enables you to have the ESQL-LIB interface coerce the value into a different data type from the defaults listed in Table 2–15.

For more information on data type conversion, see "ESQL Data Type Compatibilities." For an example of the dynamic ESQL FETCH statement, see Step 9 in the "Executing Dynamic Cursor Specifications" section in this chapter.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095