Executing SQL Statements With Bound Parameters

Construct and execute a SQL statement, using bound parameters to set values for statement parameters at runtime.

1. Allocate a handle for the statement using SQLAllocHandle.

   For example, this statement allocates a SQL_HANDLE_STMT handle the with name “stmt”, on a connection with a handle named “dbc”:

   SQLAllocHandle( SQL_HANDLE_STMT, dbc, &stmt );

2. Bound parameters for the statement using SQLBindParameter.

   For example, these lines declare variables to hold the values for the department ID, department name, and manager ID, as well as for the statement string itself. Then, they bind parameters to the first, second, and third parameters of a statement executed using the “stmt” statement handle:

   #defined DEPT_NAME_LEN 20
   
   SQLLEN cbDeptID = 0,
      cbDeptName = SQL_NTS, cbManagerID = 0;
   SQLCHAR deptname[ DEPT_NAME_LEN ];
   SQLSMALLINT deptID, managerID; 
   SQLCHAR *insertstmt = 
      "INSERT INTO department "   
      "( dept_id, dept_name, dept_head_id )"   
      "VALUES (?, ?, ?,)"; 
   SQLBindParameter( stmt, 1, SQL_PARAM_INPUT,
      SQL_C_SSHORT, SQL_INTEGER, 0, 0,     
      &deptID, 0, &cbDeptID); 
   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);

3. Assign values to the parameters.

   For example, these lines assign values to the parameters for the fragment of step 2:

   deptID = 201; 
   strcpy( (char * ) deptname, "Sales East" ); 
   managerID = 902;

   Usually, these variables are set in response to user action.

4. Execute the statement using SQLExecDirect.

   For example, this line executes the statement string held in “insertstmt” on the “stmt” statement handle:

   SQLExecDirect( stmt, insertstmt, SQL_NTS) ;