Preparing and executing SQL statements in CLI applications

After you have allocated a statement handle, you can perform operations using SQL statements or XQuery expressions. An SQL statement or XQuery expression must be prepared before it can be executed, and CLI offers two ways of preparing and executing: perform the prepare and execute operations in separate steps, and combine the prepare and execute operations into one step.

Before you begin

Before preparing and executing your SQL statement or XQuery expression, ensure that you have allocated a statement handle for it.

Procedure

  • To prepare and execute an SQL statement or XQuery expression in separate steps:
    1. Prepare the SQL statement or XQuery expression by calling SQLPrepare() and passing the statement or expression as the StatementText argument.
      Note: XQuery expressions must be prefixed with the case-insensitive "XQUERY" keyword, unless the statement attribute SQL_ATTR_XQUERY_STATEMENT has been set to SQL_TRUE for this statement handle.
    2. Call SQLBindParameter() to bind any parameter markers you have in the SQL statement. CLI supports named parameter markers (for example, :name) and unnamed parameter markers represented by a question mark (?).
      Note:
      • To use named parameter markers, you must explicitly enable named parameter processing by setting the EnableNamedParameterSupport configuration keyword to TRUE.
      • For XQuery expressions, you cannot specify parameter markers in the expression itself. You can, however, use the XMLQUERY function to bind parameter markers to XQuery variables. The values of the bound parameter markers are then passed to the XQuery expression specified in XMLQUERY for execution.
    3. Execute the prepared statement by calling SQLExecute().
    Use this method when:
    • The same SQL statement or XQuery expression is executed repeatedly (usually with different parameter values). This avoids having to prepare the same statement or expression more than once. The subsequent executions use the access plans already generated by the prepared statement, thus increasing driver efficiency and delivering better application performance.
    • The application requires information about the parameters or columns in the result set before the statement execution.
  • To prepare and execute an SQL statement or XQuery expression in one step:
    1. Call SQLBindParameter() to bind any parameter markers you may have in the SQL statement. CLI supports named parameter markers (for example, :name) and unnamed parameter markers represented by a question mark (?).
      Note:
      • To use named parameter markers, you must explicitly enable named parameter processing by setting the EnableNamedParameterSupport configuration keyword to TRUE.
      • For XQuery expressions, you cannot specify parameter markers in the expression itself. You can, however, use the XMLQUERY function to bind parameter markers to XQuery variables. The values of the bound parameter markers are then passed to the XQuery expression specified in XMLQUERY for execution.
    2. Prepare and execute the statement or expression by calling SQLExecDirect() with the SQL statement or XQuery expression as the StatementText argument.
      Note: XQuery expressions must be prefixed with the case-insensitive "XQUERY" keyword, unless the statement attribute SQL_ATTR_XQUERY_STATEMENT has been set to SQL_TRUE for this statement handle.
    3. Optional: If a list of SQL statements is to be executed, call SQLMoreResults() to advance to the next SQL statement.
    Use this method of preparing and executing in one step when:
    • The statement or expression is executed only once. This avoids having to call two functions to execute the statement or expression.
    • The application does not require information about the columns in the result set before the statement is executed.