Dynamic Cursor SELECT Using Progress/ESQL
These are typical steps you take to execute a dynamic cursor SELECT using Progress/ESQL:
CAUTION: If you call
- Declare a pointer to an instance of the ESQL SQLDA_T structure, as shown in the following example:
For detailed information on the SQLDA, see the "SQL Descriptor Area" section.
- Prepare the SQL statement as shown in this code fragment:
NOTE: You specify any lock criteria (FOR READ ONLY or FOR UPDATE) directly in the request string, along with the cursor specification. DEMOSTMT is an arbitrary statement identifier, like a cursor name.
The variable prequest is a pointer to the request string. It ultimately maps to a request handle in the C code.
NOTE: The scope of a statement identifier (used, for example, in PREPARE, DESCRIBE and DECLARE CURSOR statements) extends to the entire ESQL source file. In the example, this means that any reference to this statement identifier in any function within the file references the prepared statement in prequest.- Determine the number of columns in the result set by first calling the
sqlgetrqhd2()
ESQL-LIB function to obtain the request handle for the prepared cursor SELECT, and then calling thesqldynncols()
function to return the number of columns:
You can also select a number of columns known to be greater or equal to the columns in the result set. However, the following steps assume you use these ESQL-LIB functions to return the exact number in the host language variable ncols.
- Allocate storage for the SQLDA_T and SQLVAR_T structures using your own memory allocation routines, the
sqlald()
ESQL-LIB function, or the ALLOCATE DESCRIPTOR statement that this function supports. The following example shows functionsqlald()
allocating space for structures that can handlencols
result columns as determined in Step 3 (The equivalent ALLOCATE DESCRIPTOR statement is shown in the comment.):
- Initialize each SQLVAR_T structure with the attributes of the columns in the result set, including data type and size information. You can do this by executing the DESCRIBE statement, calling the
sqldyndesc()
ESQL-LIB function directly, or providing the information from your knowledge of the table. This example executes the DESCRIBE statement:
- Based on the data type and size of each column, use your own memory allocation routines or the
sqlalloc()
ESQL-LIB function to allocate storage for the data in the columns. As described in the previous section, the data type for each column is contained in the TYPE members of the SQLVAR_T structures (each column or array column element in the result has its own SQLVAR_T instance). The DATA member of the SQLVAR_T structure points to the allocated storage for each column.Also, if you can set the data type to a NULL value (the NULLABLE member of the SQLVAR_T structure is non-zero (TRUE), also allocate storage (again using
NOTE: See thesqlalloc()
or the equivalent) for the indicator variable (a long type) to which the INDICATOR member of the SQLVAR_T structure points.alloc_disp_cols()
function in thedyndemo.cc
example for a comprehensive example of this step. This function usessqllalloc()
to allocate storage for an arbitrary number of columns with arbitrary data types, and displays the names of the columns.- Declare the cursor for the SELECT using the DECLARE CURSOR statement, as shown in this example:
DEMOSTMT is the statement identifier used in the PREPARE statement in Step 2. You do not specify lock criteria in the dynamic DECLARE CURSOR statement, as you do in the static DECLARE CURSOR statement. Instead, you place lock criteria directly in the request string along with the cursor specification (see Step 2).
- Open the cursor you just declared, as shown in this example:
When you open a cursor, the SELECT statement you defined in your PREPARE statement (in Step 2) executes.
NOTE: If the SELECT statement to which the cursor name refers contains dynamic parameter markers, values for the parameter markers are supplied in the OPEN statement. For more information, see the "Using Dynamic Parameter Markers" section in this chapter.- Use the FETCH statement to access the results that the cursor SELECT generates, as shown in the following example:
The FETCH statement retrieves one row of data into the data areas allocated in Step 6. You must access these data areas to access the column data.
NOTE: See thedisp_data()
function in the installeddyndemo.cc
example for a comprehensive example of this step. This function reads and displays the data for an arbitrary number of columns with arbitrary data types.- When you are done with the SELECT statement, use the CLOSE statement to close the cursor, as shown in the following example:
sqldld()
before deallocating memory previously allocated withsqlalloc()
, the pointers to that memory are lost and the memory cannot be recovered until your application terminates.See the
dealloc_cols()
function in the installeddyndemo.cc
example for a comprehensive example of this step.The following section describes how to use dynamic parameter markers in the cursor SELECT statement and any other dynamic ESQL request, using prepared execution. For more information on the dynamic ESQL statements used for prepared execution, see the "Dynamic ESQL Statements for Prepared Execution" section in this chapter.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |