Using ESQL-LIB and Parameter Markers

Using ESQL-LIB alone to implement your dynamic ESQL application with parameter markers provides more flexibility than it does without parameter markers. However, you must explicitly manage all ESQL error and status condition handling for each ESQL-LIB function. You must also declare and manage your own SQL request handles (the ESQL-LIB equivalent of dynamic ESQL statement identifiers). For more information on error handling, see the "Error Handling" section. For more information on SQL request handles, see ESQL-LIB Reference." See clidemo.c for an example of parameter marker support using only ESQL-LIB.

Handling Parameter Markers with ESQL-LIB

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

  1. Declare an SQL request handle for the statement you are about to prepare.
  2. Prepare a statement by calling sqldynprep().
  3. If the statement is a cursor specification, create a column SQLDA_T structure:
    1. Determine the number of the column that the cursor specification requires by calling the sqldynncols() function.
    2. Allocate an SQLDA_T structure for the result set columns by calling the sqlald() function (or your own memory allocation routine).
    3. Initialize the SQLDA_T structure with the column attribute information of the result set by calling the sqldyndesc() function.
    4. 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 sqldynnparms() to retrieve the number of parameter markers in the SQL statement.
  5. NOTE: You do not need to call sqlgetrqhdl(), as you would when using Progress/ESQL statements. You are already working with your own declared SQL request handle.

  6. Allocate an SQLDA_T structure to hold the parameter marker values by calling the sqlald() function (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 and you are referencing the cursor elsewhere in your application (such as for a positioned UPDATE), set a cursor name by calling sqldynsetn().
  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 by calling the sqldynexec() function. Pass to sqldynexec() the address of the parameter SQLDA_T structure that you allocated in step 5 to supply the current values for parameter markers in the statement. If the statement is a cursor specification, execute the fetch loop to retrieve the rows by calling the sqldynftch() function. Pass to sqldynftch() the address of the column SQLDA_T structure that you allocated in step 3 to return the result set.
  12. Repeat the statement as often as you want with new parameter values:
  13. If the statement is a cursor specification:
    If the statement is not a cursor specification:
    Close the existing query after your last fetch by calling the sqldynclose() function with its input parameter set to the value of SQL_STMTCLOSE, then repeat steps 8 through 10 as necessary to supply new values for the parameter markers.
    Repeat steps 9 and 10 as necessary to supply new values for the parameter markers.

  14. Close any cursors you no longer plan to use by calling the sqldynclose() function with its input parameter set to the value of SQL_STMTDROP.
  15. This permanently releases all memory associated with the cursor specification. It does not deallocate storage associated with any SQLDA_T structures.

    NOTE: If you prepare another statement using the SQL request handle associated with an open cursor, the cursor is automatically closed and the corresponding statement and its memory resources are released.

  16. Deallocate any storage you allocated for DATA and INDICATOR members of SQLVAR_T structures and any SQLDA_T structures that you allocated by calling the sqldld() function (or your own memory deallocation routine).

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