Visual C++ 6 Unleashed

Visual C++ 6 Unleashed

By MICKEY WILLIAMS and David Bennett

The CDatabase Class

The MFC CDatabase class is used to encapsulate your application's dealings with a connection to the database. This may be a connection to a database server over the network, or it may just be used to keep track of your settings for a desktop database on the local machine. In most cases, the methods associated with CDatabase correspond directly to the functions of the ODBC C API that work with connection handles.

You can retrieve the CDatabase class associated with your CRecordset class by using the m_pSet->m_pDatabase CRecordset class variable.

Executing SQL Statements with CDatabase

For general queries that return resultsets, it usually is easier to use the CRecordset class, which you will look at next. However, you can execute SQL statements that do not return resultsets without using a CRecordset. You do this by calling CDatabase::ExecuteSQL(),:

void ExecuteSQL( LPCSTR lpszSQL );

This function simply takes a SQL string passed in lpszSQL and executes it against the current datasource. Notice that ExecuteSQL() does not return a value. If something goes wrong (for example, the SQL statement fails), a CDBException is thrown. Your application needs to catch these in order to determine whether the statement did not execute properly.

OnSetOptions()

When you call ExecuteSQL(), the MFC framework makes a call to the OnSetOptions() member of CDatabase before it sends the SQL statement to the database. This allows the CDatabase object to set up any options that are required before executing SQL statements.

In the default implementation, this function simply sets the SQL_QUERY_TIMEOUT option for the statement handle that will be used for the operation to the value that was specified with a call to CDatabase::SetQueryTimeout(). You can add any other options you may need by deriving your own class from CDatabase and overriding the OnSetOptions() member function, which is passed the statement handle that MFC will use to execute the statement.

TIP

The CDatabase class provides an additional, undocumented, virtual function, which gives your class the opportunity to bind parameters to a statement executed with ExecuteSQL(). To use this function, you derive your own class from CDatabase and override the BindParameters() function:

    void CDatabase::BindParameters(HSTMT hstmt);

To actually bind parameters, you use the ODBC C API, as shown in the preceding chapter. When doing these sorts of things, it is very useful to take a look at the source code for the MFC database classes, included with Visual C++ in DBCORE.CPP.

Transactions with CDatabase

The CDatabase class also is responsible for managing transactions for the database connection. Transactions enable you to execute a series of SQL statements as a single operation. If one of the operations in a transaction fails, the rest of the operations in the transaction also can be undone.

This feature is most useful when you need to make several different, but related, changes to a database. If you are entering a sales order, for example, you may want to update both your Shipping table and your Billing table. If you update one of these and the other update fails, you can expect a few extra customer service calls.

Note

Not all ODBC drivers support transactions. You can find out whether the current driver supports transactions by calling CDatabase::CanTransact().

To begin a transaction using the MFC ODBC classes, call CDatabase::BeginTrans(). You then can execute the operations that make up the transaction by calling CDatabase::ExecuteSQL() or by using CRecordset objects derived from this CDatabase.

CAUTION

For some ODBC drivers, calling BeginTrans() when CRecordset objects already have been opened can cause problems with Rollback() processing. To avoid difficulties, it is best to call BeginTrans() first, and then open the recordsets that will be used for the transaction.

A transaction can end in one of two ways. If all of the operations were successful and you want to go through with the transaction, you should call CDatabase::CommitTrans(). If an error has occurred, and you want to cancel the transaction, you should call CDatabase::Rollback(), which will undo all the operations performed with this CDatabase (and any derived recordsets) since the call to BeginTrans().

This example shows a simple transaction involving a row insertion made by calling ExecuteSQL(): 

try {
    m_pSet->m_pDatabase->BeginTrans();
    m_pSet->m_pDatabase->ExecuteSQL(
        "INSERT INTO Employee VALUES ('Joe Beancounter', 'Accounting', 80000)");
    m_pSet->m_pDatabase->CommitTrans())
        TRACE("Transaction Commited\n");
    else
        TRACE("Error in CommitTrans\n");
}
catch(CDBException *pEx) {
    pEx->ReportError();
    m_pSet->m_pDatabase->Rollback();
}

Effects of Transactions on CRecordsets

Ending a transaction on a CDatabase object can have different effects on the CRecordsets that are created from it, depending on the ODBC driver that you are using. Calling CRecordset::GetCursorCommitBehavior() and CDatabase::RollbackBehavior() en ables you to find out how your driver affects CRecordsets when you call CommitTrans() or Rollback(). Both of these functions will return one of the following values:

Using the ODBC API Directly

For some applications, the MFC database classes may not give you quite enough control over the database interaction. If you need to do anything not directly supported by the MFC classes, you can call the ODBC C API directly. Of course, to call the ODBC API, you need ODBC handles. Well, it just so happens that you can get the ODBC connection handle from the m_hdbc member of CDatabase. You also can get statement handles from the m_hstmt member of the CRecordset class.

You may freely mix ODBC C API calls with use of the MFC database classes in your applications. However, you may want to take a look at the source code for the MFC classes to see how the MFC member functions use the ODBC API.

Share ThisShare This

Informit Network