Executing prepared statements

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

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);
   

   where:

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

   • stmt provides a handle to the statement.

   • ? is a statement parameter marker.

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_SHORT, 
      SQL_INTEGER,  
      0,           
      0,             
      &sDeptID, 
      0,        
      &cbDeptID);
   

   where:

   • stmt is the statement handle.

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

   • SQL_PARAM_INPUT indicates that the parameter is an input statement.

   • SQL_C_SHORT indicates the C datatype being used in the application.

   • SQL_INTEGER indicates the SQL datatype being used in the database.

   • 0 indicates the column precision.

   • 0 indicates the number of decimal digits.

   • &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:

   SQLBindParameter( stmt, 2, SQL_PARAM_INPUT,
      SQL_C_CHAR, SQL_CHAR, DEPT_NAME_LEN, 0,
      deptname, 0,&cbDeptName);
   
   SQLBindParameter( stmt, 3, SQL_PARAM_INPUT,
      SQL_C_SSHORT, SQL_INTEGER, 0, 0,
      &managerID, 0, &cbManagerID);
   

4. Execute the statement:

   retcode = SQLExecute( stmt);
   

   You can repeat steps 2 through 4 multiple times.

5. Drop the statement using SQLFreeHandle.

   Dropping the statement frees resources associated with the statement itself.