ASA Programming Guide
  ODBC Programming
    Executing SQL statements

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 department
                ( dept_id, dept_name, dept_head_id )
                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 dept_id 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\ASA\ODBCPrepare\odbcprepare.cpp.

For more information on SQLPrepare, see SQLPrepare in the Microsoft ODBC Programmer's Reference.