Using Progress/ESQL and Parameter Markers

Currently, there is no SQL preprocessor support for dynamic parameter markers in Progress/ESQL statements. Consequently, if you choose to use the preprocessor exclusively, the only types of dynamic ESQL with parameter markers that you can include in an application are those statements in which you know the exact number and data types of dynamic parameter markers.

However, if you include additional calls to ESQL-LIB, you can use parameter markers in a more flexible manner. Using the sqlgetrqhdl() function, your ESQL application can gain access to the underlying ESQL-LIB interface and to parameter marker information that is unavailable through the Progress/ESQL syntax alone. See the sample program dprmdemo.cc which uses a combination of Progress/ESQL and ESQL-LIB to support parameter markers. For information on preparing and executing dynamic ESQL with parameter markers using only ESQL-LIB, see the "Using ESQL-LIB and Parameter Markers" section.

Handling Parameter Markers with Progress/ESQL

These are the typical steps with Progress/ESQL to prepare and execute SQL statements that use dynamic parameter markers.

  1. Ensure that you have declared a pointer to each SQLDA_T structure — at least one for parameter marker values and one for the result set of any cursor specification.
  2. Prepare a statement using the PREPARE statement.
  3. If the statement is a cursor specification, create a column SQLDA_T structure:
    1. Allocate an SQLDA_T structure for the result set columns using the ALLOCATE DESCRIPTOR statement (or your own memory allocation routine). Be sure to specify a large enough number of columns to handle the cursor specification. For more information, see the "Executing Dynamic Cursor Specifications" section in this chapter.
    2. Initialize the SQLDA_T structure with the column attribute information of the result set using the DESCRIBE statement.
    3. For each column (SQLVAR_T structure), assign storage addresses (allocated, if necessary) to the DATA and INDICATOR members to hold the column result.
  4. Call the sqlgetrqhdl() function to retrieve the SQL request handle associated with the SQL statement identifier of your prepared statement.
  5. Call sqldynnparms() to retrieve the number of parameter markers in the SQL statement.
  6. Allocate an SQLDA_T structure to hold the parameter marker values using the ALLOCATE DESCRIPTOR statement (or your own memory allocation routine).
  7. Call sqldyndescinp() to initialize the parameter SQLDA_T structure with assumed data type information for each parameter marker in the prepared statement.
  8. For each parameter marker (SQLVAR_T of the parameter SQLDA_T structure), assign storage addresses (allocated, if necessary) to the DATA and INDICATOR members to hold the parameter marker value. (Adjust the data type of the parameter marker, if necessary, to match the data type of the storage address.)
  9. If the statement is a cursor specification, declare a cursor using the DECLARE CURSOR statement.
  10. For each parameter marker (SQLVAR_T of the parameter SQLDA_T structure), assign the appropriate value(s) to the DATA and INDICATOR storage areas.
  11. Execute the statement:
  12. If the statement is a cursor specification:
    If the statement is not a cursor specification:
    Invoke the OPEN statement for the cursor, specifying the parameter SQLDA_T structure in the USING clause to pass parameter values to the statement. Execute the fetch loop to retrieve the rows using the dynamic ESQL FETCH statement.
    Invoke the EXECUTE statement for the prepared statement identifier, specifying the parameter SQLDA_T structure in the USING clause to pass parameter values to the statement.

    The USING clause of the OPEN and EXECUTE statement can specify a list of host language variable references (including indicator variables), instead of an SQL descriptor area. If you specify host language variable references in the USING clause, the SQL preprocessor declares its own SQL descriptor area to reference them.

    In this case, skip steps 4 through 8, and in step 10, assign the parameter marker values to the specified host language variables. Ensure that your host language variable list is compatible in number and data type with the parameter markers in the prepared statement or cursor specification. For more information, see the "Host Language Variables" and "Indicator Variables" sections in this chapter.

  13. Repeat the statement as often as you want with new parameter values.
  14. If the statement is a cursor specification, first close the existing query after your last fetch using the CLOSE statement, then repeat steps 9 through 11 as necessary to supply new values for the parameter markers.

    If the statement is not a cursor specification, repeat steps 10 and 11 as necessary to supply new values for the parameter markers.

  15. Deallocate any storage you allocated for DATA and INDICATOR members of SQLVAR_T structures and any SQLDA_T structures that you allocated using the DEALLOCATE DESCRIPTOR statement (or your own memory deallocation routine).
  16. NOTE: If you prepare another statement using the statement identifier associated with an open cursor, the cursor is automatically closed and the corresponding statement and its memory resources are released; the memory resource for any SQLDA structures that your application uses are untouched.


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