Prepared Execution for Dynamic Cursor Specification
Follow these general steps to execute a dynamic cursor specification:
- Prepare the cursor SELECT using the PREPARE statement (or
sqldynprep()
), establishing an SQL request handle for the statement.- Allocate an SQLDA to retrieve rows from the result set. It must be large enough for the number of columns in the cursor specification.
There are several techniques available to complete this step. You can skip this step if you have the preprocessor allocate the SQLDA for you from a host language variable list specified later in this procedure.
- If you complete Step 2, initialize the column attributes in the SQLDA according to the specification using the DESCRIBE statement (or the
sqldyndesc()
ESQL-LIB function). You can also initialize the column attributes yourself, if the information is available.- If you complete Step 2 and Step 3, allocate storage for each column of the specification according to the initialized column attributes in the SQLDA. (There are several techniques available to complete this step.)
- Associate a cursor with the specification using the DECLARE CURSOR statement (or the
sqldynsetn()
ESQL-LIB function).- Execute the cursor specification using the OPEN statement (or
sqldynexec()
).- If you constructed your own SQLDA in Step 2 through Step 4, retrieve each row of data into your SQLDA using the dynamic FETCH statement (or the
sqldynftch()
ESQL-LIB function).- If you did not specify your own SQLDA, retrieve each row of data into a list of host language variables using the dynamic FETCH statement. This host language variable list must exactly match the number of columns in the specification result set.
The preprocessor allocates and initializes its own SQLDA to reference this list of variables, deallocating and reallocating the SQLDA each time the FETCH statement is invoked.
- If you use your own SQLDA in the previous steps, deallocate its structure and data storage. (There are several techniques available to complete this step.)
If you do not allocate and use your own SQLDA, your dynamic cursor request pays a significant penalty in memory management with each row retrieval. You must also be precise about matching the number of host language variables to the number of columns in your specification. However, you reduce your development cycle from eight to four steps, giving some of the most intricate and tedious code to the preprocessor. For more information on executing dynamic cursor specifications using your own SQLDA, see the "Executing Dynamic Cursor Specifications" section in this chapter.
For information on the dynamic ESQL statements used for cursor specifications, see the "Prepared Execution" section.
If you have dynamic parameter markers in the cursor specification, you also need to provide your own SQLDA or host language variable list to specify the values. However, dynamic parameter markers require closer attention. For more information, see the "Using Dynamic Parameter Markers" section in this chapter.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |