Teach Yourself Database Programming
with Visual C++ 6 in 21 days
Today's material introduces OLE DB and explains the OLE DB object hierarchy. Today's material also relates the basic concepts of ODBC to concepts of OLE DB, enabling you to build on your knowledge of database APIs and expand it to include OLE DB.
Today you will
OLE DB is designed to provide a means for accessing data regardless of the data source. As shown in Figure 16.1, OLE DB becomes the data access bridge for documents, email systems, file systems, spreadsheets, COM components, and relational databases.
Figure 16.1 : The topology of OLE DB applications.
As you can see in Figure 16.1, an application that uses OLE DB can communicate with any data source for which there is an OLE DB data provider. Because of the object-oriented (and COM) nature of OLE DB, OLE DB data providers can be written to accommodate relational as well as nonrelational data sources.
Two basic components of an OLE DB implementation are the data provider and the data consumer. An OLE DB data provider is an application that responds to queries and returns data in a usable form. An OLE DB data provider responds to various OLE DB calls to provide the information from the data source in a usable tabular form.
An OLE DB consumer is an application, or other COM component, that uses the OLE DB API to access a data source. OLE DB enables an application to access the entire range of enterprise data, regardless of where it is stored.
The key feature of OLE DB is that it simplifies the requirements for implementing a data provider. With OLE DB, the only requirement for a data provider is to return data in a tabular form; the data provider is not required to support a SQL language interface.
| NOTE |
To create an ODBC driver for a data source, it's necessary to build an SQL processing engine that can interpret and execute SQL queries. However, to create an OLE DB data provider, it's not necessary to build an SQL processing engine. |
An important component of OLE DB is the service provider. As shown in Figure 16.1, a service provider is a middleman in the OLE DB architecture. Acting as a consumer of raw OLE DB data sources and as a provider to other OLE DB consumers, a service provider can provide functionality that OLE DB data providers don't implement themselves.
For instance, OLE DB service providers alleviate the need for OLE DB data providers to implement their own SQL database engine. OLE DB offers a query-processing component as a service provider. Developers of OLE DB data providers can use the query-processing service provider if they don't want to implement a query-processing engine themselves.
Because OLE DB components provide a consistent interface, OLE DB data providers can use OLE DB service providers if they need to, in order to offer complete OLE DB functionality to applications. Also, some OLE DB service providers provide cursor engines, besides query-processing engines. Other service providers built to provide additional functionality will be available in the future.
OLE DB extends the capabilities of ODBC by enabling less sophisticated data sources to have data providers written for them. OLE DB extends ODBC, but many concepts of ODBC programming have their counterparts in OLE DB.
Open your ADOMFC1 project. Add a menu titled ODBC and add a drop-down menu item called Simple. Use ClassWizard to implement a command handler in the View class for that menu choice.
You need to include the files SQL.H and SQLEXT.H in ADOMFC1View.cpp, as shown here:.
#include <SQL.H> #include <SQLEXT.H>
You also need to use the ODBC import library, called Odbc32.lib, in your linker input (found under the Project Settings menu), shown in Figure 16.2.
Figure 16.2 : ODBC import library.
Inside your command handler, add the code in Listing 16.1.
Listing 16.1 ODBC API Programming
){kind=link}
){kind=link}
1: void CADOMFC1View::OnOdbcSimple()
2: {
3: RETCODE retcode;
4: HENV henviron;
5: HDBC hdbconn;
6: HSTMT hstmt;
7: char szCustFirstName[50];
8: SDWORD sdOutputDataLen;
9: unsigned char connStrOut[256];
10:
11: retcode = ::SQLAllocEnv(&henviron);
12: if (retcode == SQL_SUCCESS)
13: {
14: retcode = ::SQLAllocConnect(henviron, &hdbconn);
15: if (retcode == SQL_SUCCESS)
16: {
17: retcode = ::SQLDriverConnect(hdbconn, 0,
18: (unsigned char *)"DSN=OrdersDb",
19: SQL_NTS, connStrOut, 256, NULL,
20: SQL_DRIVER_NOPROMPT);
21: if (retcode == SQL_SUCCESS)
22: {
23: retcode = ::SQLAllocStmt(hdbconn, &hstmt);
24: if (retcode == SQL_SUCCESS)
25: {
26: retcode = ::SQLExecDirect(hstmt,
27: (unsigned char *)
28: "SELECT CustFirstName FROM Customers",
29: SQL_NTS);
30:
31: for (retcode = ::SQLFetch(hstmt);
32: retcode == SQL_SUCCESS;
33: retcode = SQLFetch(hstmt))
34: {
35: ::SQLGetData(hstmt, 1, SQL_C_CHAR,
36: szCustFirstName, 50,
37: &sdOutputDataLen);
38: ::MessageBox(NULL, szCustFirstName,
39: "Simple ODBC", MB_OK);
40: }
41: ::SQLFreeStmt(hstmt, SQL_DROP);
42: }
43: ::SQLDisconnect(hdbconn);
44: }
45: ::SQLFreeConnect(hdbconn);
46: }
47: ::SQLFreeEnv(henviron);
48: }
49: }
As you know, the code in Listing 16.1 is unrealistically simple. Listing 16.1 is intended merely to provide a simple ODBC program for comparison with the simple OLE DB program that you will see later in Listing 16.2.
Line 11 in Listing 16.1 calls SQLAllocEnv to instruct the ODBC driver manager to allocate variables for this application and return an environment handle. Line 14 calls SQLAllocConnect to instruct the ODBC driver manager to allocate variables to manage a connection and to obtain a connection handle. Line 17 calls SQLDriverConnect to make a connection, using the OrdersDb data source name. (SQLAllocEnv, SQLAllocConnect, and SQLDriverConnect are ODBC 2.0 functions that have been replaced in ODBC 3.0 with the SQLAllocHandle function.)
Line 23 allocates a statement handle. Line 26 calls SQLExecDirect, using that statement handle, to execute a query against the database. Lines 31-40 use SQLFetch and SQLGetData to retrieve the results of the query and display the first field in a series of message boxes. Lines 41-47 release all the resources allocated earlier in the routine.
Compile the application and run it. Yes, the message boxes do make a lovely interface.
OLE DB programming is quite different from ODBC programming. OLE DB uses COM and COM interfaces extensively.
To write a routine similar to Listing 16.1 for OLE DB is quite a bit more involved, so much more involved that all I can show you today is the OLE DB code to load the OLE DB provider and initialize it.
Add to your ADOMFC1 project a menu titled OLE DB and add a drop-down menu item called Simple. Use ClassWizard to implement a command handler in the View class for that menu choice.
You need to include the files oledb.h, oledberr.h, msdaguid.h, and msdasql.h in ADOMFC1View.cpp, as shown here:
#include <oledb.h> #include <oledberr.h> #include <msdaguid.h> #include <msdasql.h>
You also need to use the OLE DB import library, called oledb.lib, in your linker input (found under the Project Settings menu), as shown in Figure 16.3.
Figure 16.3 : OLE DB import library.
You also need to add a preprocessor definition of DBINITCONSTANTS under the C/C++ tab of the Project Settings menu, as shown in Figure 16.4.
Figure 16.4 : The DBINITCONSTANTS preprocessor definition.
Inside your OLE DB Simple command handler, add the code
in Listing 16.2.
Listing 16.2 OLE DB API programming
){kind=link}
){kind=link}
1: void CADOMFC1View::OnOledbSimple()
2: {
3: IDBInitialize* pIDBInitialize = NULL;
4:
5: // Initialize The Component Object Module Library
6: //CoInitialize(NULL);
7:
8: // Obtain Access To The OLE DB - ODBC Provider
9: CoCreateInstance(CLSID_MSDASQL, NULL, CLSCTX_INPROC_SERVER,
10: IID_IDBInitialize, (void **) &pIDBInitialize);
11: pIDBInitialize->Initialize();
12:
13: // This Is Where You Would Utilize OLE DB . . .
14:
15: // Free Up Allocated Memory
16: pIDBInitialize->Uninitialize();
17: pIDBInitialize->Release();
18:
19: // Release The Component Object Module Library
20: //CoUninitialize();
21:
22: }
As you can see, the code in Listing 16.2 is unrealistically simple. Listing 16.2 is intended merely to get you started with OLE DB programming. For the sake of code brevity, error checking is not performed.
Line 3 in Listing 16.2 declares an IDBInitialize pointer. IDBInitialize is an interface used to initialize and uninitialize data source objects and enumerators.
Lines 6 and 20 show the calls to CoInitialize and CoUninitialize. These calls are commented out because the COM libraries are already being initialized elsewhere in the code in ADOMFC1. They are shown in Listing 16.2 to illustrate the fact that the COM libraries must be initialized when you are doing OLE DB programming, but not when doing ODBC programming.
Lines 9 and 10 call CoCreateInstance to load the OLE DB provider for ODBC data sources; the provider resides in MSDASQL.DLL. The CoCreateInstance call requests a pointer to the IDBInitialize interface for the MSDASQL object and stores it in pIDBInitialize. As you know, the CoCreateInstance call will load MSDASQL.DLL into memory. Line 11 calls the IDBInitialize::Initialize function to initialize the provider.
From here you could make calls directly into the OLE DB provider. You would use QueryInterface to obtain pointers to the OLE DB interfaces exposed by the provider and would call its member functions to perform operations on the database.
ODBC programming and OLE programming are similar in that the application, in both environments, does the following:
For ODBC, to load the appropriate DLL(s), the application links with Odbc32.lib, which is the import library for the ODBC Driver Manager. The Driver Manager DLL, Odbc32.dll, loads when the application loads. The application calls ODBC API functions in the Driver Manager DLL, and the Driver Manager in turn calls functions in the appropriate ODBC driver.
For OLE DB, the application initializes the COM libraries. The application loads the proper data provider according to the CLSID parameter that it passes to the CoCreateInstance function. After that, the application can obtain pointers to the interfaces that the provider exposes and can call functions directly in the provider.
For ODBC, the application connects to the data source by calling SQLAllocEnv, SQLAllocConnect, and SQLDriverConnect (or by calling SQLAllocHandle) to allocate a connection handle. The application then builds a connection string containing the user ID, password, and the name of the data source.
For OLE DB, the application connects to the data source by building an array of property structures that contain the user ID, password, and the name of the data source. The application then calls IDBProperties::SetProperties to set initialization properties. (Listing 16.2 doesn't show this step, but tomorrow you will see the code for this.) Then the application calls IDBInitialize::Initialize to initialize the data source object.
The fundamental differences between the model for OLE DB and the model for ODBC are
As you can see, OLE DB takes an object-oriented, or COM, approach, whereas ODBC takes a traditional API-based approach to database client programming.
The OLE DB interface is composed of several major objects: Enumerator, DataSource, Session, Command, Rowset, Index, Error, and Transaction. In Figure 16.5, you can see the hierarchy of the OLE DB objects. During this week you will have a chance to look at each object in detail. A brief survey of the major OLE DB objects follows.
Figure 16.5 : The OLE DB object hierarchy.
The Enumerator object retrieves information regarding the OLE DB providers and enumerators available on this system. Much of the information about OLE DB providers is stored in the registry. An Enumerator exposes the ISourcesRowset interface and returns a Rowset describing all data sources and enumerators visible to the Enumerator.
Using the Enumerator object is better than directly accessing the registry, because in the future this information might be stored somewhere else. The Enumerator object abstracts the source of the data provider information from an application, enabling it to work even if a new enumeration method is created.
A data consumer uses a DataSource object to connect to a data provider. A data provider can be an OLE DB application, a database, or an ODBC data source using the OLE DB ODBC data source provider. When connecting to a database, a DataSource object encapsulates the environment and connection information, including a username and password. A DataSource object can be made persistent by saving its state to a file.
A Session object provides a context for transactions. Sessions create an environment to encapsulate transactions, generate rows of data from a data source, and generate commands that can query and manipulate the data source. A DataSource object creates a Session object; a DataSource object can create multiple Session objects.
A Command object processes commands. An OLE DB data provider isn't required to process commands. A Command object can create commands that can query or manipulate a data source. The result of a query creates a Rowset object. A Command object can also create multiple row sets. When accessing a data source, Command objects can create prepared statements and queries that return multiple row sets.
A Rowset object accesses information from a data source in a tabular form. A Rowset object can be created as the result of executing a command. If the data provider doesn't support commands (which it is not required to provide), a row set can be generated directly from the data provider. The capability to create row sets directly is a requirement of all data providers. A Rowset object also is used when accessing data source schema information. Depending on the functionality of the data provider, a Rowset object can also update, insert, and delete rows.
An Index object is a special case of a Rowset object. An Index object creates a row set that uses an associated index, which allows ordered access to a data source row set.
An Error object encapsulates errors that occur when accessing a data provider. An Error object can be used to obtain extended return codes and status information. OLE DB Error objects use the standard OLE Automation methodology of error handling. Although all OLE DB methods return error codes indicating the success or failure of the method call, they are not required to support the extended information provided by the Error object.
A Transaction object encapsulates transactions with a data source. A transaction buffers changes to the data source, giving the application the opportunity to commit or abort these changes. Transactions can improve application performance when accessing a data source. If the OLE DB provider supports them, distributed transactions, which enable multiple OLE DB data consumers to participate in shared transactions, are possible. An OLE DB provider is not required to support the transaction interface.
The following Microsoft Web sites can help you keep up with the latest developments of OLE DB:
The following Internet newsgroups might also be helpful:
OLE DB builds on and expands the capabilities of ODBC.
Because of the need to implement a SQL processor in an ODBC driver, writing an OLE DB provider for a data source is generally easier than writing an ODBC driver. Because OLE DB providers can be written for nonrelational data sources, OLE DB provides an interface to relational as well as nonrelational data sources.
OLE DB takes an object-oriented approach to database client development, whereas ODBC takes a function-based API approach. The OLE DB object hierarchy consists of just a few objects, which expose COM interfaces to perform well-defined sets of functions.
| I can see how OLE DB technology can help a large enterprise access all the information it stores in disparate locations, but what about a small organization? Small organizations don't have data stored all over the place, so what can OLE DB do in that environment? | |
| The data in a small organization might not be stored in many different locations, but OLE DB technology can certainly be of help to everyone. First, OLE DB provides a consistent and scalable interface to access data providers, no matter what the source. Second, OLE DB enables you to use this consistent interface to retrieve information previously inaccessible in a programmatically consistent manner. OLE DB potentially opens all information in an organization to any application. | |
| Does OLE DB support security? | |
| The security mechanisms in OLE DB are currently incomplete. OLE DB will permit authentication, authorization, and the management of security options. Authentication makes sure users are who they say they are and is generally implemented by a username and password mechanism. When it's complete, OLE DB will support domain-based and distributed authentication methodologies. Authorization methods make sure users access only what they have privileges to access. The current version of OLE DB supports local authorization methods by returning a flag if security restrictions cause a call to fail. When it's complete, OLE DB will support Distributed Component Object Model (DCOM) authorization methods. Finally, the complete OLE DB will support mechanisms to manage permissions for users and groups. | |
| Many complaints about using ODBC to access a database concern performance issues. How will the additional layer of an OLE DB ODBC provider affect performance? Are my applications going to run even more slowly? | |
| You should not notice much of a performance difference between the OLE DB ODBC provider and using the ODBC API directly. Remember that OLE DB is based on COM technology. COM is a way to provide a consistent interface so that two applications can share functionality. The OLE DB ODBC provider is simply a mechanism that remaps ODBC-specific calls into the OLE DB model; it doesn't add any overhead to this process. As the OLE DB technology matures, you are sure to see more pure OLE DB providers for data sources. Because the goal of COM and OLE DB is to create a plug-and-play architecture for application components, applications designed today using the OLE DB ODBC provider should be capable of using a pure OLE DB data provider with very few modifications. |
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.
){kind=link}