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

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.

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.

Bind the other two parameters and assign values to sDeptId.
Execute the statement:
retcode = SQLExecute( stmt);
Steps 2 to 4 can be carried out multiple times.
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 SQLPrepare in the Microsoft ODBC API Reference at [external link] http://msdn.microsoft.com/en-us/library/ms710926.aspx.

Discuss this page in DocCommentXchange.