Teach Yourself Database Programming
with Visual C++ 6 in 21 days
Although considered legacy, the APIs that you will learn about today provide some valuable insight into the structured nature of developing database applications. Merriam Webster defines legacy as "being from the past," but you can hardly limit the content of this chapter to dusty old relics that need only cursory explanation. Although ODBC and DAO APIs might no longer be applicable in mainstream coding circles, the technology provides the foundation for most databases supported today.
Today you will
| NOTE |
This book focuses primarily on the newer OLE DB (ADO) technologies, but remember that it is still in its infancy and OLE DB providers for many databases are still in development. With this in mind, it is easy to see the importance of understanding these legacy interfaces. Who knows, you might have to provide support for an application using these APIs. |
Databases, and their programming APIs, come in a variety of flavors. Many different databases are available to the developer, and each has a specific set of programming APIs. SQL was an attempt to standardize the database programming interface. However, each database implementation of SQL varies slightly.
| NOTE |
ANSI SQL-92 is the latest and most supported version of SQL, but the specification only provides a guideline. It is up to the database vendor to support all or part, as well as additional elements of the specification. |
ODBC was the first cohesive attempt to provide an application layer that would allow access to many different databases. ODBC provided a consistent specification for database vendors to develop ODBC drivers that applications could connect to. Applications can make function calls to the ODBC driver to send data to and receive data from a database, or in some cases multiple databases.
ODBC provides standardized access to databases. This enables the application developer to better concentrate on the application and its user interface and not have to worry about database specifics for every possible database on the market. To make things even simpler, the developers of ODBC decided to implement the API layer as a SQL translation mechanism. By passing ODBC SQL to an ODBC driver, the application can communicate with the database using SQL. Because there are many different flavors of SQL, ODBC provides a single flavor that would be translated into a flavor that the database could read.
| NOTE |
You might have heard that an ODBC driver is Level X-compliant as related to the API. What does this mean? There are three levels of compliance: Core Level-All drivers must support this level. Must be able to support connections, SQL statement preparation and execution, data set management, and transaction management. Level 1-Must support all core-level compliance, as well as dialog-based connectivity, and be able to obtain driver/datasource information, which includes advanced connections using get and set options. Level 2-Must support all the previous levels, plus the capability to list and search the datasource connections, and advanced query mechanisms and have support for scrollable cursors, among other things. |
Figure 14.1 : The ODBC architecture overview.
The Driver Administrator is a Control Panel applet responsible for defining the ODBC data sources. A data source is simply the connection definition to a specific database. The connection definition contains information about the type of database, as well as the pertinent location information for the database. It then assigns a common name, called the Data Source Name (DSN), to the definition. The ODBC Driver Manager and drivers use the name as an index into the data source table to find the database-specific information.
Refer to Day 2, "Tools for Database Development in Visual C++ Developer Studio," for a description of the steps to define DSNs.
The ODBC Driver Manager is a set of functions that receives requests from the application and manages the subsequent driver actions for those requests. The primary functions of the Driver Manager are to load and unload database drivers and pass function calls to the driver. You might be thinking that this is a little bit of overkill. Why not just call the driver directly? Wouldn't it be faster? Just imagine, however, if the Driver Manager didn't exist, and your application was responsible for loading, unloading, and maintaining driver connections. Your application would be responsible for every possible driver configuration available, including the data source definitions. (Registry programming, anyone?) The Driver Manager makes the application developer's life easy, by compartmentalizing this functionality.
If you look a little closer at the Driver Manager, you see that it does perform some processing related to your application's requests. It implements some of the functions of the ODBC API. These include SQLDataSources, SQLDrivers, and SQLGetFunctions. It also performs some basic error checking, function call ordering, checking for null pointers, and validating of function arguments and parameters.
| NOTE |
Note that the ODBC API calls start with SQL. There is good reason for this. The ODBC API communicates through the SQL database interface instead of calling the database's lower layer. This is done primarily to add some level of consistency and standardization. ODBC does this by mapping ODBC SQL to the database driver's specific SQL standard. Hence, the naming convention for ODBC API function calls. |
When the Driver Manager loads a driver, it stores the address of each function call in the driver and then tells the driver to connect to the data source. The application specifies which data source to connect to, using the data source name. The Driver Manager searches the DSN definition file for the particular driver to load. When the application is done, it tells the Driver Manager to disconnect (SQLDisconnect). The Driver Manager in turn hands this to the connected driver, which disconnects from the data source. The Driver Manager will unload the driver from memory only when the application frees the connection. The driver is kept in memory in case the application developer decides he needs further access.
To adequately discuss every aspect of developing ODBC drivers would require another book. However, a cursory discussion is warranted. An ODBC driver must perform the following:
A driver can be defined as one of two types. A file-based driver accesses the physical data directly. A DBMS-based driver doesn't access the data directly but performs SQL functions on another wrapper. This wrapper is referred to as an engine. The database engine for Microsoft Access is the Jet engine.
Now that I've discussed the architecture of the ODBC specification, let's take a look at how to develop an application by using the ODBC API.
| NOTE |
Although this section introduces certain steps in developing an ODBC application, it isn't intended to be a complete reference. Many ODBC programming references are available that provide in-depth discussions about the API function calls. |
Before I discuss the API function calls, let's make some sense out of processing a data request. First you have to know where the data is (data source definition). Then you have to connect to it. After you are connected, you need to ask the data source for information. After the information is in hand, you process it and in most cases hand it back to the data source for safe keeping. When you are finished, you disconnect from the data source.
Figure 14.2 : The ODBC programmatic flow chart.
First you have to acquire an environment handle. Do this by calling SQLAllocHandle. At this point you might be asking what an environment handle is. A handle is nothing more than a pointer to a special structure. The environment mentioned here is generally considered the system and data source information that the Driver Manager needs to store for the driver. You might also be asking why the Driver Manager does this, not the driver. Recall that you have not connected yet, and therefore the Driver Manager doesn't know what driver you will be using and will hold this information until it is needed. Some applications might need to connect to multiple databases. If the Driver Manager did not exist, the application would have to keep track of all the environment handles.
SQLHENV envHandle1; SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE, &envHandle1);
After the Environment Handle is allocated, the application must then determine the attributes for the handle. The most important of these attributes is the ODBC version attribute (SQL_ATTR_ODBC_VERSION). Different versions of ODBC support different SQL statements and parameters. In some cases, it is important to determine the ODBC version that the driver supports and to know the differences.
You can think of a statement in ODBC as being a SQL statement. As discussed earlier, ODBC communicates with the SQL interface to the database. The Driver Manager maps the ODBC SQL to the driver's SQL. However, a statement also carries attributes with it that define it in the context of the connection to the data source. This includes, but is certainly not limited to, the resultsets that the statement creates. Some statements require specific parameters in order to execute. These parameters are also considered attributes of the statement. Therefore, each statement has a handle that points to a structure that defines all the attributes of the statement. This handle also assists the driver in keeping track of the statements, because a multitude of statements can be associated with a connection.
Statement handles are defined and allocated similarly to the environment handle. However, the handle type is HSTMT. Remember that the Driver Manager allocates the handle structure and hands this off to the driver whenever the connection to the driver is made.
Here's where things can differ depending on what the application requires. If an application just wants to read data from the database and display it to the user (that is, database viewer application), it won't require some of the more complex SQL UPDATEs, INSERTs, or DELETEs.
| NOTE |
Because Day 15, "The ODBC API and the MFC ODBC Classes," discusses the binding of parameters, this section skips the explanation and demonstration of how to bind SQL parameters to the application's data. However, it is important to bear in mind that when you are preparing a SQL statement, this binding must take place. |
There are two primary ways to prepare and execute the statements. The first is SQLExecDirect, which essentially executes a SQL statement in a single step. For many application requirements, this is okay. Some applications, however, might need to execute the same statement several times. To do this, you should use the SQLPrepare and then the SQLExecute functions. You call SQLPrepare once and then call SQLExecute as many times as necessary to execute the prepared statement.
After the SQL statement has been executed, the application must be prepared to receive the data. The first part of this takes place when the application binds the results to the local variables. However, the results aren't passed back to the application directly. The application has to tell the Driver Manager that it is ready to receive the results. The application does this by calling SQLFetch. SQLFetch only returns one row of data. Because the data is returned in columns, the application has to bind those columns with the SQLBindCol call. Essentially, you have to do the following statements, in order, to receive the resultset:
First the application calls SQLNumResultCols to find out how many columns are in each record. SQLDescribeCol tells the application what type of data is stored in each column. The application has to bind the data to variables in its address space in order to receive the data. Then the application calls SQLFetch or SQLGetData to obtain the data. The application repeats this sequence for any remaining statements.
When all the statements have been executed and the data received, the application calls SQLEndTran to commit or roll back the transaction. This takes place if the commit mode is manual (application-directed). If the commit mode is set to be automatic (which is the default), the command will be committed whenever the SQL statement is executed.
| NOTE |
Think of a transaction as a single entity that contains any number of steps. If any step or part of the transaction fails, the entire transaction fails. A transaction can be either committed or rolled back. Committed indicates that every part/step of the transaction was successful. If any part fails, then the transaction is rolled back, which indicates that the original data is preserved. Changing the commit mode to manual will assist in preserving data integrity. |
Because Day 15 presents a more
detailed example, this section shows only a portion of the program
flow. This example will fetch the last name for all the records
stored in the AddressBook database.
Listing 14.1 A Simple ODBC Example to Retrieve the
Last Name from the AddressBook Database
){kind=link}
){kind=link}
1: #include <SQL.H>
2: #include <SQLEXT.H>
3: void CAddressBookView::OnFillListBox()
4: {
5: RETCODE rcode;
6:
7: HENV henv1;
8: HDBC hdbc1;
9: HSTMT hstmt1;
10:
11: char szFirstName[50];
12: char szLastName[50];
13: char szPhoneNum[20];
14:
15: SDWORD sdODataLength;
16: unsigned char conStringOut[256];
17:
18: rcode = ::SQLAllocEnv(&henv1);
19: if (rcode == SQL_SUCCESS)
20: {
21: rcode = ::SQLAllocConnect(henv1, & hdbc1);
22: if (rcode == SQL_SUCCESS)
23: {
24: rcode = ::SQLDriverConnect(hdbc1, 0,
25: (unsigned char *)"DSN=AddressBookDb",
26: SQL_NTS, conStringOut, 256, NULL,
27: SQL_DRIVER_NOPROMPT);
28: if (rcode == SQL_SUCCESS)
29: {
30: rcode = ::SQLAllocStmt(hdbc1, &hstmt1);
31: if (rcode == SQL_SUCCESS)
32: {
33: rcode = ::SQLExecDirect(hstmt1,
34: (unsigned char *)
35: "SELECT szLastName FROM AddressTable",
36: SQL_NTS);
37:
38: for (rcode = ::SQLFetch(hstmt1);
39: rcode == SQL_SUCCESS;
40: rcode = SQLFetch(hstmt1))
41: {
42: ::SQLGetData(hstmt1, 1, SQL_C_CHAR,
43: szLastName, 50, & sdODataLength);
44: ::MessageBox(NULL, szLastName,
45: " from AddressBookDb ", MB_OK);
46: }
47: ::SQLFreeStmt(hstmt1, SQL_DROP);
48: }
49: ::SQLDisconnect(hdbc1);
50: }
51: ::SQLFreeConnect(hdbc1);
52: }
53: ::SQLFreeEnv(henv1);
54: }
55: }
Line 18 in Listing 14.1 calls SQLAllocEnv to instruct the ODBC Driver Manager to allocate variables for this application and return an environment handle. Line 21 calls SQLAllocConnect, which tells the Driver Manager to allocate variables to manage a connection and to obtain a connection handle. Line 24 calls SQLDriverConnect to make a connection to the AddressBookDb data source name.
You might have noticed that the section "Step One: Connect to a Data Source" discusses using the SQLAllocHandle call to allocate any handle, including the environment handle. SQLAllocHandle is an ODBC 3.0 call that replaces SQLAllocEnv, SQLAllocConnect, and SQLDriverConnect. This was presented in this fashion to make the point that some legacy applications might contain ODBC version 2.0 code.
Notice the #include declarations:
#include <SQL.H> #include <SQLEXT.H>
These #include files are required for any function implementing the ODBC API.
Obviously, this listing is very simplistic and is presented here to assist you in understanding programmatic flow of the ODBC API and working with databases.
As you can see from this simplistic listing, you must perform many steps just to obtain some data from a database. There is an easier way.
| NOTE |
Although the MFC class library provides class wrappers for database functions, the ODBC API function calls are still accessible from within the application. Remember to include the SQL.H and the SQLEXT.H files. |
The Microsoft Foundation Classes (MFC) are designed to make life simple for developers. They enable developers to create Windows-based applications without having to know the underlying Windows architecture. Because database applications are an important aspect of managing data, Microsoft developed the MFC wrappers for the ODBC API. These classes present an object-oriented approach to using the ODBC API.
| NOTE |
The MFC class wrappers for the ODBC API make life easier on the programmer, linking to the MFC can make the application quite large. Depending on how the MFC library is linked with the application, the MFC DLLs might need to be distributed with the application's executable and libraries. |
The CDatabase class represents a connection to the database. It contains the m_hdbc member variable, which represents the connection handle to a data source. To instantiate the CDatabase class, call the constructor and then the OpenEx or Open member function. This will initialize the environment handle and perform the connection to the data source. To close the connection, call the Close member function.
| NOTE |
The application can use the CDatabase class for more than just one database. If the application finishes using a database but needs to connect to another database, the same instance of the CDatabase class can be reused. Simply call Close to close the connection to the original data source; then call the OpenEx member function to a different data source. |
There are member functions that perform work on the connected database. After the application is connected to a database, it is ready to begin work with the CDatabase instance. To begin a transaction, the CDatabase class contains a member function called BeginTrans. After all the processing is completed, the application will call the CommitTrans to commit the transaction or Rollback to rollback the changes. Both CommitTrans and Rollback are member functions of the CDatabase class.
| NOTE |
The CDatabase class also contains a member function that can execute SQL statements that don't require a returned resultset (recordset). This function member is the ExecuteSQL function. |
There are also member functions that will return specific information about the data source. Some of these are
As you can see, the CDatabase class provides the C++ programmer with an object-oriented interface to the ODBC environment and connection API calls.
The CRecordSet class defines the data that is received from or sent to a database. The recordset could be defined as an entire table or simply one column of a table. The recordset is defined by its SQL statement.
The m_hstmt member variable of the CRecordSet contains the statement handle for the SQL handle that defines the recordset. The m_nFields member variable holds the number of fields in the recordset. The m_nParams member variable holds the number of parameters used to define the recordset. The recordset is connected to the data source through a pointer to the CDatabase object. This pointer is the CRecordSet member variable m_pDatabase.
Other member variables are defined in the CRecordSet class declaration. The m_strFilter member variable defines the WHERE clause used in the SQL statement. The m_strSort member variable is used if the SQL statement uses an ORDER BY clause.
There are many ways that recordsets can be opened or defined. CRecordSet has an Open member function that will actually perform the recordset query. The application can format a SQL SELECT statement to pass in the Open function member of the CRecordSet class.
The first parameter for the Open member function defines how the recordset will be opened. You can define and open a recordset by the following three methods:
In 1995, Microsoft introduced the DAO API. This API was developed as the API for the Microsoft Jet Database engine. The Microsoft Jet Database Engine is the database engine for Microsoft Access. The Jet Database Engine contains an ODBC interface that enables both direct and indirect ODBC access to other databases.
As opposed to being a layered API similar to ODBC, DAO was based on OLE Automation objects. Coding directly to the ODBC API was a matter of calling API functions directly within the application or using the MFC wrappers. This wasn't the case with DAO objects. If the programmer is proficient in COM, programming directly to the API can be more convenient. Not all programmers are proficient with COM, so Microsoft developed DAO wrappers within the Microsoft Foundation Classes (MFC). To ease the transition from one API to another, the MFC classes are similar to the MFC ODBC wrappers.
Not only does the DAO API provide an object-oriented method for accessing a database, but it also provides the capability for database management. DAO has the capability to modify the database schema. It does this by enabling the application to define queries and table structures within the database and then applying SQL statements against those structures. New relationships and table unions can also be defined from within the DAO API.
This section presents pertinent information for understanding the DAO objects and introduces key steps in working with these objects. After this introduction, you will explore the MFC wrapper classes for the DAO objects.
Before you jump into the DAO API object description, let's take a quick look at the underlying engine that DAO was created for. The Jet Engine was primarily developed as the engine for the Microsoft Access database. Microsoft, understanding that a proprietary database might not succeed, decided to add two types of ODBC support to the engine.
DAO's Direct ODBC is similar in nature to pass-through SQL. ODBC calls are passed through the engine and handed directly to the ODBC Driver Manager. Indirect ODBC requires that the ODBC layer of the Jet Engine process the request. Access, for example, supports SQL statements.
The Jet Engine is also designed to interface to ISAM (indexed-sequential access files) as shown in Figure 14.3. If an ISAM driver exists for the file database, it can be accessed through the ISAM layer of the Jet engine.
Figure 14.3 : The Jet Database Engine interface diagram.
The relationship between the DAO classes is shown in Figure 14.4.
Figure 14.4 : DAO class diagram.
Note that the classes shown in Figure 14.4 are not MFC classes; they are classes provided in the DAO SDK.
The CdbDBEngine class is the base class for the DAO classes. It contains and controls all other DAO classes. You must create an instance of this class before any connections to the Jet database can be performed. The DBEngine class is the class that contains the logic to connect to the Jet database engine. For an application to connect to the Jet database, it must create a DBEngine object.
Directly underneath the CdbDBEngine object is the CdbDBWorkspace object. The workspace object manages the database. Remember that DAO provides the ability to modify the actual database schema. This is done through the database object. The database that the workspace owns can contain QueryDefs and TableDef objects that can be used to modify the database structure. The workspace object also contains group and user objects that further define the database permissions structures, and allows the application to add or modify them.
The workspace might contain any number of databases that it is connected to. A typical application can access one database for employee information and another for payroll information to create a history report. This would be done by instantiating the CdbDBDatabase object for each database and assigning it to the workspace already created to manage the payroll history reporting.
CdbDBRecordsets are similar in nature to the ODBC wrapper for the ODBC recordset. For each SQL statement that is executed on any database, a recordset must exist to receive the data. Therefore, a CdbDBRecordset object will be instantiated for each query or action.
If you look at the MFC wrapper classes supplied for DAO, you will notice that they are similar in some respects to the wrappers for ODBC. This was done to aid developers in migrating ODBC applications that connected to databases designed to use the Jet engine. Because the object model for DAO was developed with some of this in mind, some correlation exists between the DAO API and its corresponding MFC wrapper classes. Because the DAO API is object-oriented, the wrapper classes are much easier to comprehend.
| CAUTION |
The DAO API provides database security through the groups object and the users object. The MFC wrapper, CDaoDatabase, doesn't grant access to these objects, so a security risk could exist. See Technote 54 in the Visual C++ documentation for details. |
The CDaoWorkspace encapsulates the CdbDBWorkspace object. However, it doesn't stop there. The application uses the workspace to manage the database itself.
The CDaoDatabase wrapper class encapsulates the CdbDBDatabase object, and all connection information is contained within it. An application will declare and instantiate the CDaoDatabase and then store this connection information within the application for all processing related to the database.
Like the ODBC MFC wrapper class, the recordset is managed and maintained by the CDaoRecordSet class. There are many similarities to the ODBC wrapper, and at first glance it would appear that applications programmatically perform the same functions.
Listing 14.2 merely shows the general sequence for developing
database applications; it doesn't really do anything.
Listing 14.2 An Example Showing the General Sequence
for Developing Database Applications
){kind=link}
){kind=link}
1: #include <stdafx.h>
2: #include <afxdao.h>
3:
4: void CAddressBookView::OnFillListBox()
5: {
6: CString lpszSQL_SELECT_ALL = "SELECT * FROM ADDRESSES";
7: CString message;
8: int nRetCode = 1;
9:
10: CString filename = "c:\\tysdbvc\\AddressBook.mdb";
11:
12: // construct new database
13: CDaoDatabase *ppDatabase = new CDaoDatabase;
14:
15: if (ppDatabase == NULL) return -1; // fatal error
16:
17: try
18: {
19: (*ppDatabase)->Open(fileName);
20: }
21: catch (CDaoException *e)
22: {
23: // create a message to display
24: message = _T("Couldn't open database-Exception: ");
25: message += e->m_pErrorInfo->m_strDescription;
26: AfxMessageBox(message);
27: nRetCode = -1;
28: }
29:
30: CDaoRecordSet *ppRecSet = new CDaoRecordSet(ppDatabase);
31:
32: try
33: {
34: ppRecSet->Open(dbOpenSnapshot,lpszSQL_SELECT_ALL,dbReadOnly);
35: }
36: catch(CDaoException *e)
37: {
38: // create a message to display
39: message = _T("Couldn't open RecordSet-Exception: ");
40: message += e->m_pErrorInfo->m_strDescription;
41: AfxMessageBox(message);
42: nRetCode = -1;
43: }
44: }
Line 19 of Listing 14.2 attempts to open the database file (in this case, an Access database file). Line 34 opens a recordset defined by
SELECT * FROM ADDRESSES
This SQL statement will retrieve all columns in the Addresses
table of the AddressBook.mdb Access database. Notice the dbReadOnly
flag passed as the last parameter. Table 14.1 shows some of the
option flags available in the DAO MFC wrapper CDaoRecordSet
wrapper class.
| dbAppendOnly | Allows additional records but doesn't permit existing records to be modified. (Dynasets only.) |
| dbDenyWrite | Prevents data from being modified while a recordset is active. (All) |
| dbDenyRead | Basically locks the tables and doesn't allow records to be read by other applications or users while the recordset is active. (Tables) |
| dbSQLPassThrough | Passes the SQL statement directly to the data source (ODBC). The DAO API won't perform any processing on the SQL statement. (Dynaset and snapshots) |
| DbForwardOnly | Allows the recordset to have forward-scrolling only. (Snapshot) |
| DbFailOnError | The workspace will roll back any changes made if an error occurs during the recordset processing. (All) |
One thing that you will notice from Listing 14.2 is that the recordset was opened, but the data wasn't mapped to any application variables. This is referred to as data binding and is covered in the next chapter.
Again, this example is extremely simplistic, but it's presented to show the object-oriented nature of the DAO MFC wrapper classes. In the next chapter you will actually build the simple Address Book application. You will build it twice, once for the ODBC wrapper classes and once for the MFC wrapper classes.
| TIP |
It is always good practice to use try/catch blocks for processing errors. |
ODBC was the first good attempt at shielding the application programmer from all the nitty-gritty of developing database applications. DAO was the follow-up API aimed at closely matching the object-oriented programming nature of C++ with the relational nature of databases.
You might run across older applications that use the ODBC or DAO APIs. It helps to gain enough understanding of the ODBC architecture and the API to be able to support legacy applications.
Today you the big picture of the two APIs. By understanding the environment of these APIs, you become more proficient at migrating to the newer OLE DB and ADO technologies.
| How do I determine which MFC wrapper classes to use? | |
| The DAO API is designed to sit on top of and interface with the Jet database engine. It is optimized for this. However, it enables the programmer to access other data sources through the ODBC layer of the engine. This pass-through method is slower than using the ODBC API directly. If the database is a Jet engine database or a local ISAM database, use DAO; otherwise, use ODBC. | |
| Why is the dbDBEngine object not directly mapped into the MFC DAO wrapper classes? | |
| The MFC DAO wrapper class for CDaoWorkspace encapsulates this functionality. The concept of the workspace is to present a transaction manager. | |
| Can I create a data source directly from my application? | |
| Yes. The ODBC API function SQLConfigDataSource will do this for you. The function takes four arguments. The first argument is the handle of the parent window. The second argument is used to designate whether you want to add a DSN (ODBC_ADD_DSN) or configure an existing one (ODBC_CONFIG_DSN). You might also remove a DSN by passing ODBC_REMOVE_DSN. The third argument names the driver, whereas the fourth argument names the data source. | |
| Does the ODBC API have any built-in exception handling? | |
| Yes. Typically, the application should perform all database processing inside try/catch blocks. Both the ODBC and the DAO will throw the CDBException error. The application must provide the error handling inside the catch block. |
The Workshop quiz questions test your understanding of today's material. (The answers appear in Appendix F, "Answers.") The exercises encourage you to apply the information you learned today to real-life situations.
© Copyright, Sams Publishing. All rights reserved.