InformIT: Visual C++ 6 Unleashed > Executing SQL Statements

Table of Contents

Executing SQL Statements

After you have successfully connected to a data source, it is time to get down to the real work of manipulating the data. This is done by executing Structured Query Language statements against the connected data source. For more on the syntax of SQL, see Chapter 19, "Database Overview."

In the following sections, you will look at how statement handles are used to execute SQL against a data source, including the use of SQLExecDirect() for direct executions, and the SQLPrepare() and SQLExecute() functions, which can be used to prepare a SQL statement in a separate step, which can then be executed multiple times.

Statement Handles

Before executing a statement, you must allocate a statement handle, which provides a data structure for ODBC to keep track of the SQL statement to be executed and the results it will return. Allocating a statement is done with the SQLAllocHandle() function that you saw earlier—HandleType should be set to SQL_HANDLE_STMT, InputHandle should receive a previously allocated connection handle, and OutputHandle should point to a new handle of type SQLHSTMT that will be initialized. You can see an example of this in the next section where you see how to use the statement handle and SQLExecDirect() to execute SQL statements.

The SQLSetStmtAttr() and SQLGetStmtAttr() functions allow you to set and retrieve options for a statement handle in the same way that SQLSetConnectAttr() and SQLGetConnectAttr() work with options for connection handles. You will look at some of the specific options for statements later in this chapter.

When you are finished with a statement handle, you should de-allocate it by calling SQLFreeHandle(), with HandleType set to SQL_HANDLE_STMT.

`SQLExecDirect()`

As the name suggests, SQLExecDirect() is the most direct (and easiest) method of executing an SQL statement against an ODBC data source. For statements that will be executed only once, this is also the fastest method of submitting SQL statements. The following is the prototype for SQLExecDirect():

SQLRETURN SQLExecDirect(SQLHSTMT StatementHandle, SQLCHAR * StatementText,
    SQLINTEGER TextLength);

This function simply takes a null-terminated string containing an SQL statement (StatementText) and executes it on the data source connected to the statement handle in StatementHandle. (Remember that, for C/C++ applications, length parameters like TextLength should be set to SQL_NTS.)

If SQLExecDirect() returns SQL_SUCCESS, the statement was successfully executed against the data source. In some cases, where the statement was successful, but something abnormal occurred, it will return SQL_SUCCESS_WITH_INFO. In this case, or if SQLExecDirect() returns SQL_ERROR, you can retrieve additional information by calling SQLGetDiagRec().

For most implementations, it's best to have a function that handles all SQL commands. In the ODBCTest example, menu items were added to add "Insert Chuck Wood" and "Delete Chuck Wood" options. These commands are designed to insert and delete a record into an Employee table. The following steps show the use of SQLExecDirect() to perform a simple INSERT and a simple DELETE by using a standard SQL routine:

Add a function prototype inside your view header file (ODBCTestView.h), as shown below in gray:

class CODBCTestView : public CView
{
protected: // create from serialization only
        CODBCTestView();
        DECLARE_DYNCREATE(CODBCTestView)

/////////////////////////////////////////////////////////////////////////////
//      Added by Chuck Wood
        SQLHANDLE hOdbcEnv;                       //ODBC Environment handle
        SQLHANDLE hDbConn;                       //ODBC Connection handle
        void setUpODBC();                       //Set up the ODBC environment
        void displayODBCError (SQLRETURN sr, char *inMessage = NULL); x//Display error


        void executeSQL (SQLCHAR *SQL);	//Execute an SQL string


/////////////////////////////////////////////////////////////////////////////

Inside the view source file (ODBCTestView.h), write the routine using the SQLExecDirect function:

void CODBCTestView::executeSQL (SQLCHAR *SQL) {
        SQLRETURN sr;                           //Return code for your ODBC calls
        SQLHSTMT hstmt;
// Allocate new Statement Handle based on previous connection
        sr = SQLAllocHandle(SQL_HANDLE_STMT, hDbConn, &hstmt);
        if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO) {
               char message[200];
               sprintf (message, "Error Allocating Handle: %d\n", sr);
               MessageBox(message);
        }
        sr = SQLExecDirect(hstmt, SQL, SQL_NTS);
        if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO) {
               char message[200];
               sprintf (message, "Error in SQLExecDirect. SQL was:\n\n%s\n\n", SQL);
               displayODBCError(sr, message);
        }
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
}

Add the "Insert Chuck Wood" and "Delete Chuck Wood" menu options, and use the ClassWizard to generate these functions. Then simply call the executeSQL function with the appropriate SQL string, as shown by the following lines in gray:

void CODBCTestView::OnViewInsertchuckwood() {


    executeSQL((SQLCHAR *)
        "INSERT INTO Employee (EmpName, Salary, Dept) VALUES ('Chuck Wood',
        x120000, 'IS')");


}
void CODBCTestView::OnViewDeletechuckwood() {


    executeSQL((SQLCHAR *)
      "DELETE FROM Employee WHERE EmpName ='Chuck Wood'");


}

Prepared SQL Statements

In addition to the SQLExecDirect() method that you saw previously, ODBC also allows you to prepare SQL statements in a separate step before executing them. This can be a much more efficient way to do things if you will be executing the same statement many times. You parse the SQL only once and can then execute the statement many different times, without the parsing overhead. This technique is particularly useful when combined with statement parameters, which you will see later.

The SQLPrepare() function is used to prepare an SQL statement for execution:

SQLRETURN SQLPrepare(SQLHSTMT StatementHandle, SQLCHAR * StatementText,
    SQLINTEGER TextLength);

This function takes a StatementHandle previously allocated with AllocHandle() and a pointer to a null-terminated string that contains the StatementText. Remember that in C/C++ apps, you should pass SQL_NTS for parameters like TextLength.

When a statement is prepared, the ODBC standard SQL grammar that is passed to SQLPrepare() is translated to the native SQL dialect for the data source. You can retrieve this native translation by calling SQLNativeSql().

`SQLExecute()`

After the statement is prepared by SQLPrepare(), you can execute the statement by calling SQLExecute() with the statement handle that was passed to SQLPrepare().

Although you can gain some performance advantages by preparing static SQL statements that will be executed several times, prepared statements are most useful when you use parameters with them, as you will see in the next section. You will also take a look at an example after you look at parameters.

Working with Parameters

So far, you have seen how to execute SQL statements based on a relatively static string. You have also seen how to use SQLPrepare() to create an SQL statement that can be executed many different times—but often, you don't want to execute exactly the same statement many times. It would be convenient if you could use the same general statement many times, with different values each time. Well, it turns out that ODBC lets you do just this by allowing the use of parameters in your SQL statements.

For example, you could use SQLPrepare() to prepare the following SQL statement:

"SELECT * FROM Employees WHERE empNo = ?"

The question mark (?) in this statement serves as a placeholder, or marker, for statement parameter. Parameters can be used to pass values into an SQL statement, as in this SELECT example. They may also be used to return output values in a procedure call. Values are assigned to the parameter at runtime by binding a variable to the parameter with SQLBindParameter().

`SQLBindParameter()`

The SQLBindParameter() function allows you to bind a buffer in memory to a given parameter marker, before the statement is executed. Its prototype is shown here:

SQLRETURN SQLBindParameter(SQLHSTMT StatementHandle, SQLUSMALLINT ParameterNumber,
    SQLSMALLINT InputOutputType, SQLSMALLINT ValueType, SQLSMALLINT ParameterType,
    SQLUINTEGER ColumnSize, SQLSMALLINT DecimalDigits, SQLPOINTER ParameterValuePtr,
    SQLINTEGER BufferLength, SQLINTEGER * StrLen_or_IndPtr);

The StatementHandle parameter refers to the statement handle that you are using to execute the SQL statement.

The parameters in an SQL statement are numbered from left to right, starting with 1. You can call SQLBindParameter() for each parameter, with the appropriate value for ParameterNumber.

The InputOutputType parameter specifies how the parameter is used. For SQL statements that don't call procedures, such as SELECT or INSERT statements, this will be SQL_PARAM_INPUT. For procedure parameters, you can also use parameters of type SQL_PARAM_OUTPUT and SQL_PARAM_INPUT_OUTPUT.

The ValueType parameter is used to specify the C type of the variable that is being bound—SQL_C_SLONG—and the ParameterType argument specifies the SQL type only of the parameter—SQL_INTEGER. These parameters specify how ODBC will perform any conversion of the data. For more on ODBC data types, see the earlier section on ODBC data types.

The ColumnSize and DecimalDigits parameters are used to specify the size of the SQL parameter and its precision. These parameters are used only for certain values of ParameterType where they are applicable.

The ParameterValuePtr parameter points to the buffer in your application that holds the value to be substituted in the SQL statement, and BufferLength is used to pass the length of the buffer for binary or character parameters.

The buffer at StrLen_or_IndPtr is used to specify information about the data passed in ParameterValuePtr. The value in this buffer can have one of the following values:

The length of the parameter at ParameterValuePtr.
SQL_NTS—The buffer holds a null-terminated string.
SQL_NULL_DATA—The buffer holds a NULL value.
SQL_DEFAULT_PARAM—A procedure should use a default parameter value.
SQL_LEN_DATA_AT_EXEC—Used to pass parameter data with SQLPutData().

For output parameters, this buffer will also receive one of these values after the statement is executed. A menu option, "Test Prepared Insert" was added to the ODBCTest example, and the Class Wizard was used to create a new function in the source file (ODBCTestView.cpp) called CODBCTestView::OnViewTestpreparedinsert that is executed when the new menu option is selected. The following added code shows how you can prepare a statement to add "Joe Schmoe" to the Employee database:

void CODBCTestView::OnViewTestpreparedinsert() {


//Code added by Chuck Wood
//REMEMBER TO CLICK CONNECT FIRST
    SQLRETURN sr;
    SQLHSTMT hstmt;
    SQLCHAR SQL[200] =
        "INSERT INTO Employee (EmpName, Salary, Dept) VALUES (?, ?, ?)";
    char name[] = "Joe Schmoe";
    double salary = 69584.23;
    char department[] = "Accounting";
    SQLINTEGER nameLength = SQL_NTS;
    SQLINTEGER salaryLength = sizeof(salary);
    SQLINTEGER deptLength = SQL_NTS;
    // Allocate a new statement handle
    sr = SQLAllocHandle(SQL_HANDLE_STMT, hDbConn, &hstmt);
    // Prepare statement
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
 "Error in SQLAllocHandle in OnViewTestpreparedinsert");
    sr = SQLPrepare(hstmt, SQL, SQL_NTS);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
 "Error in SQLPrepare in OnViewTestpreparedinsert");
    // Bind Parameters
    sr = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
        SQL_C_CHAR, SQL_CHAR, 0, 0,
        name, sizeof(name), &nameLength);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
         "Error in Binding 1 in OnViewTestpreparedinsert");
    sr = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
        SQL_C_DOUBLE, SQL_DOUBLE, 0, 0,
        &salary, sizeof(salary), &salaryLength);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
         "Error in Binding 2 in OnViewTestpreparedinsert");
    sr = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT,
        SQL_C_CHAR, SQL_CHAR, 0, 0,
        department, sizeof(department), &deptLength);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
         "Error in Binding 3 in OnViewTestpreparedinsert");
    // Execute statement with parameters
    sr = SQLExecute(hstmt);
    if(sr != SQL_SUCCESS && sr != SQL_SUCCESS_WITH_INFO)
        displayODBCError(sr,
  "Error in SQLExecute in OnViewTestpreparedinsert");
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt);

Parameter Arrays

In the previous example, you passed one set of parameter values for each call to SQLExecute(). Although this can add some efficiency to your code, you can gain even more in terms of both network overhead and data source execution time by passing an entire array of parameters to a single SQLExecute() call. Unfortunately, like many other advanced features of ODBC, this may not be supported by all drivers.

To pass arrays of parameters, you set the SQL_ATTR_PARAMSET_SIZE statement attribute to the length of your parameter array(s). You should also set the SQL_ATTR_PARAM_ STATUS_PTR attribute to point to an array to receive the status for the result of each set of parameters.

Row-Wise Binding

In the previous example, you used column-wise binding to bind a separate buffer for each parameter, although you can also simplify your code somewhat by binding parameters by row. This allows you to define a structure that will hold all your parameters for a statement. You can then pass an array of these structures when calling SQLExecute().

To use row-wise binding, you set the SQL_ATTR_PARAM_BIND_TYPE statement attribute to the size of the structure you have defined to hold the parameters. You will then bind each parameter individually, binding to the address of the corresponding structure field for the first element of the array. This is very similar to the row-wise binding that you will see for column data later in this chapter.

Passing Parameter Data at Execution Time

Earlier, when you explored SQLBindParameter(), you saw that you could pass a value of SQL_DATA_AT_EXEC in the indicator array specified by StrLen_or_IndPtr. This allows you to pass data for lengthy parameters at the time the statement is executed. This can be useful for values that are too long to store in conventional parameter buffers.

To pass parameter data at execution time, you bind the desired parameters with the SQL_DATA_AT_EXEC flag set in the indicator array, and then call SQLExecute(), which will return SQL_NEED_DATA if data is required for parameters.

You will then call SQLParamData() to retrieve information about the required parameter data. If parameter data is required, SQLParamData() will return SQL_NEED_DATA, and the parameter number that is required will be returned in the buffer at ValuePtrPtr.

You can then call SQLPutData() to pass the data for the parameter. This may be called several times to pass long data values.

You then should call SQLParamData() again to see whether any additional parameter data is required. After all required data has been sent, SQLParamData() will execute the statement and return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, provided the statement executed without error.

Parameter Information

In cases where your application code doesn't know ahead of time about the parameters required for a particular SQL statement, you can use the SQLNumParams() function to retrieve the number of parameters required and can then call SQLDescribeParam() to retrieve specifics about each parameter.

Working with Result Sets | Next Section

Previous Section