14 Database Management With Microsoft ODBC

Chapter 14

Database Management with Microsoft ODBC Objectives •

Describe the role of ODBC in the applications that interact with databases.

•

Explain ODBC architecture.

•

List the various classes involved in database applications.

•

Access data from from different different DBMSs using the MFC classes

• •

Implement an ODBC connection to a local database. Advantages Advantages of using DAO DAO classes

Introduction A database database management management system (DBMS) consists of a collection of interrelated interrelated data and set of program programs s to access that data. Database Database systems systems are designed designed to manage manage large bodies bodies of information. information. It provides mechanisms mechanisms for defining structures for the storage of information, information, efficient manipulation manipulation of data and information information retrieval. This chapter gives you a brief description of DBMS and its advantages. Visual C++ provides several ways to work with databases. Besides writing stand-alone database applications, applications, you can often use a database effectively in other kinds of programs as a convenient storage and retrieval medium. ODBC is a call-level interface that allows applications to access data in any database for which there is an ODBC driver. Using ODBC, you can create database applications with access to any database for which your end-user has an ODBC driver. ODBC provides an API that allows your application to be independent of the source database management system (DBMS). ODBC data sources are accessible through the MFC ODBC classes, as described in this chapter.

Advantages of using DBMS Use of standard file formats - Popularly the database database files are accepted accepted in the format format of .dbf or Microsoft .mdb. Indexed file access - For quick access to records by key, you need an indexed file. You can write your own B-tree file access routines, but that’s a tedious job which has been done already. All DBMS’s contain contain efficient indexed indexed access routines. Data integrity safeguards - Many DBMS’s come with security systems for protecting their data. One example is transaction processing. A transaction encompasses a series of related changes. If the entire transaction can’t be processed, it is rolled back so that the database reverts to its original state before the transaction. Multi-user access control - Your application may need multi-user access. Most DBMS’s provide record locking to prevent interference between simultaneous users.

Page 172

Database Management with Microsoft ODBC

Open Database Connectivity (ODBC) •

ODBC Architecture

•

MFC ODBC Classes

•

Handling Transactions in ODBC

When DBMS was first introduced, applications typically used embedded SQL to access a specific DBMS. The application used to have a pre compiler that converted the SQL statements into data access code for that particular DBMS. The venders of the next DBMS generation started providing DLLs for APIs that allow an application to compile SQL statements at run time. The Open Database Connectivity (ODBC) is a new approach in DBMS APIs. It is an interface that enables applications to access multiple, heterogeneous data sources. It provides common APIs that is capable of accessing all the major DBMS’s such as Oracle, SQL server, etc. The ODBC provides the following: 1) A library of ODBC function calls that allow an application to connect to a DBMS, execute SQL statements and retrieve the results. 2) A standard set of error codes. 3) The ODBC standard defines not only the rules for SQL grammar but also the C-language programming interface to a SQL database. 4) It is now possible for a single compiled C or C++ file to access the DBMS databases having the ODBC drivers. 5) The ODBC Software development kit (SDK), included with Visual C++, contains 32-bit drivers for DBF files, Microsoft Access databases etc. 6) Other database companies, including Oracle, Informix, Ingres, Gupta provide their own ODBC drivers for their DBMS. 7) You no longer have to buy the interface tool from the same company that supplies you the database engine.

The ODBC Architecture ODBC has a unique DLL - based architecture that makes the system completely modular. Any typical ODBC application has four main components in it’s architecture. Application

1) The application

Driver Manager 2) ODBC driver manager Oracle Driver

SQL Server Driver

dBase Driver

3) ODBC driver

4) Actual data source

Oracle Server (Data Source)

Data

SQL Server (Data Source)

Data

Data

SEED Infotech Ltd.


Page 173

In ODBC, the data source name defines a set of data, with the information required to access that data and the location of the data source. (You can define a data source name from the ODBC administrator in the Windows Control Panel). A data source is a specific instance of data hosted by some database management system (DBMS). The application performs the processing and calls ODBC functions to submit SQL statements and receive results. The ODBC driver manager loads and unloads the application requested drivers along with processing some of the function call. The ODBC driver processes most of the function calls, submits SQL requests to a specific data source and returns results to the application. The data source consists of data the application is accessing. This may be a DBMS or other type of data such as dBASE or FoxPro file. A small top-level DLL, ODBC32.DLL defines the API. ODBC32.DLL calls database specific DLL’s , known as drivers during the program execution. With the help of windows registry (maintained by the ODBC Administrator module in the Windows Control Panel) ODBC32.DLL tracks which database - specific DLLs are available and thus allows a single program to access data in several DBMS simultaneously.

SEED Infotech Ltd.

Page 174


ODBC32.DLL Driver Manager

MFC Database Application Program

ODBCJT32.DLL Jet Controller

MSJT3032.DLL Jet DataBase Engine MSXB3032.DLL Xbase driver

Local DBF Files

ODBCCR32.DLL cursor library

SQL Server ODBC Driver

SQL Server

Local MDB files

Remote Shared Databases

The MFC ODBC Classes MFC contains all the components you will need to write C++ database applications for Windows. MFC comes with two separate database access systems : ODBC (Open Database Connectivity) and DAO (Data Access Objects). The two principal ODBC classes are CDatabase and CRecordset. Objects of type CDatabase represent ODBC connections to data sources and objects of type CRecordset represent the scrollable rowsets (usually snapshots). Recordset is a block of data containing records (may be returned by an SQL query). The CRecordset object contains the current record in the recordset. Snapshot is a static recordset, i.e. the changes in the database are not reflected in the recordset. Dynaset is a recordset, which reflects the changes (updates, not additions or deletions), in the database. You seldom derive classes from CDatabase but you always derive classes from CRecordset to match the columns in your database tables.

CRecordset This class represents one row (record) in the recordset. This class along with the ODBC code manages the dynaset or snapshot. Recordsets enable scrolling from record to record, updating records (adding, editing, and deleting records), qualifying the selection with a filter, sorting the selection, etc. Typically, your program should derive a class from CRecordset (with the help of AppWizard or ClassWizard ) and create an instance of the class . Now it should call the inherited Open member function. Using the values of the parameters and data members CRecordset constructs and opens a CDatabase object, it issues an SQL select statement and then moves to the first record. Your program would then call other CRecordset member functions to position the cursor and exchange data between database fields and the members in your class representing the fields. When the object derived from your new class is deleted, the recordset is closed, and the connection is released. The simplest way to customize a query is to specify a WHERE clause (m_strFilter) and / or an ODER BY clause (m_strSort) before creating the recordset. To take advantage of

SEED Infotech Ltd.


Page 175

filtering and sorting the recordset, set the desired values for m_strFilter and m_strSort before calling OnInitialUpdate of CRecordView For filtering –

m_pSet ->m_strFilter = “[PubID] = “ + strUserDefinedID; For sorting –

m_pSet ->m_strSort = “Title ASC ”;

CRecordset Overridables DoFieldExchange Called to exchange data (in both directions) between the field data members of the recordset and the corresponding record on the data source. Implements record field exchange (RFX). GetDefaultConnect Called to get the default connect string. GetDefaultSQL Called to get the default SQL string to execute. OnSetOptions Called to set options for the specified ODBC statement. OnWaitForDataSource Called to yield processing time to other applications for asynchronous operations. You can join two database tables using ClassWizard. Use ClassWizard to access the class derived from CRecordset. Click the “update columns” buttons to select the table to be joined. The “Bind All” button will add data member to the new fields. Edit the GetDefaultSQL function of the recordset to access two tables. Modify the filter string m_strFilter.

CDatabase Before an application can interact with the database, it must request a connection with a data source. The CDatabase class represents a connection to a data source, through which the application operates on the data source. You can have one or more CDatabase objects active at a time in your application. To use CDatabase, construct a CDatabase object and call its Open member function. This opens a connection. When you then construct CRecordset objects for operating on the connected data source, pass the constructor of the CRecordset object a pointer to your CDatabase object. When you finish using the connection, call the Close member function and destroy the CDatabase object. Close closes any recordsets you have not closed previously.

Database Overridables OnSetOptions Called by the framework to set standard connection options. The default implementation sets the query timeout value and the processing mode (asynchronous or synchronous). You can establish these options ahead of time by calling SetQueryTimeout and SetSynchronousMode . OnWaitForDataSource Called by the framework to yield processing time to other applications during a lengthy operation.

SEED Infotech Ltd.

Page 176


CDatabase object m_hdbc

ODBC connection

CAuthorSet object (embedded in document) m_pDatabase

m_hstmnt

ODBCrowset Dynaset or snapshot Database

Fig : MFC ODBC class database relationship

CRecordView Provides a form view directly connected to a recordset object. The dialog data exchange (DDX) mechanism exchanges data between the recordset and the controls of the record view. Like all form views, a record view is based on a dialog template resource. Record views also support moving from record to record in the recordset, updating records, and closing the associated recordset when the record view closes. The application created by using AppWizard with Database view option provides toolbar buttons that enable the user to step forward and backword through a database table. The CRecordView class is a form view class that is attached to a recordset. 

Like CFormView class, the CRecordView class depends on a dialog template.



The CFormView class has data members that correspond to the controls in the Dialog, but the CRecordView class accesses objects in a foreign object, namely the attached CRecordset object.



When the user enters data in the controls, the record view’s DDX(Dialog Data Exchange) code moves the data into the recordset data members , which are bound to the database columns by the recordset’s RFX(Record Field Exchange) code.

To write a simple application using CRecordview 1) Create a new workspace with SDI option. 2) In the database support dialog box, select Database view without file option. 3) Press the Data Source button to invoke the Database options dialog box. In this select the appropriate data source. 4) Now the AppWizard will create two classes, one derived from CRecordset and one derived from CRecordView ( Which has as it’s member a pointer to the class derived from CRecordset ). It will also generate an empty dialog template. At runtime, the recordview object and the recordset objects are connected. You should add controls to the dialog template and match the controls to recordset data members. If you are accessing the FoxPro Database, you don’t have to write anything for updating the Database. But if you are accessing the Access Database, the AppWizard will create a “Read Only” view based database application. Since all the scrolling and default updating is by CRecordView::OnMove function , you should override this function , if you want to add, delete or update records from your application. Also you should add the code to add (AddNew), delete (Delete) and update (Edit) records. You should call Requery to see the changes.

SEED Infotech Ltd.


Page 177

RecordSet Database Table Record View

MyRecordSet { m_strName;

Name :

NameRoll No.

RFX

DDX m_iRoomNos }

Room :

Fig : Relationship between the ODBC database objects and data exchange machanism. E.g. Consider a database for a student record with the fields Name, Roll_No and Total_Marks. Create an application using AppWizard according to above given steps. Now to make it a Read-Write application, you will have to override the OnMove function of CRecordView os follows :

BOOL COdbcView : : OnMove(UINT nIDMoveCommand) { switch( nIDMoveCommand ) { case ID_RECORD_PREV : m_pSet->MovePrev(); if( ! m_pSet->IsBOF( )) break; case ID_RECORD_FIRST : m_pSet->MoveFirst(); break; case ID_RECORD_NEXT: m_pSet->MoveNext(); if( ! m_pSet->IsEOF( ) ) break; if( m_pSet->CanScroll ( ) ) { m_pSet->SetFieldNull ( NULL ); break; } case ID_RECORD_LAST: m_pSet->MoveLast( ) ; break; default: ASSERT( FALSE ); } UpdateData( FALSE ); return TRUE; }

Edit the menu command handlers as follows:

void COdbcView :: OnRecordAdd() { m_pSet->AddNew();

SEED Infotech Ltd.

Page 178


UpdateData(TRUE); if(m_pSet->CanUpdate()) { m_pSet->Update(); } m_pSet->ReQuery(); UpdataData(FALSE); } void COdbcView :: OnRecordClearFields() { m_pSet->Name = _T(“”); m_pSet->Total_Marks = 0; m_pSet->Roll_No = 0; UpdataData(FALSE); } void COdbcView :: OnRecordDelete() { m_pSet->Delete(); m_pSet->MoveNext(); // back to last if we moved off the end if(m_pSet->IsEOF()) { m_pSet->MoveLast(); } //Is the set now empty if(m_pSet->IsEOF()) { m_pSet->SetFieldNull(NULL); } UpdateData(FALSE); } void COdbcView :: OnRecordUpdate() { m_pSet->Edit(); UpdateData(TRUE); if(m_pSet->CanUpdate()) { m_pSet->Update(); } m_pSet->ReQuery(); }

Other MFC classes supporting ODBC CDBException - An exception resulting from failures in data access processing. This class serves the same purpose as other exception classes in the exception-handling mechanism of the class library. CFieldExchange - Supplies context information to support record field exchange (RFX), which exchanges data between the field data members and parameter data members of a recordset object and the corresponding table columns on the data source. Analogous to class CDataExchange, which is used similarly for dialog data exchange (DDX). Related Class- CLongBinary Encapsulates a handle to storage for a binary large object (or BLOB), such as a bitmap. CLongBinary objects are used to manage large data objects stored in database tables.

SEED Infotech Ltd.


Page 179

Handling Transactions in ODBC Transaction is a logical set of operations that are to be executed atomically. The database is in consistent state before and after the execution of the transaction. If the transaction fails in between, then the database is rolled back to it’s original state. For example, to perform the simple task of giving someone a raise, you might select a current salary, add 10% to it and then update it with the new account. You might also go back to their personal record and adjust their tax, withholding information. This is one transaction, even though it involves three SQL statements: one SELECT and two UPDATE. If the SELECT works and the first UPDATE changes the person’s salary, but the subsequent tax UPDATE fails , it is very convenient to be able to rollback the transaction , scratching out any changes since the transaction was strated . If everything in the transaction works correctly, the transaction can be committed In MFC, you can rollback a transaction by calling Rollback() against the CDatabase object . If you successfully complete a transaction, you can call CommitTrans() on the CDatabase object to have a transaction permanently recorded by the database. For these calls to work, you must call BeginTrans() against the database ; the first statement executed after your call to BeginTrans() is the first statement which will be undone if you call RollBack() , or actually written if you call CommitTrans().

Multiple RecordSets In the previous examples, the view object contained an embedded recordset that was created with the CRecordset default constructor, which caused the creation of a CDatabase object. The view’s OnInitialUpdate function called CRecordset::Open , which called the virtual GetDefaultConnect function, opened the database, and then called the virtual GetDefaultSQL function. The problem with this scenario is that there can be only one recordset per database because the database is embedded in the recordset. To get multiple recordsets, you have to do things a different way - you must create the CDatabase object first. Once you’ve done that, you can construct as many recordsets as you want, passing a CDatabase pointer as a parameter to the CRecordset constructor. You start by embedding a CDatabase object in the document in the place of the CRecordset object. You also include a pointer to the primary recordset.

Adding ODBC Capability to An MFC Application Add the following line at the end of StdAfx.h:

#include

Edit the RC file in text mode. After the line

“#include “”afxprint.rc”” //printing/print preview resources\r\n”

Add the line

SEED Infotech Ltd.

Page 180


“#include “”afxdb.rc “” //database resources\r\c And after the line

#include “afxprint.rc” //printing/print preview

Add the line

#include “afxdb.rc”

//database resources

DAO • • •

DAO database classes in MFC. Advantages of DAO classes. Similarities Between ODBC Database Classes and MFC DAO Database Classes.

Data Access Objects (DAO) was added to MFC in version 4. It provides a framework for using code to create and manipulate databases. DAO supplies a hierarchical set of objects that use the Microsoft Jet database engine to access data and database structure in: Database that use the Microsoft Jet (.MDB) databases engine ODBC data sources, using an ODBC driver Installable ISAM databases, such as dBASE® Paradox ™ and Microsoft FoxPro

DAO database classes in MFC MFC provides a set of classes that supports the DAO technology.

CObject

The following table lists the MFC DAO classes and the DAO objects they represent. Class

DAO object

Description

CDaoWorkspace

Workspace

Manages transactions and access to properties of the database engine. The Workspace manages one or more CDaoDatabase objects.

CDaoDatabase

Database

Manages a connection to a database

SEED Infotech Ltd.


Page 181

CDaoRecordset

Recordset

Manages a result set (recordset), a set of records returned by a query. Recordset can be tables, dynasets, or snapshots.

CDaoRecordView

(none)

Enables you to view the contents of a recordset in a CFormView – derived object

CDaoFieldExchange

(none)

Manages the between the database.

CDaoException

Error

Represents an exception condition arising from the MFC database classes based on DAO.

CDaoQueryDef

Querydef

Manages saving queries in a database for reuse.

CDaoTableDef

Tabledef

Manages manipulating or viewing the structure of a table in a database.

COleVariant

VARIANT

Used to pass data between application and the DAO object.

CByteArray

(none)

Used to transfer large binary objects (like bitmaps) between a database and the application.

exchange of data recordset and the

the

Advantages of DAO classes The DAO classes have the following advantages : 1) Better performance in some cases, particularly when using Microsoft Jet (.MDB) databases. 2) Compatibility with the ODBC classes and with Microsoft Access Basic and Microsoft Visual Basic. 3) Access to validation rules. 4) Ability to specify relations between tables. 5) A richer data access model, with support for Data Definition Language (DDL) as well as Data Manipulation Language (DML).

Similarities Between ODBC Database Classes and MFC DAO Database Classes The original design of the MFC ODBC classes was based on the DAO object model that has been in use in Microsoft Access and Microsoft Visual Basic. This means that there are many common features of the ODBC and DAO MFC classes. In general, the programming models are the same. A few similarities are: 1) Both the ODBC and DAO classes have database objects that manage using the underlying database management system (DBMS). 2) Both have recordset objects representing a set of results returned from that DBMS.

SEED Infotech Ltd.

Page 182


3) The DAO database and recordset objects have members nearly identical to the ODBC classes. 4) With both sets of classes, the code to retrieve data is identical except for some object and member name changes. Changes will be required, but usually the process is a straightforward name change when switching from the ODBC classes to DAO classes. 5) For example in both models the procedure to retrieve data is to create and open a database object, create and open a recordset object, and navigate (move) though the data performing some operation.

Note : In the DAO world, RFX is called DFX-short for DAO Record Field Exchange.

SEED Infotech Ltd.


Page 183

Summary •

•

•

•

•

•

•

ODBC is an interface that enables applications to access multiple, heterogeneous data sources. ODBC application has following components – Application code 

ODBC API



ODBC driver manager



DBMS – specific drivers

Recordset is a block of data containing records. The CRecordset object contains the current record in the recordset. Snapshot is a static recordset, i.e. the changes in the database are not reflected in the recordset. Dynaset is a recordset, which reflects the changes (updates, not additions or deletions), in the database. Query can be customized by specifying a WHERE clause (m_strFilter) and / or an ODER BY clause (m_strSort) before creating the recordset. DAO supplies a hierarchical set of objects that uses the Microsoft Jet database engine to access data and database structures.

Quiz 8) List the ODBC architectural components. 9) What is a recordset? 10) What is the difference between snapshot and dynaset? 11) Give two important data members of CRecordSet class? 12) Similarities between ODBC and DAO. 

SEED Infotech Ltd.





14 Database Management With Microsoft ODBC

Recommend Documents