Programming Interfaces Guide
   CHAPTER 4. ODBC Programming     

Using prepared statements in ODBC

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 statement:

1. You prepare the statement using SQLPrepare. The following code fragment illustrates how to prepare an INSERT statement:

   SQLRETURN   retcode;
   SQLHSTMT    hstmt;
   
   retcode = SQLPrepare(hstmt,
               "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.

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

   • ?     The question marks are placeholders for statement parameters.

2. You set statement parameter values using SQLBindParameter. For example, the following function call sets the value of the dept_id variable:

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

In this example:

• hstmt     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 and SQL_INTEGER indicate the C data type being used in the application and the SQL 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     Pointer to a buffer for the parameter value.

• The following zero indicates the length of the buffer, in bytes.

• &cbDeptID     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(hstmt); 

  This step can be carried out multiple times.

• Drop the statement. This frees resources associated with the statement itself. You drop statements using SQLFreeStmt.

     For more information, see the ODBC SDK documentation.