Executing Prepared SQL Statements

Use the full set of functions that comes with Adaptive Server ODBC Driver for using prepared statements, which provide performance advantages for statements that are used repeatedly.

  1. Prepare the statement using SQLPrepare.

    For example, this 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, this 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:
    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.

Parent topic: SQL Statements Execution