Executing prepared statements

Prepared statements provide performance advantages for statements that are used repeatedly. ODBC provides a full set of functions for using prepared statements.

For an introduction to prepared statements, see Preparing statements.

To execute a prepared SQL statement

  1. Prepare the statement using SQLPrepare.

    For example, the following code fragment illustrates how to prepare an INSERT statement:

    SQLRETURN   retcode;
    SQLHSTMT    stmt;
    retcode = SQLPrepare( stmt,
                "INSERT INTO Departments
                 ( DepartmentID, DepartmentName, DepartmentHeadID )
                 VALUES (?, ?, ?,)",
              SQL_NTS);

    In this example:

    • retcode   Holds a return code that should be tested for success or failure of the operation.

    • stmt   Provides a handle to the statement so that it can be referenced later.

    • ?   The question marks are placeholders for statement parameters.

  2. Set statement parameter values using SQLBindParameter.

    For example, the following function call sets the value of the DepartmentID variable:

    SQLBindParameter( stmt,
                     1,
                     SQL_PARAM_INPUT,
                     SQL_C_SSHORT,
                     SQL_INTEGER,
                     0,
                     0,
                     &sDeptID,
                     0,
                     &cbDeptID);

    In this example:

    • stmt   is the statement handle.

    • 1   indicates that this call sets the value of the first placeholder.

    • SQL_PARAM_INPUT   indicates that the parameter is an input statement.

    • SQL_C_SHORT   indicates the C data type being used in the application.

    • SQL_INTEGER   indicates SQL data type being used in the database.

      The next two parameters indicate the column precision and the number of decimal digits: both zero for integers.

    • &sDeptID   is a pointer to a buffer for the parameter value.

    • 0   indicates the length of the buffer, in bytes.

    • &cbDeptID   is a pointer to a buffer for the length of the parameter value.

  3. Bind the other two parameters and assign values to sDeptId.

  4. Execute the statement:

    retcode = SQLExecute( stmt);

    Steps 2 to 4 can be carried out multiple times.

  5. Drop the statement.

    Dropping the statement frees resources associated with the statement itself. You drop statements using SQLFreeHandle.

For a complete sample, including error checking, see samples-dir\SQLAnywhere\ODBCPrepare\odbcprepare.cpp.

For more information about SQLPrepare, see [external link] SQLPrepare in the Microsoft ODBC Programmer's Reference.