Teach Yourself Visual C++ 6 in 21 Days

Previous chapterNext chapterContents


- 14 -
Retrieving Data from an ODBC Database



A large number of applications use a database. Everything from a personal organizer to a large, corporate personnel system uses a database to store and maintain all the records that the applications use and manipulate. Visual C++ provides you with four different technologies for using and accessing databases in your applications, Data Access Objects (DAO), ODBC, OLE DB, and ActiveX Data Objects (ADO). Today and tomorrow, you'll learn about two of these technologies, how they differ, and how you can use them in your own applications. Today, you will learn

Database Access and ODBC

Most business applications work with data. They maintain, manipulate, and access records of data that are stored in databases. If you build business applications, odds are that you will need to be able to access a database with your applications. The question is, which database?

There are a number of databases on the market. If you need to create a single-user application that is self-contained on a single computer, you can use any one of numerous PC-based databases, such as Microsoft's Access, FoxPro, or Borland's Paradox. If you are building applications that need to access large, shared databases, you are probably using an SQL-based (Structured Query Language) database such as SQL Server or Oracle. All of these databases provide the same basic functionality, maintaining records of data. Each will allow you to retrieve several records or a single record, depending on your needs. They'll all let you add, update, or delete records as needed. Any of these data- bases will be able to serve your application's needs, so you should be able to use any database for one application and then switch to another for the next application, based on the needs of the application and which database is most suited for the specific application needs (or your employer's whim).


NOTE: To be completely honest, there are numerous differences between the various databases that are available today. Each of these databases has specific strengths and weaknesses, making one more suitable for a specific situation than another. However, a discussion of the differences between any of these databases is beyond the scope of this book. For the discussions of databases today and tomorrow, you can assume that all of these databases are functionally equal and interchangeable.

The problem that you will encounter when you switch from one database to another is that each database requires you to use a different interface for accessing the database. Therefore, you have to learn and use a whole new set of programming techniques and functions for each database that you need to work with. This is the problem that the ODBC interface was designed to correct.

The Open Database Connector (ODBC) Interface

Microsoft saw the incompatibility between database interfaces as a problem. Each database had its own application development language that was well integrated with the database but didn't work with any other database. This presented a problem to any developer who needed to use one database for an application and then a different database for the next application. The developer had to learn the specific development language for each of the databases and couldn't use any languages that she already knew. For programmers to work with any database with the programming language of the developer's choice, they needed a standardized interface that works with every database.

The Open Database Connector (ODBC) interface is implemented as a standard, SQL-based interface that is an integral part of the Windows operating system. Behind this interface are plug-ins for each database that take the ODBC function calls and convert them into calls to the specific interface for that database. The ODBC interface also uses a central set of database connection configurations, with a standardized way of specifying and maintaining them. This setup allows programmers to learn and use a single database interface for all databases. This also allowed programming language vendors to add ODBC support into their languages and development tools to make database access all but transparent.

The CRecordset Class

In the Visual C++ development environment, most of the ODBC functionality has been encapsulated into two classes, CRecordset and CDatabase. The CDatabase class contains the database connection information and can be shared across an entire application. The CRecordset class encapsulates a set of records from the database. The CRecordset class allows you to specify a SQL query to be run, and the CRecordset class will run the query and maintain the set of records that are returned by the database. You can modify and update the records in the record set, and your changes will be passed back to the database. You can add or delete records from the record set, and those same actions can be passed back to the database.

Connecting to the Database

Before the CRecordset class can perform any other functions, it has to be connected to a database. This is accomplished through the use of the CDatabase class. You don't need to create or set the CDatabase instance; the first instance of the CRecordset class does this for you. When you create an application using the AppWizard and choose to include ODBC database support, the AppWizard includes the database connection information in the first CRecordset-derived class that it creates. When this CRecordset class is created without being passed a CDatabase object, it uses the default connection information, which was added by the AppWizard, to create its own database connection.

Opening and Closing the Record Set

Once the CRecordset object is created and connected to the database, you need to open the record set to retrieve the set of records from the database. Do this by calling the Open member function of the CRecordset object. You can call this function without any arguments if you want to take the default values for everything, including the SQL statement to be executed.

The first argument to the Open function is the record set type. The default value for this, AFX_DB_USE_DEFAULT_TYPE, is to open the record set as a snapshot set of records. Table 14.1 lists the four types of record set types. Only two of these record set types are available in the AppWizard when you are specifying the data source.

TABLE 14.1. RECORD SET TYPES.

Type Description
CRecordset::dynaset A set of records that can be refreshed by calling the Fetch function so that changes made to the record set by other users can be seen.
CRecordset::snapshot A set of records that cannot be refreshed without closing and then reopening the record set.
CRecordset::dynamic Very similar to the CRecordset::dynaset type, but it is not available in many ODBC drivers.
CRecordset::forwardOnly A read-only set of records that can only be scrolled from the first to the last record.

The second argument to the Open function is the SQL statement that is to be executed to populate the record set. If a NULL is passed for this argument, the default SQL statement that was created by the AppWizard is executed.

The third argument is a set of flags that you can use to specify how the set of records is to be retrieved into the record set. Most of these flags require an in-depth understanding of the ODBC interface so you understand how the flags can and should be used in your applications. Because of this, I'll discuss only a few of these flags in Table 14.2.

TABLE 14.2. RECORD SET OPEN FLAGS.

Flag Description
CRecordset::none The default value for this argument; specifies that no options affect how the record set is opened and used.
CRecordset::appendOnly This flag prevents the user from being able to edit or delete any of the existing records in the record set. The user will only be able to add new records to the set of records. You cannot use this option with the CRecordset::readOnly flag.
CRecordset::readOnly This flag specifies that the record set is read-only and no changes can be made by the user. You cannot use this option with the CRecordset::appendOnly flag.

Once the user finishes working with the record set, you can call the Close function to close the record set and free any resources used by the record set. The Close function doesn't take any arguments.

Navigating the Record Set

Once you have a set of records retrieved from the database, you need to be able to navigate the set of records (unless the set has only one record). The CRecordset class provides several functions for navigating the record set, allowing you to move the user to any record. Table 14.3 lists the functions that you use to navigate the record set.

TABLE 14.3. RECORD SET NAVIGATION FUNCTIONS.

Function Description
MoveFirst Moves to the first record in the set.
MoveLast Moves to the last record in the set.
MoveNext Moves to the next record in the set.
MovePrev Moves to the previous record in the set.
Move Can be used to move a specific number of records from the current record or from the first record in the set.
SetAbsolutePosition Moves to the specified record in the set.
IsBOF Returns TRUE if the current record is the first record in the set.
IsEOF Returns TRUE if the current record is the last record in the set.
GetRecordCount Returns the number of records in the set.

Of all of these navigation and informational functions, only two, Move and SetAbsolutePosition, take any arguments. The SetAbsolutePosition function takes a single numeric argument to specify the row number of the record toward which to navigate. If you pass 0, it navigates to the beginning-of-file (BOF) position, whereas 1 takes you to the first record in the set. You can pass negative numbers to this function to cause it to count backward from the last record in the set. (For example, -1 takes you to the last record in the set, -2 to the next-to-last record, and so on.)

The Move function takes two arguments. The first argument is the number of rows to move. This can be a positive or negative number; a negative number indicates a backward navigation through the record set. The second argument specifies how you will move through the set of rows. The possible values for the second argument are listed in Table 14.4 with descriptions of how they affect the navigation.

TABLE 14.4. MOVE NAVIGATION TYPES.

Type Description
SQL_FETCH_RELATIVE Moves the specified number of rows from the current row.
SQL_FETCH_NEXT Moves to the next row, ignoring the number of rows specified. The same as calling the MoveNext function.
SQL_FETCH_PRIOR Moves to the previous row, ignoring the number of rows specified. The same as calling the MovePrev function.
SQL_FETCH_FIRST Moves to the first row, ignoring the number of rows specified. The same as calling the MoveFirst function.
SQL_FETCH_LAST Moves to the last row, ignoring the number of rows specified. The same as calling the MoveLast function.
SQL_FETCH_ABSOLUTE Moves the specified number of rows from the start of the set of rows. The same as calling the SetAbsolutePosition function.

Adding, Deleting, and Updating Records

Navigating a set of records from a database is only part of what you need to be able to do. You also need to be able to add new records to the record set, edit and update existing records, and delete records. These actions are all possible through the various functions that the CRecordset class provides. The functions that you will use to provide this functionality to the user are listed in Table 14.5.

TABLE 14.5. RECORD SET EDITING FUNCTIONS.

Function Description
AddNew Adds a new record to the record set.
Delete Deletes the current record from the record set.
Edit Allows the current record to be edited.
Update Saves the current changes to the database.
Requery Reruns the current SQL query to refresh the record set.

None of these functions takes any arguments. However, some of them require following a few specific steps to get them to work correctly.

To add a new record to the database, you can call the AddNew function. The next thing that you need to do is set default values in any of the fields that require values, such as the key fields. Next, you must call the Update function to add the new record to the database. If you try to navigate to another record before calling the Update function, the new record will be lost. Once you save the new record, you need to call the Requery function to refresh the record set so that you can navigate to the new record and let the user edit it. This sequence of function calls typically looks like the following:

// Add a new record to the record set
m_pSet.AddNew();
// Set the key field on the new record
m_pSet.m_AddressID = m_lNewID;
// Save the new record to the database
m_pSet.Update();
// Refresh the record set
m_pSet.Requery();
// Move to the new record
m_pSet.MoveLast();

When you need to delete the current record, you can simply call the Delete function. Once you delete the current record, you need to navigate to another record so the user isn't still looking at the record that was just deleted. Once you delete the current record, there is no current record until you navigate to another one. You do not need to explicitly call the Update function because the navigation functions call it for you. This allows you to write the following code to delete the current record:

// Delete the current record
m_pSet.Delete();
// Move to the previous record
m_pSet.MovePrev();

Finally, to allow the user to edit the current record, you need to call the Edit function. This allows you to update the fields in the record with the new values entered by the user or calculated by your application. Once all changes are made to the current record, you need to call the Update function to save the changes:

// Allow the user to edit the current record
m_pSet.Edit();
// Perform all data exchange, updating the fields in the recordset
.
.
// Save the user's changes to the current record
m_pSet.Update();

You might be wondering how you get to the fields in the records to update them. When the AppWizard creates the CRecordset-derived class for your application, it adds all the fields in the records that will be in the record set as member variables in order of the record set class. As a result, you can access the member variables in order to access and manipulate the data elements in the database records that are members of the record set.

Creating a Database Application Using ODBC

For the sample application that you will build today, you'll create an SDI application with ODBC database support. The application will retrieve records from an ODBC database, allowing the user to edit and update any of the records. You'll also add function- ality to enable the user to add new records to the database and to delete records from the database.

Preparing the Database

Before you can begin building an application that uses a database, you need a database to use with your application. Almost every database that you can purchase for your applications comes with tools for creating a new database. You'll need to use these tools to create your database and then use the ODBC administrator to configure an ODBC data source for your new database.

For the sample application in this chapter, I used Access 95 to create a new database. I used the Access Database Wizard to create the database, choosing the Address Book database template as the database to be created. When the Database Wizard started, I selected the default set of fields for including in the database and selected the option to include sample data, as shown in Figure 14.1. I then accepted the rest of the default settings offered in the Database Wizard.

FIGURE 14.1. Including sample data in the database.

Once you create the database, you need to configure an ODBC data source to point to the database you just created. To do this, run the ODBC Administrator, which is in the Control Panel on your computer.

Once in the ODBC Administrator, you'll add a new data source. You can do this by clicking the Add button, as shown in Figure 14.2. This opens another dialog, which allows you to select the database driver for the new data source, as shown in Figure 14.3. For the sample application that you will build today, because the database was created using Access, select the Microsoft Access Driver and click the Finish button.

FIGURE 14.2. The ODBC Data Source Administrator.

FIGURE 14.3. The Create New Data Source dialog.

In the ODBC Microsoft Access Setup dialog, shown in Figure 14.4, you'll provide a short, simple name for the data source. Your application will use this name to specify the ODBC data source configuration to use for the database connection, so it should reflect the function that the database will be serving, or it should be similar to the name of the application that will be using this database. For the purposes of the sample application database, name your data source TYVCDB (for Teach Yourself Visual C++ Database) and enter a description for the database in the next field.

Once you enter a name and description for the data source, you need to specify where the database is. Click the Select button and then specify the Access database that you created. Once you finish configuring the ODBC data source for your database, click the OK button to add the new data source to the ODBC Administrator. You can click the OK button to finish the task and close the ODBC Administrator because you are now ready to turn your attention to building your application.

FIGURE 14.4. The ODBC Microsoft Access 97 Setup dialog.

Creating the Application Shell

For the sample application that you will build today, you'll create a standard SDI-style application with database support. First, start a new project, selecting the AppWizard, and give your application a suitable name, such as DbOdbc.

On the first AppWizard form, specify that you want to build an SDI application. On the second AppWizard form, specify that you want to include Database view with file support. Click the Data Source button to specify which data source you will use in your application. In the Database Options dialog, specify that you are using an ODBC data source, and select the ODBC configuration from the list that you configured for your Access database, as shown in Figure 14.5. You can set the record set type to either Snapshot or Dynaset.

FIGURE 14.5. The Database Options dialog.

Once you click the OK button, another dialog opens, presenting you with the available tables in the database you selected. Select the Addresses table, as shown in Figure 14.6, and click the OK button to close this dialog and return to the AppWizard.

You can continue through the rest of the AppWizard, accepting all of the default settings. When you reach the final AppWizard step, you'll notice that the AppWizard is going to create an extra class. If you select this class, you'll see that it is derived from the CRecordset class, and it is the record set class for your application. You'll also notice that the view class is derived from the CRecordView class, which is a descendent of the CFormView class, with some added support for database functionality.

FIGURE 14.6. The Select Database Tables dialog.

Designing the Main Form

Once you create the application shell, you need to design the main form that will be used for viewing and editing the database records. You can design this form using the standard controls that are part of Visual C++, without adding any special ActiveX controls. For designing the main form in your sample application, lay out the main form as shown in Figure 14.7, and configure the controls with the properties specified in Table 14.6.


TIP: If you want to save a little time when building the example, you can leave out most of the controls and database fields from the application. The key fields that you'll need to include are ID, First and Last Names, Birthdate, and Send Card. If you want to leave out the other fields from the application, that's fine.

TABLE 14.6. CONTROL PROPERTY SETTINGS.

Object Property Setting
Static Text ID IDC_STATIC

Caption ID:
Edit Box ID IDC_EID
Static Text ID IDC_STATIC

Caption First Name:
Edit Box ID IDC_EFNAME
Static Text ID IDC_STATIC

Caption Last Name:
Edit Box ID IDC_ELNAME
Static Text ID IDC_STATIC

Caption Spouse Name:
Edit Box ID IDC_ESNAME
Static Text ID IDC_STATIC

Caption Address:
Edit Box ID IDC_EADDR

Multiline Checked
Static Text ID IDC_STATIC

Caption City:
Edit Box ID IDC_ECITY
Static Text ID IDC_STATIC

Caption State:
Edit Box ID IDC_ESTATE
Static Text ID IDC_STATIC

Caption Zip:
Edit Box ID IDC_EZIP
Static Text ID IDC_STATIC

Caption Country:
Edit Box ID IDC_ECOUNTRY
Static Text ID IDC_STATIC