Visual C++ 6 Unleashed

Visual C++ 6 Unleashed

By MICKEY WILLIAMS and David Bennett

Developing an OLE DB Application

OLE DB development used to be quite difficult. Many OLE DB developers were forced to use the slower ActiveX Data Objects (ADO) interface to access OLE DB data sources simply because of the development time and training required to use OLE DB.

Visual C++ 6.0 changed all that. Now, OLE DB consumers can be developed using either the MFC or the ATL AppWizards. ATL executables, new to version 6, are quite smaller than corresponding MFC applications and tend to run more quickly. MFC may be slightly easier to develop, but MFC uses the same ATL classes developed in the ATL AppWizard to access OLE DB data.

This section takes you through a development process to add OLE DB functionality to an existing ATL application.

Note

The code for this chapter assumes a working dialog box is already set up. To follow the code found in this chapter, you can begin with Chap22Start and add the code as indicated. Chap22Start contains all the dialog box controls and menu items necessary to navigate through the functionality in this chapter. Chap22Final contains the finished code example that is created in this chapter, in case you need to see a fully functional example.

This chapter assumes an ODBC database named VCUnleashed is set up on your machine, with an Employee table and Department table that contain specific columns. A sample database can be found on the CD.

Finally, an error is captured and displayed whenever this program tries to go beyond the start or the end of the recordset and when a record is deleted. This error is the result of trying to update an invalid row. The error was left to illustrate the error handling routine described in this chapter.

Adding an ATL OLE DB Consumer Object

To follow this example, begin a new ATL COM EXE project. Create a dialog box to contain database column information and a menu to aid in navigation. This will usually take some time; if you want to see an example or skip this step, the starting configuration for this example is contained in the Chap22Start directory.

After you've set up a new ATL COM EXE application, click on Insert, New ATL Object from the Visual Studio menu. This opens the ATL Object Wizard dialog box. Select Data Access under Category and choose Consumer, as shown in Figure 22.2.

22fig02.gif

Figure 22.2 The ATL Object Wizard makes it easy to create a new OLE DB Consumer.

Click Next. The ATL Object Wizard Properties dialog box opens. Immediately click the Select Datasource button, as shown in Figure 22.3.

22fig03.gif

Figure 22.3 The ATL Object Wizard enables you to add properties or select data sources for your OLE DB Consumer.

After clicking the Select Datasource button, the Data Link Properties dialog box opens. Here, you select which OLE DB provider you want to write a consumer for. Usually, the choice is the ever-popular Microsoft OLE DB Provider for ODBC Drivers, as shown in Figure 22.4.

22fig04.gif

Figure 22.4 The Microsoft OLE DB Provider for ODBC Drivers enables you to access any ODBC data source as if it were an OLE DB data source.

When you click the Next button or the Connection tab, the Data Link Properties dialog box enables you to choose the name of your data source, as well as to enter username, password, and catalog information, if appropriate. For this example, VCUnleashed was chosen as the ODBC data source.

When you click OK, a list of tables available in that data source opens inside the Select Database Table dialog box. In Figure 22.5, the Employee table was chosen. When you choose which table or tables you want to access with your OLE DB consumer, click OK.

22fig05.gif

Figure 22.5 The Select Database Table dialog box enables you to choose the table for your OLE DB data source.

When you return to the ATL Wizard Properties dialog box, you can see that the names of your header file, class, and OLE DB accessor are filled in. Choose whether you want Table or Command for your data source (usually Command) and whether you want change, insert, and delete capabilities added to your OLE DB rowset (you usually do). When you're finished, click OK. A new OLE DB consumer is then generated and placed inside your Visual C++ project.

Consumer Components

Now your ATL project has a newly generated OLE DB consumer class. For many applications, you won't need to change this project. However, in this application, you need to make some changes to the consumer. This section describes the components and the changes that were made, and why.

CRowset

The main goal of an OLE DB consumer is to allow the developer to access OLE DB data in a rowset format. The CRowset class contains functionality similar to the CRecordset class in the MFC ODBC library. Table 22.1 shows some popular methods contained within the CRowset class. For a more complete list, check out the MSDN documentation.

Table 22.1. Popular CRowset Methods

Method Description
Close Releases a rowset
Delete Deletes rows from the rowset
GetData Retrieves data from the rowset's copy of the row
Insert Creates and inserts a new row
MoveFirst Repositions the next-fetch location to the first record
MoveLast Repositions the next-fetch location to the last record
MoveNext Increments the next-fetch location to the next record
MovePrev Decrements the next-fetch location to the last record
SetData Updates the rowset

CAccessor and CAccessorRowset

In order to work with the data in a rowset, you generally want to use accessor objects. Accessors are used to tell OLE DB about the structure of your client application's buffers, which are used either to hold column data from a rowset or values for command parameters.

The CAccessor class is an ATL template class. All accessors created by Visual C++ are derived from CAccessor. In our example, the accessor that is used to build our rowset is based on the CEmployeeAccessor class, which contains four components:

Note

Notice that the EmpID is left off the accessor data definition because the EmpID is an autoincrement field. Any updates or inserts to an autoincrement field cause database errors.

The ATL Wizard automatically puts all the table columns in the class. If you started working with the Chap22Start project, be sure to delete all references to EmpID. Chap22Final shows how it looks when you're finished.

Note

Again, notice that the EmpID is left off the column map. When a column is removed, you must be careful to renumber the ordinal (first parameter) of the COLUMN_ENTRY to correspond to columns from a SELECT statement. If you started with the Chap22Start project, you will need to remove the EmpID reference yourself.

CAccessorRowset encapsulates a rowset and its associated accessors in a single class. By such encapsulation, the developer can access any CRowset or CAccessor method or variable. The CAccessorRowset class is inherited by the CRowset and CAccessor classes, and is the parent class to the CCommand class and the CTable class. Figure 22.6 shows the structure of the five main OLE DB classes and how they relate to each other through inheritance.

22fig06.gif

Figure 22.6 The five main OLE DB classes are strongly interrelated.

As you can see in Figure 22.6, the CAccessor class is inherited from a base class that is defined with a template. (CEmployeeAccessor in our example.) This base class enables the CAccessor class to not only contain accessor functionality, but also to contain the data definition and SELECT statements that are used by the rowset.

CCommand

The CCommand class is inherited from the CAccessorRowset class. The CCommand class is hardly ever instantiated, but rather serves as an ancestor for other classes that can then define how their OLE DB consumer functions.

The CCommand class is inherited from a CAccessorRowset that is built from a CRowset class (CAccessor) and the superclass of the CAccessor class used for this application's data definition (CEmployeeAccessor):

class CEmployee : public CCommand<CAccessor<CEmployeeAccessor> >

The CCommand class has two main functions:

The CCommand class builds a CAccessorRowset based on an SQL command. In the Open() statement, a NULL is passed where an SQL command should go. The NULL instructs CCommand to use the default SQL found in the DEFINE_COMMAND macro in the accessor.

Data Source Properties

For some providers, you might be able to use the default properties of the provider and move right on to initializing the provider. However, in most cases, you should specify one or more properties before initializing the provider.

Properties for a data source object are divided into two property sets: The DBPROPSET_DBINIT property set contains properties that can be set before the data source is initialized, and the DBPROPSET_DATASOURCE property set contains properties that can be set after the data source is initialized.

The DBPROPSET_DBINIT property set contains the following individual properties:

In addition, you can use the DBPROPSET_DATASOURCE property set to set the DBPROP_ CURRENTCATALOG property, which determines which catalog or database is to be used within the data source. For more detail on the acceptable values for the properties listed here, see the OLE DB specification.

CCommand Properties

The properties that you can define for a property set are as follows:

Note

If you are accessing all the columns inside a table and using only one table, the CTable class is almost identical to the CCommand class. The main difference is that, instead of using a SQL command to build a CAccessorRowset, the CTable class uses a table definition. Most developers stick with the CCommand class because of its flexibility.

Using a Consumer

After creating a consumer, you need to use it to interact with the user. In our example, all the user interaction is handled through the CChap22Dialog class in the Chap22Dialog header file (Chap22Dialog.h). This section shows the code you write to effectively interact with an OLE DB consumer.

Opening and Closing a Rowset

The first step to using a consumer is to include the header file for the consumer .hin the class that needs OLE DB support:

//Added by Chuck Wood for OLE DB header file support
#include "Employee.h"

Next, you need a class variable to contain the CRowset information. In this example, the CEmployee class, inherited from CCommand, is defined using the m_Set variable at the beginning of the class definition, as shown in gray:

class CChap22Dialog :
    public CAxDialogImpl<CChap22Dialog>
{
public:

    CEmployee m_Set;    //Added by Chuck Wood for DB support

After being defined, the rowset can be opened and closed. In the CChap22Dialog constructor and destructor, the rowset is opened and closed, as shown by the lines of code in bold:

CChap22Dialog() {

    m_Set.Open();

    DoModal();
}
~CChap22Dialog() {

    m_Set.Close();

}

Updating and Inserting into Rowsets

OLE DB has two routines for updating the database. The first, CRowset.SetData(), is used to update an existing rowset from the class variables defined in the CAccessor (CEmployeeAccessor). The second, CRowset.Insert(), inserts a new row into the rowset and the database using variables defined in the CAccessor. Unlike ODBC, there is no AddNew() function or edit mode. Instead, the programmer usually controls whether an add is in progress and issues the appropriate command depending on whether an insert or update is needed. The following steps can be implemented to handle inserting and deleting inside an ATL program:

  1. Declare a Boolean flag used to indicate whether an insert is in progress. In the constructor, set this flag to FALSE. These commands are shown in gray:
    BOOL m_bInserting;    //Added by Chuck Wood for insert support

CChap22Dialog() {
    m_Set.Open();

    m_bInserting = FALSE;

    DoModal();
}

  2. The MFC contains an UpdateData() routine used for displaying information from an ODBC recordset to the dialog box and for taking information from the dialog box and updating a recordset. Similar functionality would be useful in an ATL application. The UpdateData() function shown next checks a saveChanges flag to see whether you want to save changes from the dialog box (TRUE, the default) or display contents of the rowset to the dialog box (FALSE). The insert and update functionality is shown in gray:

    //Functions written by Chuck Wood to aid in database support
void UpdateData(BOOL saveChanges=TRUE) {
    //Written by Chuck Wood for Visual C++ Unleashed
    //Mimics the MFC UpdateData function
    char salaryHolder[25];
    if (saveChanges) {
        GetDlgItemText(IDC_EMPNAME, m_Set.m_EmpName, 51);
        GetDlgItemText(IDC_DEPT, m_Set.m_Dept, 11);
        GetDlgItemText(IDC_SALARY, salaryHolder, 25);
        //Currency conversions (DB_NUMERIC) are a real pain
        double salary = atof(salaryHolder);
        //Adjust salary for scale
        salary *= pow(10, m_Set.m_Salary.scale);
        //Initialize val array to zero
        for (int loop = 0; loop < 16; loop++) {
            //initialize salary in DB to zero
            m_Set.m_Salary.val[loop] = 0;
        }
        for (loop = 0; salary > 0; loop++) {
            //Adjust hexadecimal power
            double trunc = floor(salary / (16*16));
            m_Set.m_Salary.val[loop] = salary - (trunc*16*16);
            salary = trunc;
        }
        HRESULT hr = 0;

        if (m_bInserting) {
            hr = m_Set.Insert();        //Add new row
        }
        else {
            hr = m_Set.SetData();    //Update row
        }

        m_bInserting = FALSE;
        if (FAILED(hr)) {
            showErrors();
        }
    }
    else {
        //Write data from the database to the dialog box
        SetDlgItemText(IDC_EMPNAME, m_Set.m_EmpName);
        SetDlgItemText(IDC_DEPT, m_Set.m_Dept);
    //Currency conversions (DB_NUMERIC) are a real pain
        double salary = 0;    //Initialize salary
        for (int loop = 0; loop < 16; loop++) {
            //Adjust hexadecimal power
            double power = pow(16, loop*2);
            salary += (m_Set.m_Salary.val[loop] * power);
        }
        salary /= pow(10.0, m_Set.m_Salary.scale);
        sprintf (salaryHolder, "%.2f", salary);
        SetDlgItemText(IDC_SALARY, salaryHolder);
    }
}

    Now the update functionality can be accessed from anywhere in the program. For instance, when the dialog box is closed, an UpdateData() can be called to make sure any final updates are recorded:

    LRESULT OnCancel(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled)
{

    UpdateData();

    EndDialog(wID);
    return 0;
}

Those Annoying Currency Fields

Currency fields are extremely complex for OLE DB. OLE DB uses a precision/scale macro, COLUMN_ENTRY_PS, to store the currency:

COLUMN_ENTRY_PS(2, 19, 4, m_Salary)

This results in a DB_NUMERIC structure to store salary information:

DB_NUMERIC m_Salary;

The DB_NUMERIC structure is defined as follows:

typedef struct tagDB_NUMERIC {
  BYTE precision;
  BYTE scale;
  BYTE sign;
  BYTE val[16];
} DB_NUMERIC;

The actual value of the number is stored in the val member. The number is stored in reverse-byte order, and then the number of decimal places is controlled by the scale. Microsoft Access stores currency with four decimal places.

For example, suppose you had a (base-10) salary of 120,000.12. With a scale of 4, the number would be converted to an integer, 1200001200. In hexadecimal, this would be equal to 478690B0. Because each byte holds two hexadecimal digits, the value stored in the val member would be

val Array Elements when 120,000.12 Is Stored as a Scale 4

Val Array Hex Value Decimal Value
val[0] B0 176
val[1] 90 144
val[2] 86 134
val[3] 47 071
val[4]-val[15] 00 000

Notice how the hexadecimal values are stored in reverse order.

To convert such a structure to decimal requires some programming. First, you should include the cmath header file:

#include <cmath> //Added by Chuck Wood for pow() support

To convert from a DB_NUMERIC structure to a double type, you need to use a loop. Convert each byte of the val array to the appropriate number by multiplying by a power of 16. Then add each byte's adjusted value to the salary. Finally, divide by the scale. The routine to handle the conversion can be seen in the following:

//Currency conversions (DB_NUMERIC) are a real pain
double salary = 0;    //Initialize salary
for (int loop = 0; loop < 16; loop++) {
    //Adjust hexadecimal power
    double power = pow(16, loop*2);
    salary += (m_Set.m_Salary.val[loop] * power);
}
salary /= pow(10.0, m_Set.m_Salary.scale);

To convert from a double to a DB_NUMERIC, you need to perform the following steps:

  1. Multiply the salary by the appropriate scale:
    //Adjust salary for scale
salary *= pow(10, m_Set.m_Salary.scale);
  2. Initialize the val array to contain zero in each element:
            //Initialize val array to zero
        for (int loop = 0; loop < 16; loop++) {
            //initialize salary in DB to zero
            m_Set.m_Salary.val[loop] = 0;
        }
  3. In a loop, subtract each two-digit hexadecimal number, and then remove two digits from the salary until the salary is equal to zero:
    for (loop = 0; salary > 0; loop++) {
    //Adjust hexadecimal power
    double trunc = floor(salary / (16*16));
    m_Set.m_Salary.val[loop] = salary - (trunc*16*16);
    salary = trunc;
}

Conversions to and from DB_NUMERIC fields to double types are cumbersome, but these conversions enable effective processing of special database types, such as the Currency type.

  1. Finally, write an insert routine. This routine is called when the user requests a new record. It updates any changes that the user has made in the dialog box using the UpdateData() routine and then calls an insertRecord() function to set up an insert:

    RESULT OnInsert(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled) {
    UpdateData();    //Update database
    insertRecord();    //Set up an insert
    return 0;
}

    The insertRecord() function clears the dialog box fields using the ATL Object Wizard–generated ClearRecord function. It then displays the blank fields using a UpdateData(FALSE) call and sets the insert flag:

    Lvoid insertRecord() {    //Set up inserting
    m_Set.ClearRecord();  //Clear dialog box fields
    UpdateData(FALSE);    //Clear window
    m_bInserting = TRUE;  //Initialize add flag
}

Note

Unlike ODBC, OLE DB has no edit modes, so the OnInsert() and insertRecord() functions don't actually do any inserting. Rather, they set up the user to insert a new record, and the UpdateData() routine handles the actual updates and inserts when the user is finished typing information for the new record.

Navigating Through a Rowset

Navigating through a rowset involves using the CRowset.MoveFirst(), CRowset.MovePrev(), CRowset.MoveNext(), and CRowset.MoveLast() functions. Each of these functions is called through menu routines that enable the user to navigate through the rowset.

The OnFirst routine is split into two functions. The menu function updates the current record and then calls the firstRecord() function to display the first record:

//The rest of this class added by Chuck Wood for OLE DB functionality
LRESULT OnFirst(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled)
{
    UpdateData();    //Update database
    firstRecord();
    return 0;
}
void firstRecord() {    //Go to the first record
    if (m_Set.MoveFirst() == S_OK) {
        UpdateData(FALSE);    //Display record
    }
    else {
        showMessage("No records found. Inserting new Record");
        insertRecord();
    }
}

The reason the OnFirst() function is split into two functions is because the OnInitDialog() routine needs to display the first record without updating any existing records:

LRESULT OnInitDialog(UINT uMsg, WPARAM wParam, LPARAM lParam, BOOL& bHandled)
{

firstRecord();    //Display first record

    return 1;  // Let the system set the focus
}

The rest of the navigation functions don't need to be split:

LRESULT OnPrev(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled) {
    showMessage("");
    UpdateData();    //Update database
    if (m_Set.MovePrev() == S_OK) {
        UpdateData(FALSE);    //Display record
    }
    else {
        showMessage("No previous records found");
        OnFirst(wNotifyCode, wID, hWndCtl, bHandled);
    }
    return 0;
}
LRESULT OnNext(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled) {
    showMessage("");
    UpdateData();    //Update database
    if (m_Set.MoveNext() == S_OK) {
        UpdateData(FALSE);    //Display record
    }
    else {
        showMessage("No more records found");
        OnLast(wNotifyCode, wID, hWndCtl, bHandled);
    }
    return 0;
}
LRESULT OnLast(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled) {
    UpdateData();    //Update database
    if (m_Set.MoveLast() == S_OK) {
        UpdateData(FALSE);    //Display record
    }
    else {
        showMessage("No records found. Inserting new Record");
        insertRecord();
    }
    return 0;
}

Deleting Rows from a Rowset

Deleting rows from a rowset (and, hence, from a database table) is always problematic. Unless transactions are used, there is no way to undo a delete. Make sure that the user didn't accidentally press the delete option and that there really is a record present to delete, and not a newly inserted record soon to be deleted. The following steps can be used for a delete:

  1. Make sure the user really wants a delete:
    if (MessageBox("Are you sure you want to delete?",
        "Confirm Delete",
        MB_YESNO) != IDYES) {
    return 0;            // Get outta here
}
  2. Make sure that an actual record exists to be deleted. If you are in the middle of an insert and you issue a delete, the newly inserted record won't be deleted, but rather the last record viewed before the insert will be deleted. This could cause some serious problems for the user:
    if (m_bInserting) {
    m_bInserting = FALSE;    //Just reset flag, ...
    UpdateData(FALSE);    // display current record, ...
    return 0;             // and get outta here
}
  3. Delete the record using the CRowset.Delete() method:
    m_Set.Delete();
  4. Now that the current row is deleted, it is no longer valid. Try moving to a different row, or if there are no more rows, inserting a new record using the insertRecord() function:
        if (FAILED(m_Set.MoveNext())) {
        if (FAILED(m_Set.MoveLast())) {
            insertRecord();
            return 0;
        }
    }
    UpdateData(FALSE);    //Display record

These steps can be seen in the OnDelete() function that is called when the user chooses the delete menu option:

LRESULT OnDelete(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled)
{
    showMessage("");
    if (MessageBox("Are you sure you want to delete?",
            "Confirm Delete",
            MB_YESNO) != IDYES) {
        return 0;            // Get outta here
    }
    //See if you can just cancel add
    if (m_bInserting) {
        m_bInserting = FALSE;    //Just reset flag, ...
        UpdateData(FALSE);    // display current record, ...
        return 0;             // and get outta here
    }
    if (FAILED(m_Set.Delete())) {
        //Delete failed.  Display messages.
        MessageBox("Could not delete record", "Database Error");
        showMessage("Could not delete record");
        return 0;             // Get outta here
    }
    //Find a new record
    if (FAILED(m_Set.MoveNext())) {
        if (FAILED(m_Set.MoveLast())) {
            insertRecord();
            return 0;
        }
    }
    UpdateData(FALSE);    //Display record
    return 0;
}

Now you're finished with this OLE DB application. If you've been following the ex ample, your application should open a dialog box similar to the one seen in Figure 22.7.

22fig07.gif

Figure 22.7 It's easy to create OLE DB applications using the ATL AppWizard and the ATL Object Wizard.

Catching OLE DB Errors

Database errors are generated by the database engine. These errors often give the developer more insight into any potential problems still present in an application.

The main class for processing multiple database errors is the CDBErrorInfo class. This class provides support for one or more OLE DB error records that are returned to the user. To use this class, perform the following steps:

  1. Call CDBErrorInfo.GetErrorRecords() to retrieve the number of OLE DB database errors:
    ULONG numErrors = 0;
CDBErrorInfo errorInfo;
errorInfo.GetErrorRecords(m_Set.m_spCommand, IID_ICommandPrepare,
&numErrors);
  2. If the number of errors is successfully retrieved, retrieve error information into an IErrorInfo interface using the CDBErrorInfo.GetErrorInfo() function in a loop. Use the IErrorInfo.GetDescription() method to retrieve each error message:
    BSTR sDescription = NULL;
IErrorInfo *pErrorInfo = NULL;
LCID lcid = GetUserDefaultLCID();
for (ULONG loop = 0; loop < numErrors; loop++) {
     errorInfo.GetErrorInfo(loop, lcid, &pErrorInfo);
     pErrorInfo->GetDescription(&sDescription);
}

These steps can be viewed in the showErrors function:

void showErrors() {
    CDBErrorInfo errorInfo;
    IErrorInfo *pErrorInfo = NULL;
    ULONG numErrors = 0;
    if (FAILED(errorInfo.GetErrorRecords(m_Set.m_spCommand,
IID_ICommandPrepare, &numErrors))) {
         MessageBox("Error information was not retrievable", "Database
Error");
        return;
    }
    if (FAILED(GetErrorInfo(0, &pErrorInfo))) {
        MessageBox("Error information was not retrievable", "Database
Error");
        return;
    }
    char message[4096];
    strcpy (message, "");
    LCID lcid = GetUserDefaultLCID();
    for (ULONG loop = 0; loop < numErrors; loop++) {
        if (FAILED(errorInfo.GetErrorInfo(loop, lcid, &pErrorInfo))) {
            continue;
        }
        BSTR sDescription = NULL;
        pErrorInfo->GetDescription(&sDescription);
        sprintf(message, "%s%S", message, sDescription);
        SysFreeString(sDescription);    //Clean up
        pErrorInfo->Release();
    }
    MessageBox(message, "Database Error");
}

This function is handy for collecting errors for any failed database operation. Simply use the FAILED macro to test the HRESULT of any database operation and show the errors if a failure occurs:

HRESULT hr = {some database operation}
if (FAILED(hr)) {
    showErrors();
}

Note

The example still has some errors that are used to show error functionality. When the user scrolls past all valid records, UpdateData() is still called, resulting in an Invalid Row Handle error. The showError routine retrieves this error and displays it, as shown in Figure 22.8.

22fig08.gif

Figure 22.8 The showErrors routine is designed to display one or more OLE DB database errors.

Share ThisShare This

Informit Network