Web based School
- 33 — Database Programming Through ODBC
33 — Database Programming Through ODBC
33 — Database Programming Through ODBC
ODBC, or Open Database Connectivity, represents a vendor-independent mechanism for accessing data in a variety of data sources.
ODBC drivers are available for many different types of data sources. You can use ODBC to retrieve data from text files, dBase tables, Excel spreadsheets, SQL Server databases, and many other sources.
Many ODBC drivers are redistributable. You can package your application for installation with the appropriate ODBC drivers and software for driver installation and management.
At the heart of ODBC is its capability to execute SQL (Structured Query Language) statements against data sources. In addition to reviewing ODBC in this chapter, we also take a (very) brief look at SQL itself. If you plan to perform extensive development work using ODBC, I recommend a reference manual on SQL, such as Date's The SQL Standard.
The MFC Library provides extensive support for ODBC applications. A series of classes exists encapsulating ODBC databases, tables, and records. The AppWizard supports the creation of ODBC applications, and further support for ODBC is provided by ClassWizard.
ODBC in Action
This section presents a review of some of the fundamental concepts of ODBC that we need to cover before we can begin an attempt to create an ODBC application.
The ODBC Setup Applet
Invoked through the Control Panel or as a stand-alone application, the ODBC setup applet is used to register data sources.
What exactly is a data source? That depends on the driver. In the case of a driver such as the SQL Server driver, the data source can be a database on a server. In the case of a driver such as the Microsoft Access or Microsoft Excel drivers, the database is a file (an MDB or XLS file). In the case of the Microsoft Text driver, the database is a disk directory that contains text files, which serve as tables in the database from the driver's perspective.
To add a data source, invoke the ODBC setup applet and select the Add button. In the resulting dialog (Figure 33.1), pick a driver and click OK.
Figure 33.1. ODBC Add Data Source dialog.
Next, a driver-specific dialog is displayed (Figure 33.2), where you can select the database and adjust the desired characteristics of the driver.
Figure 33.2. ODBC driver setup (Microsoft Text driver).
The ODBC setup applet's main dialog (Figure 33.3) lists all installed data sources. You can add or delete data sources, or you can modify the setup of existing data sources using this dialog.
Figure 33.3. ODBC Setup Applet.
ODBC API Concepts
Applications that use ODBC rely on ODBC drivers for data access. Drivers can be single-tier or multiple-tier. Single-tier drivers process ODBC calls and SQL statements. Multiple-tier drivers process ODBC calls and pass SQL statements to the data source (potentially a server residing elsewhere on the network).
The ODBC standard defines three conformance levels. The Core API includes those fundamental ODBC calls that are required to access a data source and execute SQL commands. The Level 1 API contains a set of additional calls used to retrieve information about data sources and the driver itself. The Level 2 API contains additional calls, such as calls that operate using parameter and result arrays. As some drivers may not support Level 2 calls (although most support Level 1), it is important to know whether a particular command is available or not; ODBC references clearly mark each command with the API level that it conforms to.
With respect to the SQL grammar, ODBC defines a core grammar and two variants: a minimum SQL grammar and an extended grammar.
Note that ODBC is not equivalent to Embedded SQL. Embedded SQL uses SQL statements in source programs written in another language. Such a hybrid program is processed by a precompiler before it is passed to the compiler of the host programming language.
In contrast, ODBC interprets SQL statements at run-time. The host program does not need to be recompiled to execute different SQL statements, nor is it necessary to compile separate versions of a host program for different data sources.
An ODBC application has to perform a series of steps to connect to a data source before it can execute SQL statements. These steps are illustrated in Figure 33.4.
Figure 33.4. Typical set of ODBC calls.
The first of the calls in Figure 33.1, SQLAllocEnv, allocates an ODBC environment. In effect, this call initializes the ODBC library and returns an environment handle of type SQLENVH.
The second call, SQLAllocConnect, allocates memory for a connection. The handle that is returned by this function, of type SQLHDBC, is used in subsequent ODBC function calls to refer to a specific connection. One application can maintain several open connections.
The third call, SQLConnect, establishes a connection by loading the driver and connecting to the data source. This call has alternatives; for example, the SQLDriverConnect call can be utilized to connect to data sources that are not set up via the ODBC setup applet.
Memory for an SQL statement is allocated through a call to SQLAllocStmt. By allocating memory for statements in a separate step, ODBC offers a mechanism whereas statements can be constructed, used, and reused before the memory allocated for them is released.
After these four calls, a typical ODBC application performs a series of calls to execute SQL statements against a database. It can use SQLPrepare to prepare (compile) an SQL statement for execution and SQLExecute to actually execute it. It can use a variety of calls to bind variables to statements and to retrieve the results of a statement.
When its work is finished, the application should free the ODBC resources it has allocated. The statement handle is freed by calling SQLFreeStmt. The connection is terminated by calling SQLDisconnect; the memory allocated for the collection is released by a call to SQLFreeConnect. Finally, the ODBC environment is released by calling SQLFreeEnv.
A Simple ODBC Example
To put this into practice, I developed a very simple ODBC application that reads rows stored in an Excel spreadsheet. When an Excel spreadsheet is accessed using the Microsoft Excel ODBC driver, worksheets play the role of database tables; and rows in a worksheet play the role of records in a table.
The spreadsheet is shown in Figure 33.5. It is a simple table of people's last names, first names, and ages.
Figure 33.5. A simple Excel spreadsheet to be accessed through ODBC.
Instead of installing this Excel spreadsheet as a data source through the ODBC setup applet, I opted to utilize the capabilities of the SQLDriverConnect function. This function enables you to connect to a data source even if it has not been previously installed through the ODBC setup applet.
At first, I was thinking of developing a command line-based example (a console application). However, this is unfortunately not possible when using the ODBC libraries. Because many ODBC calls may use the Windows interface (displaying dialogs, for instance), it is not possible to utilize them from a command-line application. An attempt to compile a console application that contains ODBC calls results in a link failure even if you specify the correct libraries on the command line.
Therefore, I decided to write a simple Windows application in which I utilize calls to MessageBox to display data retrieved from the ODBC source.
The application is shown in Listing 33.1. This program can be compiled from the command line by typing cl ages.c odbc32.lib user32.lib. Using this program requires the file ages.xls to be available in the current directory.
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>
#define CONNSTR \
"DBQ=AGES.XLS;DRIVER={Microsoft Excel Driver (*.xls)}"
#define CONNLEN (sizeof(CONNSTR)-1)
#define SQLTRY(x,y) \
{ \
rc = y; \
if (rc != SQL_SUCCESS) \
{ \
char buf[255]; \
char szState[6]; \
char szMsg[255]; \
SDWORD sdwNative; \
SWORD swMsgLen; \
SQLError(hEnv, hDBC, hStmt, szState, &sdwNative, \
szMsg, sizeof(szMsg), &swMsgLen); \
wsprintf(buf, "Error %d performing %s\nSQLState = %s" \
"\nSQL message = %s", rc, x, szState, szMsg); \
MessageBox(NULL, buf, "Error", MB_OK | MB_ICONSTOP); \
goto Terminate; \
} \
}
int WINAPI WinMain(HINSTANCE d1, HINSTANCE d2, LPSTR d3, int d4)
{
SQLHENV hEnv = 0;
SQLHDBC hDBC = 0;
SQLHSTMT hStmt = 0;
SQLCHAR szConnStr[255];
SQLCHAR szStmt[255];
SQLCHAR szFirstName[255];
SQLCHAR szLastName[255];
long nAge;
SWORD cbConnStr;
RETCODE rc;
SDWORD sdwLNLen;
SDWORD sdwFNLen;
SDWORD sdwALen;
int i;
char szResult[1000];
SQLTRY("SQLAllocEnv", SQLAllocEnv(&hEnv))
SQLTRY("SQLAllocConnect", SQLAllocConnect(hEnv, &hDBC))
SQLTRY("SQLDriverConnect", SQLDriverConnect(hDBC, NULL,
CONNSTR, CONNLEN, szConnStr,
sizeof(szConnStr), &cbConnStr,
SQL_DRIVER_NOPROMPT))
SQLTRY("SQLAllocStmt", SQLAllocStmt(hDBC, &hStmt))
wsprintf(szStmt, "SELECT * FROM [Sheet1$]");
SQLTRY("SQLPrepare", SQLPrepare(hStmt, szStmt, strlen(szStmt)))
SQLTRY("SQLBindCol", SQLBindCol(hStmt, 1, SQL_C_CHAR,
(PTR)szLastName, sizeof(szLastName), &sdwLNLen))
SQLTRY("SQLBindCol", SQLBindCol(hStmt, 2, SQL_C_CHAR,
(PTR)szFirstName, sizeof(szFirstName), &sdwFNLen))
SQLTRY("SQLBindCol", SQLBindCol(hStmt, 3, SQL_C_SLONG,
(PTR)&nAge, sizeof(nAge), &sdwALen))
SQLTRY("SQLExecute", SQLExecute(hStmt))
for (i = 1; (rc = SQLFetch(hStmt)) == SQL_SUCCESS; i++)
{
wsprintf(szResult, "Record #%d\nLast Name: %s\nFirst Name:"
" %s\nAge: %d", i, szLastName, szFirstName, nAge);
MessageBox(NULL, szResult, "Data", MB_OK);
}
if (rc != SQL_NO_DATA_FOUND)
{
SQLTRY("SQLFetch", rc)
}
MessageBox(NULL, "Successfully completed.", "Success", MB_OK);
Terminate:
if (hStmt) SQLFreeStmt(hStmt, SQL_CLOSE);
if (hDBC) SQLDisconnect(hDBC);
if (hDBC) SQLFreeConnect(hDBC);
if (hEnv) SQLFreeEnv(hEnv);
}
Because there are so many things that can go wrong during an ODBC call, I did not think that I could get away with an example that has no error checking. Fortunately, ODBC calls use a uniform error reporting mechanism; thus, it was easy to create a simple macro, SQLTRY, and use that for simple error reporting. In a more sophisticated application you may utilize, for example, exception processing for this purpose (instead of that nasty goto).
The rest is fairly simple. After the obligatory calls to SQLAllocEnv and SQLAllocConnect, the program calls SQLDriverConnect. This call enables it to open a table that has not been set up using the ODBC setup applet, and do it (optionally) without presenting a user interface. This is exactly what we are doing here; note the constants CONNSTR and CONNLEN that are used for this purpose. In CONNSTR, the driver's name must be specified exactly; otherwise, the call will fail.
Once the database (spreadsheet) has been successfully connected to, a single SQL statement is executed:
SELECT * FROM [Sheet1$]
The name Sheet1$ (enclosed in square brackets because it contains a character, '$', not recognized by SQL) is the driver-supplied name for the first spreadsheet in an Excel workguide. The SELECT SQL statement is used to retrieve a record or set of records; in its present form, it is used to simply retrieve all fields in all records.
The next three calls bind C variables to table columns. This is the purpose of the SQLBindCol function. When records are subsequently retrieved, field values are deposited into these variables.
The records themselves are retrieved by SQLFetch and displayed, in a rather pedestrian fashion, using MessageBox. SQLFetch is called repeatedly until its return value is something other than SQL_SUCCESS. A return value of SQL_NO_DATA_FOUND indicates that the last record has been retrieved; anything else is an error and treated accordingly.
The program ends with the obligatory calls to SQLFreeStmt, SQLDisconnect, SQLFreeConnect, and SQLFreeEnv to free up resources and terminate the connection to the data source.
If you run this program, it displays a series of dialogs like the one shown in Figure 33.6.
Figure 33.6. Dialog displayed by ages.exe.
Other ODBC calls
The example program in Listing 33.1 demonstrated some of the basic features of ODBC. Needless to say, there are many other ODBC function calls that application can utilize.
In addition to SQLConnect and SQLDriverConnect, the SQLBrowseConnect provides a third alternative for connecting to a data source. This function enables applications to iteratively browse data sources.
Several connection options related to transaction processing, character set translation, time-outs, tracing, and other features can be set using SQLSetConnectOption. Current settings can be retrieved through SQLGetConnectOption.
Information about drivers, data sources, and other options can be retrieved through a variety of functions, including SQLDataSources, SQLDrivers, SQLGetFunctions, SQLGetInfo, and SQLGetTypeInfo.
Statement-level options can be specified by calling SQLSetStmtOption.
As an alternative to calling SQLPrepare and SQLExecute, applications can utilize the SQLExecDirect function to execute SQL statements in a single step. The advantages of using SQLPrepare include the capability to execute a prepared statement more than once and to retrieve information about the result set prior to executing the statement.
The driver's translated version of an SQL statement can be retrieved by calling SQLNativeSql.
Some SQL statements require parameters. You can use SQLBindParameter to match variables in your program with question marks in an SQL statement. For example, you can use an SQL statement like this one:
INSERT INTO [Sheet1$] (LastName, FirstName, Age) VALUES (?, ?, ?)
Prior to executing this statement, you can use three SQLBindParameter calls to match program variables to question marks in the SQL statement. This function is used in conjunction with SQLParamData and SQLPutData, which are used in response to an SQL_NEED_DATA return value from SQLExecute.
SQLParamOptions, which is a Level 2 ODBC extension, enables an application to set multiple values. Another Level 2 extension, SQLExtendedFetch, can be used to return data on several rows in an array form.
Information about a statement's parameters can be retrieved by calling SQLDescribeParam and SQLNumParams.
Information about a statement's results can be obtained by calls to SQLNumResultCols, SQLColAttributes, and SQLDescribeCol. The SQLRowCount function returns the number of rows affected by an SQL UPDATE, INSERT, or DELETE operation; it, however, is not guaranteed to return the number of rows in a result set, and few SQL drivers support that functionality.
As an alternative to using SQLBindCol to bind columns, an application can rely on SQLGetData to retrieve data from unbound columns.
ODBC supports positioning of SQL cursors. A Level 2 extension function, SQLSetPos, can be used to position the cursor to a specific row and to update, delete, or add data to the row set.
Transaction processing is supported by the SQLTransact function.
Information about a data source can be retrieved by calling the functions SQLTables, SQLTablePrivileges, SQLColumns, SQLColumnPrivileges, SQLPrimaryKeys, SQLForeignKeys, SQLSpecialColumns, SQLStatistics, SQLProcedures, and SQLProcedureColumns. The information is returned by these functions as a result set, accessible by calling SQLBindCol and SQLFetch.
ODBC enables the asynchronous execution of functions. Asynchronous execution is enabled by calling SQLSetStmtOption or SQLSetConnectOption with SQL_ASYNC_ENABLE. When, afterwards, a function that supports asynchronous execution is called, it returns immediately with the return value SQL_STILL_EXECUTING. Repeated calls to the same function (with parameters that must be valid but are ignored, except for the first, hStmt parameter) can be used to determine whether the function's execution has completed.
Information about ODBC errors can be retrieved in a standardized form using SQLError.
The SQL Standard and ODBC
SQL, or Structured Query Language, is an official (ANSI) standard for relational database processing. In this section, I present a very brief overview of SQL, with special emphasis on the use of its statements in the context of ODBC. Hopefully, this brief summary will prove to be helpful in carrying out simple ODBC SQL operations in your applications without having to surround yourself with SQL reference works.
At the heart of SQL are data manipulation statements and schema definition statements. Data manipulation statements retrieve, add, delete, or change data in a recordset (row set). Schema definition statements define the layout of a database.
Data Manipulation Statements
There are four basic data manipulation statements: SELECT, INSERT, UPDATE, and DELETE.
SELECT operations have a general form of "SELECT-FROM-WHERE". For example, a SELECT statement may look like the following:
SELECT FirstName, LastName FROM EMPLOYEES WHERE EMPLOYEES.Age<30
Many other clauses and qualifiers can be used to refine a SELECT statement.
One of the most distinguishing features of relational databases is the ability to perform join operations. Loosely speaking, join operations means combining two or more tables into a single result set. For example, consider the following statement:
SELECT EMPLOYEES.FirstName, EMPLOYEES.LastName, PLANS.Name FROM EMPLOYEES, PLANS WHERE EMPLOYEES.Age < PLANS.MaxAge
This statement operates on two tables, EMPLOYEES and PLANS; the former represents the employees of a corporation, the latter the set of benefit packages the corporation offers. This SELECT statement retrieves, for each employee, the name of the employee and the name of all the plans the employee qualifies for by age. Note that if the employee qualifies for more than one plan, his or her name will appear more than once in the result set.
If you wish to use a SELECT statement to retrieve all the fields in a row, you can use a single asterisk as a shorthand. For example, for an EMPLOYEES table that has three fields, FirstName, LastName, and Age, the following two statements are equivalent:
SELECT FirstName, LastName, Age FROM EMPLOYEE SELECT * FROM EMPLOYEE
SQL also offers a series of aggregate functions. These functions include COUNT, MIN, MAX, AVG, and SUM. For example, to count, in the EMPLOYEES table, the number of employees whose last names are distinct, you would use the following statement:
SELECT COUNT (DISTINCT EMPLOYEES.LastName) FROM EMPLOYEES
Or, to calculate the combined life experience of the corporation's work force, you would issue the following statement:
SELECT SUM (EMPLOYEES.AGE) FROM EMPLOYEES
Obviously, many forms of the SELECT statement operate on multiple rows and return row sets as results. The SQL standard defines the concept of a cursor that is used to iteratively fetch the rows from a result set. The ODBC SQLBindCol and SQLFetch functions are based on the same principle.
The INSERT statement is used to add rows to a table. The UPDATE statement is used to modify existing rows. The DELETE statement is used to remove rows. The syntax of these commands is similar to the syntax of the SELECT command. In particular, these commands have cursor-based and noncursor variants. For example, consider the following command that you would execute on December 31 every year to update the corporate employee database (naturally, nobody ages after 30):
UPDATE EMPLOYEES SET EMPLOYEES.AGE = EMPLOYEES.AGE + 1 WHERE EMPLOYEES.AGE < 30
This searched update operates on all rows specified by the WHERE clause and does not require a cursor to execute. Other forms of these statements are cursor based; ODBC supports such operations via SQLBindParameter and related functions.
Views
A view, loosely speaking, is a kind of a "virtual" table. Not backed by physical storage, a view represents a row set created dynamically using the CREATE VIEW statement.
A view can be created with the help of a SELECT statement. For example, to create a view containing all employees younger than 30, you would use the following statement:
CREATE VIEW YOUNGEMPLOYEES (LastName, FirstName, Age) AS SELECT EMPLOYEES.LastName, EMPLOYEES.FirstName, EMPLOYEES.Age FROM EMPLOYEES WHERE EMPLOYEES.Age < 30
In subsequent operations, you can use this view just like you would use any other table. For example, you can use the following SELECT statement:
SELECT * FROM YOUNGEMPLOYEES
Data Definition Statements
Data definition statements are used to create and update tables and indexes in a database.
The CREATE TABLE statement can be used for, what else? To create a table, of course. To create the EMPLOYEES table that we used in the preceding sections, you could use the following statement:
CREATE TABLE EMPLOYEES
( LastName CHAR(30) NOT NULL,
FirstName CHAR(30),
Age INTEGER
)
The CREATE TABLE statement supports constraint clauses. These include UNIQUE clauses (specifying that a field's value must be unique) and CHECK clauses (specifying a condition). For example, our EMPLOYEES table definition may look like this:
CREATE TABLE EMPLOYEES
( LastName CHAR(30) NOT NULL,
FirstName CHAR(30) NOT NULL,
Age INTEGER,
UNIQUE (LastName, FirstName),
CHECK (Age < 30)
)
Tables can also be created with indexes. For example, to create an EMPLOYEES table indexed by last name, use the following syntax:
CREATE TABLE EMPLOYEES
( LastName CHAR(30) NOT NULL,
FirstName CHAR(30),
Age INTEGER,
PRIMARY KEY (LastName)
)
The CREATE INDEX statement can be used to create an index on an existing table.
The ALTER TABLE statement can be used to modify the structure of an existing table.
The DROP statement can be used to delete an existing table or index from the database.
Finally, the GRANT and REVOKE commands can be used to grant and revoke security privileges on specific tables.
ODBC in MFC Applications
The use of ODBC is greatly simplified by the Microsoft Foundation Classes Library. Simple applications that access tables through ODBC can be created with only a few mouse clicks using the AppWizard and ClassWizard. Several MFC classes exist that support accessing databases and recordsets.
Our discussion of ODBC-related features in the MFC Library starts with the construction of a simple example.
Setting Up a Data Source
Before an MFC ODBC application can be constructed using AppWizard, it is necessary to identify a data source on which the application will operate. The data source must be identified and set up through the ODBC setup applet.
The data source used in our example application is a text file. To access this file, we need the Microsoft Text ODBC driver. (If you did not install this driver when you set up Visual C++, rerun the Visual C++ setup program.)
The data file, ages.txt, will contain a set of records with first names, last names, and ages. The first row in the file will be used as a header row. The file will be a comma-separated file, with the following contents:
LastName,FirstName,Age Doe,John,29 Doe,Jane,26 Smith,Joe,44 Brown,Joseph,27
After creating this file, we must identify the data source through the 32-bit ODBC setup applet. Invoke this applet and click on the Add button; select the Microsoft Text Driver in the dialog shown in Figure 33.7.
Figure 33.7. Adding a text data source.
Clicking on this dialog's OK button invokes the ODBC Text Setup dialog (Figure 33.8), which is a dialog specific to the selected driver. The Microsoft Text driver views disk directories as databases and individual text files as tables in the database. The driver can be set up to use either the current directory or a specific directory as the data source.
If you select a specific directory, the driver enables, through the Options extension of its dialog, setting up individual tables (text files). For example, I specified g:\amfc as the directory where the new application will be placed and created ages.txt in that directory. After specifying this directory name by clicking on the Select Directory button, the Define Format button became active in the ODBC Text Setup dialog (Figure 33.9).
Figure 33.9. ODBC Text Setup options.
Clicking on the Define Format button brings up yet another dialog (Figure 33.10) where the format of individual tables (text files) can be specified. In the case of the ages.txt table, setting the Column Name Header check box enables the Guess button to work correctly and retrieve the names of fields and correctly guess their type.
Figure 33.10. Defining the format of a text table.
Dismiss this dialog by clicking on the OK button. When the ODBC Text Setup dialog reappears, add a name to this data source. I decided to name this data source "CSV Files in AMFC." Dismiss this dialog, too, by clicking on its OK button, and dismiss the Data Sources dialog by clicking on its Close button.
At this point, a look at the amfc directory where the ages.txt file resides reveals that the ODBC setup applet created another file, one named schema.ini. This file, shown in Listing 33.2, contains information on the characteristics of the ODBC data source that we just specified.
[ages.txt] ColNameHeader=True Format=CSVDelimited MaxScanRows=25 CharacterSet=OEM Col1=LASTNAME Char Width 255 Col2=FIRSTNAME Char Width 255 Col3=AGE Integer
Now that our data source has been set up and identified, we can turn to the AppWizard to construct a skeleton for our application.
Creating an ODBC Application Skeleton Through AppWizard
To begin creating the ODBC skeleton application, fire up AppWizard and create a project named AMFC. The project should be single-document-based (AppWizard Step 1). Database options are specified in AppWizard Step 2 (Figure 33.11), where you should specify the Database view without file support option.
Figure 33.11. Specifying database support in AppWizard.
Once you have specified this option, you must click the Data Source button and define a data source for this application before proceeding. Specify the recently created data source, CSV Files in AMFC, as the ODBC data source in this Database Options dialog (Figure 33.12).
Figure 33.12. Specifying a data source.
When you click the OK button in the Database Options dialog, AppWizard responds by showing yet another dialog (Figure 33.13) where you can select database tables. Select the ages.txt file as the database table and click the OK button.
Figure 33.13. Specifying a table.
Clicking the OK button returns you to the AppWizard main dialog and enables you to proceed from Step 2.
For our test application, we do not need to change any other options, so you might as well proceed by clicking the Finish button. The AMFC test application will be created by AppWizard at this time.
Take a look at the classes created by AppWizard (Figure 33.14). When compared with applications that have no database support, you may notice a new class and a few new member variables in the application's document and view classes.
Figure 33.14. ODBC application classes.
The new class, CAMFCSet, is a class derived from CRecordset. Looking at this class's declaration (shown in Listing 33.3), we can see that AppWizard not only created the class, it also added member variables that reflect the fields of the database table that we specified.
class CAMFCSet : public CRecordset
{
public:
CAMFCSet(CDatabase* pDatabase = NULL);
DECLARE_DYNAMIC(CAMFCSet)
// Field/Param Data
//{{AFX_FIELD(CAMFCSet, CRecordset)
CString m_LastName;
CString m_FirstName;
long m_Age;
//}}AFX_FIELD
// Overrides
// ClassWizard generated virtual function overrides
//{{AFX_VIRTUAL(CAMFCSet)
public:
virtual CString GetDefaultConnect(); // Default connection string
virtual CString GetDefaultSQL(); // default SQL for Recordset
virtual void DoFieldExchange(CFieldExchange* pFX); // RFX support
//}}AFX_VIRTUAL
// Implementation
#ifdef _DEBUG
virtual void AssertValid() const;
virtual void Dump(CDumpContext& dc) const;
#endif
};
These member variables are also reflected in the class's implementation file (Listing 33.4), in the constructor function and also in the function DoFieldExchange. The latter is called by the MFC framework to exchange data between the recordset's member variables and corresponding columns in the database table.
IMPLEMENT_DYNAMIC(CAMFCSet, CRecordset)
CAMFCSet::CAMFCSet(CDatabase* pdb)
: CRecordset(pdb)
{
//{{AFX_FIELD_INIT(CAMFCSet)
m_LastName = _T("");
m_FirstName = _T("");
m_Age = 0;
m_nFields = 3;
//}}AFX_FIELD_INIT
m_nDefaultType = snapshot;
}
CString CAMFCSet::GetDefaultConnect()
{
return _T("ODBC;DSN=CSV files in AMFC");
}
CString CAMFCSet::GetDefaultSQL()
{
return _T("[AGES].[TXT]");
}
void CAMFCSet::DoFieldExchange(CFieldExchange* pFX)
{
//{{AFX_FIELD_MAP(CAMFCSet)
pFX->SetFieldType(CFieldExchange::outputColumn);
RFX_Text(pFX, _T("[LastName]"), m_LastName);
RFX_Text(pFX, _T("[FirstName]"), m_FirstName);
RFX_Long(pFX, _T("[Age]"), m_Age);
//}}AFX_FIELD_MAP
}
Before we proceed, let me call your attention to a subtle yet deadly bug present in Visual C++ Version 4. Notice that the GetDefault member function returns the string value "[AGES].[TXT]". This, unfortunately, is wrong, and will result in an SQL syntax error if you attempt to run the application. The correct string should be "[AGES.TXT]"; you must change the implementation of this function to reflect the correct value.
The changes in the application's document and view classes are minor. The document class acquired a new member variable, m_aMFCSet, which is of type CAMFCSet and, rather unsurprisingly, represents the table that the application's document is associated with. The view class also acquired a member variable, m_pSet, which is set by default to point to the document class's m_aMFCSet member. The view class also has a new function, OnGetRecordset; the default implementation simply returns the value of m_pSet.
Although the skeleton application can be built at this time, it is not very useful in its present state. As shown in Figure 33.15, it merely displays a blank dialog; and although the record selection commands work, their only visible effect is the enabling and disabling of command items and buttons as one end or the other of the table is reached. Clearly, we must modify the application's dialog before the application is of any practical use.
Figure 33.15. The skeleton application in action.
Customizing the ODBC Application
As it turns out, customizing our ODBC application is laughably simple. In fact, the customization that enables us to browse records in our table does not require adding a single line of code by hand. All that is required is the addition of controls to the application's main dialog and the use of ClassWizard to add the appropriate member variables.
To begin, open the IDD_AMFC_FORM dialog for editing. Remove the static "TODO" control, then add three static controls and three edit controls as shown in Figure 33.16. Name the edit controls IDC_LASTNAME, IDC_FIRSTNAME, and IDC_AGE, respectively.
Figure 33.16. Adding controls to AMFC's main dialog.
Now comes the tricky part. In order to have ClassWizard assign member variables, hold down the Control key, and double-click on one of the edit fields. The result is a ClassWizard Add Member Variable dialog that is already filled with values that represent ClassWizard's guess as to the proper recordset member variable (Figure 33.17). The ClassWizard guesses the proper variable name by looking at the static fields in the dialog.
Figure 33.17. Assigning recordset member variables to dialog fields.
Repeat this action for the other two dialog fields. When done, recompile the application.
Surprise! This was all that needed to be done to turn AMFC into a functional application. In its present form (Figure 33.18), it is a functional browser of records in the ages.txt file.
Figure 33.18. The AMFC application in action.
Needless to say, as a simple browser, AMFC barely scratches the surface of the ODBC capabilities of MFC. Before we conclude this chapter, we'll take a look at what else is supported by the ODBC classes in the MFC Library.
ODBC Classes in MFC
The set of classes that the MFC Library offers in support of ODBC applications is shown in Figure 33.19. Among these classes, the two important ones are CDatabase and CRecordset.
Figure 33.19. ODBC support classes in MFC.
The CDatabase class represents a connection to a data source. Its member variable m_hdbc represents an ODBC connection handle. The member functions Open and Close can be used to establish a connection to the data source or to terminate the connection.
Other member functions are used to set or retrieve connection settings. These functions include GetConnect (returns the ODBC connection string), IsOpen, GetDatabaseName, CanUpdate, CanTransact, InWaitForDataSource, SetLoginTimeout, SetQueryTimeout, and SetSynchronousMode. By default, the CDatabase class uses asynchronous mode for accessing the data source. An asynchronous operation that is in progress can be canceled by calling the Cancel member function.
Transaction processing is supported through the member functions BeginTrans, CommitTrans, and Rollback.
The CDatabase class also offers two overridable functions. OnSetOptions is used to set standard connection options. OnWaitForDataSource is called by the framework to yield processing time while performing a lengthy operation.
The ExecuteSQL member function can be used to directly execute an SQL statement. This statement cannot be used in conjunction with SQL statements that return data records.
The CRecordset class encapsulates the functionality of an ODBC SQL statement and the row set returned by the statement. Member variables of this class identify the ODBC statement handle, the number of fields and parameters in the recordset, the CDatabase object through which this recordset is connected to the data source, and two strings that correspond to SQL WHERE and ORDER BY clauses.
The two principal types of recordsets are dynasets and snapshots. The type of a recordset is specified when calling the CRecordset::Open member function. Snapshots represent a static view of the data as it existed at the time the snapshot was created. This is most useful for tasks such as report generation. Dynasets present a dynamic view of the data, reflecting changes to it made by other users or through other recordsets in your application.
When the recordset is opened through its Open member function, the table is accessed and the query that the recordset represents is performed. The recordset and the associated statement handle can be closed by calling the Close member function.
Attributes of the recordset can be retrieved by calling the member functions CanAppend, CanRestart, CanScroll, CanTransact, CanUpdate, GetRecordCount, GetStatus, GetTableName, GetSQL, IsOpen, IsBOF, IsEOF, and IsDeleted.
The recordset can be navigated through the functions Move, MoveFirst, MoveLast, MoveNext, and MovePrev.
Operations on the recordset can be carried out by calling AddNew, Delete, Edit, or Update.
Other recordset functions carry out miscellaneous housekeeping functions.
You never use an object of type CRecordset directly. Rather, you should derive a class from CRecordset and add member variables that correspond to the fields (columns) of the table that the recordset represents. Next, override the recordset's DoFieldExchange member function; this function should facilitate the exchange of data between member variables and fields in the database through RFX_ functions. These functions, similar in syntax and concept to the dialog data exchange (DDX_) functions, are summarized in Table 33.1.
Function Name |
Field Type |
ODBC SQL Type |
|
RFX_Binary |
CByteArray |
SQL_BINARY, SQL_LONGVARBINARY, SQL_VARBINARY |
|
RFX_Bool |
BOOL |
SQL_BIT |
|
RFX_Byte |
BYTE |
SQL_TINYINT |
|
RFX_Date |
CTime |
SQL_DATE, SQL_TIME, SQL_TIMESTAMP |
|
RFX_Double |
double |
SQL_DOUBLE |
|
RFX_Int |
int |
SQL_SMALLINT |
|
RFX_Long |
LONG |
SQL_INTEGER |
|
RFX_LongBinary |
CLongBinary |
SQL_LONGVARCHAR |
|
RFX_Single |
float |
SQL_REAL |
|
RFX_Text |
CString |
SQL_CHAR, SQL_DECIMAL, SQL_LONGVARCHAR, SQL_NUMERIC, SQL_VARCHAR |
Field exchange is facilitated through the CFieldExchange class. An object of this class contains information about the field that is being exchanged when the recordset's DoFieldExchange member function is called.
The CRecordView class is a view class derived from CFormView that is designed specifically to display database records in forms. Objects of type CRecordView utilize dialog data exchange (DDX) and record field exchange (RFX) functions to facilitate the movement of data between the form and the data source. CRecordView-derived objects are used in conjunction with CRecordset-derived objects.
ODBC operations utilize the CDBException class for reporting errors via the MFC exception mechanism.
Summary
ODBC is a powerful, SQL-based, vendor-independent mechanism for accessing data in various data sources.
At the heart of ODBC are ODBC drivers, which are often redistributable DLLs that implement access to data sources of various types. Single-tier drivers implement both the connection to the data source and the processing SQL statements. Multiple-tier drivers connect to data sources and pass on the SQL statements. ODBC data sources can be local files (for example, text files, dBase files, Excel files) and remote data servers (for example, SQL Server, Oracle).
Data sources are usually specified through the ODBC setup applet (invoked through the Control Panel), although the SQLDriverConnect call makes it possible to connect to a data source that has not been set up this way.
An ODBC session involves calls that build up a connection to the data source, construct and submit SQL statements, and process the results. The ODBC API defines a series of function calls that facilitate these sessions. The API defines a set of conformance levels (Core, Level 1, and Level 2); most drivers support at least Level 1 ODBC functions.
ODBC supports a variation of the standard SQL syntax. Data manipulation statements such as SELECT, INSERT, UPDATE, and DELETE—as well as data definition statements such as CREATE TABLE, DROP TABLE, CREATE INDEX, DROP INDEX, and ALTER TABLE—are supported. ODBC also supports the CREATE VIEW SQL statement.
The Microsoft Foundation Classes Library provides two classes for ODBC support. The CDatabase class represents an ODBC connection; the CRecordset class represents an ODBC SQL statement and the row set the statement returns. Applications typically derive a class from CRecordset and add member variables corresponding to table columns. The CRecordset class offers member functions that facilitate browsing and editing the row set.
Popular Tutorials
-
MS Access
1109
-
C++
1222
-
HTML
584
-
JavaScript
616
-
Vbscript
873
-
Oracle
473
-
VC++
875
-
SQL
2959
-
XML
514
-
Java
814
-
Perl
455
-
Linux
451
