Microsoft's Open Database Connectivity API is one of the first members of the Windows Open Services API (WOSA) to be released as a commercial product. WOSA is a suite of application programming interfaces for Windows applications that are intended to simplify and standardize the programming of a variety of classes of Windows-based procedures. Other components of WOSA include the Open Data Services (ODS) API for connecting to mainframe and minicomputer databases, the Messaging API (MAPI) that is employed by Microsoft Mail, and the Telephony API (TAPI) for call routing and voice mail. (Microsoft had a variety of other WOSA member APIs in the development stage at the time this book was written.)
This chapter explains the structure of the ODBC API and how the Access database engine uses the ODBC API, and it gives examples of using the ODBC API with ODBC drivers for desktop databases. Chapter 20, "Creating Front Ends for Client-Server Databases," explains how to use the ODBC API and ODBC drivers for RDBMSs such as Microsoft SQL Server for OS/2 and Windows NT, IBM DB2 and DB2/2, and Watcom SQL.
The ODBC API consists of Windows DLLs that include sets of functions to provide the following two fundamental database services for all database types for which ODBC drivers are available:
The ODBC API implements SQL as a call-level interface (CLI). A call-level interface employs a set of standard functions to perform specific duties, such as translating SQL queries from ANSI SQL to the dialect of SQL used by the RDBMS, representing the RDBMS's field data types by an extended set of SQL-92 field data types, and handling error conditions. The ODBC API conforms to the CLI standard (SQL CAE draft specification—1991) developed by the SQL Access Group (SAG), a consortium of client-server RDBMS software publishers and users who have a large stake in the success of client-server database technology.
Visual C++ database applications that use the ODBC API have a multitiered structure similar to the structure of the desktop databases supported by the Access database engine, shown in Figure 6.1 in the preceding chapter. The full structure of a Visual C++ database application that uses all the features of the ODBC API, including the Open Database Services (ODS) API to access mainframe and minicomputer databases, is shown in Figure 7.1.
Microsoft supplies ODBC drivers for its versions of SQL Server for OS/2 and Windows NT, and Oracle databases with Visual C++. The Microsoft Query applet included with Excel and Word for Windows, as well as the Microsoft ODBC Desktop Database Drivers kit, incorporate each of the single-tier drivers shown in Figure 7.1 below the ISAM drivers path. Independent software vendors (ISVs) such as Q+E Software (Intersolv) supply suites of ODBC drivers for a variety of desktop DBMs and client-server RDBMSs. Sources of commercial ODBC drivers and mainframe/minicomputer gateways are listed in the section "Open Database Connectivity (ODBC) Drivers" in Appendix A, "Resources for Developing Visual C++ Database Applications."
NOTE
A group of Microsoft's competitors, including Borland International, Lotus Development Corporation, and IBM, have joined to promulgate an alternative database connectivity standard called Integrated Database Application Programming Interface (IDAPI). References to the IDAPI group in the computer trade press attribute its formation to a desire by these competitors to prevent Microsoft from creating a de facto industry standard database connectivity API. Being first in the market gives Microsoft's ODBC API the momentum to qualify as today's industry "standard" for database connectivity. It's likely that any competing standard, if such a product appears, will be compatible with or directly comparable to the ODBC API.
The following sections describe the basic features of the ODBC API and show how you can use the ODBC API with desktop DBMs and unconventional datasources such as worksheets and text files.
NOTE
Most of the information in the following sections is derived from Microsoft's Programmer's Reference for the ODBC SDK, and from a white paper called "Jet Database Engine ODBC Connectivity," written by Neil Black of Microsoft's Jet Program Management and Stephen Hecht of the Jet Development group. You can download the complete text of this white paper in Word for Windows .DOC format from Section 11 (ODBC Connectivity) of the MSACCESS forum on CompuServe as RJETWP.ZIP. The paper for Access 2 is in RJETV2.ZIP. This paper can also be found on the MSDN Level I CD.
An additional document called The Jet Database Engine 2.0: A User's Overview by Paul Litwin can also be found on MSDN Level I.
ODBC drivers are classified as members of one of the two following categories:
This chapter concentrates on the use of single-tier ODBC drivers.
Three levels of conformance to the ODBC API are defined: core level, level 1, and level 2. Here are the general definitions for each of the three levels:
Core-level conformance meets the requirements of the SAG CLI specification. The majority of commercial ODBC drivers provide level 1 conformance, plus the scrollable cursor feature of level 2. Future versions of ODBC drivers probably will provide full level 2 conformance if the RDBMS that the driver is designed for supports level 2 features.
The ODBC API specifies three levels of conformance to SQL grammar: minimum, core, and extended. The SQL conformance levels define the ANSI SQL reserved words that ODBC drivers and datasources must be able to process. Table 7.1 lists the Data Manipulation Language (DML), Data Definition Language (DDL), and Data Control Language (DCL) SQL reserved words required for each level of conformance. SQL data types and expressions also are listed. Successively higher levels of grammar include the grammar of the lower levels. The core SQL grammar conforms to the requirements of the SQL Access Group CAE SQL draft specification—1991; almost all commercial ODBC drivers conform to at least the core-level SQL grammar.
| Level | DML Statements | DDL/DCL Statements | Data Types | Expressions |
| Minimum | SELECT, | CREATE | SQL_CHAR | Simple |
| INSERT, | TABLE, | arithmetic | ||
| UPDATED | DROP TABLE | |||
| SEARCHED, | ||||
| DELETE | ||||
| SEARCHED, | ||||
| COMMIT | ||||
| TRANSACTION, | ||||
| ROLLBACK | ||||
| TRANSACTION | ||||
| Core | Full SELECT, | ALTER | SQL_VARCHAR, | Subqueries |
| positioned | TABLE, | SQL_DECIMAL, | and aggregate | |
| UPDATE, and | CREATE | SQL_NUMERIC, | functions | |
| positioned | INDEX, | SQL_SMALLINT, | ||
| DELETE | DROP | SQL_INTEGER, | ||
| INDEX, | SQL_REAL, | |||
| CREATE | SQL_FLOAT, | |||
| VIEW, | SQL | |||
| DROP | DOUBLEPRECISION | |||
| VIEW, | ||||
| GRANT, | ||||
| REVOKE | ||||
| Extended | LEFT OUTER | Batch SQL | SQL_LONGVARCHAR, | SUBSTRING, ABS |
| JOIN, RIGHT | statements, | SQL_BIT, | ||
| OUTER JOIN | stored | SQL_TINYINT, | ||
| procedures | SQL_BIGINT, | |||
| SQL_BINARY, | ||||
| SQL_VARBINARY, | ||||
| SQL_LONGVARBINARY, | ||||
| SQL_DATE, | ||||
| SQL_TIME, | ||||
| SQL_TIMESTAMP |
The extended-level SQL grammar category is a catch-all for extensions to ANSI SQL that were standardized in SQL-92, such as reserved words to enforce referential integrity and nonstandard reserved words that are found in many RDBMS implementations of SQL.
NOTE
At the time this book was written, no commercial ODBC drivers fully supported the equivalent of Access SQL's PIVOT and TRANSFORM keywords used to create crosstab queries. Crosstab queries, a subject of the next chapter, are one of the most useful forms of summary queries. In most cases, the Access database engine sends GROUP BY statements to the server and then transforms the results into crosstab form. It's likely that future versions of RDBMS and the ODBC drivers that accompany them will include PIVOT and TRANSFORM (or their equivalents) as SQL reserved words.
Table 5.8 in Chapter 5, "Learning Structured Query Language," provides a list of the field data types of SQL-92 and their Access SQL equivalents (where exact equivalents are supported). The names assigned to some ODBC data types that appear in Table 7.1 differ slightly from the formal SQL-92 naming conventions. You use the ODBC data type keywords in SQL statements unless you've specified the SQL pass-through option. Access converts most unsupported numeric data types to double-precision numbers. In most cases, a double-precision number is adequate to represent accurately any numeric value commonly found in database tables.
There is no provision in Access field data types to specify the precision and scale of a numeric data type. Precision is the number of digits that compose the number (including digits in the fractional portion of the number). Scale is the number of digits following the decimal point. In xBase, you specify the precision and scale of a field by specifying the width of the field (including a position for the decimal point) followed by the number of decimal places. Access converts numeric values to either single-precision or double-precision numbers based on the values of precision and scale of the ODBC numeric data type.
CAUTION
It's an uncommon practice to use a numeric field with decimal fraction values as the primary key field of a table. Doing so can cause the Access database engine to lose the bookmark values that specify the location of a record by its primary key value. If the precision or scale of a numeric value used as a primary key field on which a unique index is created exceeds the representational capability of Access's double-precision field, the message #Deleted appears in a text box bound to the field.
Access has a repertoire of field data types that is broader than that of any desktop database presently supported by commercial ODBC drivers.
The ODBC API contains a variety of built-in functions that you can use to make the ANSI SQL code that you send to the RDBMS's ODBC driver with the SQL pass-through option independent of the RDBMS in use. To implement the ODBC scalar functions, you use the ODBC escape shorthand syntax. Escape shorthand syntax, called simply escape syntax in this book, encloses the shorthand syntax in French braces ({}). The escape syntax for all ODBC functions is
{fn ODBCFunction([Argument(s)])}
Thus, if you want to return a person's first name and last name from values in the first_name and last_name fields of a table, you use the following statement:
{fn CONCAT(first_name, CONCAT(' ', last_name))}
You need an embedded CONCAT() function, because CONCAT() supports concatenation of only two strings. (This is unlike concatenation with Visual C++'s & symbol, which lets you concatenate any number of strings.) Note that the standard literal string identifier character is the single quote (') in ANSI SQL.
The following sections compare the ODBC scalar functions for string, numeric, and date/time values to the equivalent functions of Visual C++ (where equivalents exist). This information is derived from the Programmer's Reference for the Microsoft Open Database Connectivity Software Development Kit.
NOTE
In most cases, you can use Visual C++'s built-in functions in your application code to perform the same operations that are offered by the ODBC scalar functions. However, you might need to use the ODBC scalar functions to create joins between table fields of different data types.
Table 7.2 lists the ODBC string functions and their equivalent functions, where available, in Visual C++.
| ODBC String Function | Purpose |
| ASCII(string_exp) | Returns the ASCII code value of the leftmost character of a string. |
| CHAR(integer_exp) | Returns the ASCII character whose code is integer_exp. |
| CONCAT(string1, string2) | Concatenates string1 and string2. |
| INSERT(string1, start, | Replaces the length characters of |
| length, string2) | string1 beginning at start with string2. |
| LEFT(string_exp, count) | Returns the leftmost count characters. |
| LENGTH(string_exp) | Returns an integer representing the length of the string. |
| LOCATE(string1, | Returns an integer representing the |
| string2[, start]) | position of string2 in string1. |
| LCASE(string_exp) | Returns an all-lowercase string. |
| REPEAT(string_exp, count) | Returns a string consisting of string_exp repeated count times. |
| RIGHT(string_exp, count) | Returns the rightmost count characters. |
| RTRIM(string_exp) | Removes trailing blank spaces. |
| SUBSTRING(string_exp, | Returns length characters beginning at |
| start, length) | start. |
| UCASE(string_exp) | Returns an all-uppercase string. |
Table 7.3 lists the ODBC numeric functions, except for trigonometric functions, and their equivalent functions, where available, in Visual C++.
| ODBC Numeric Function | Purpose |
| ABS(numeric_exp) | Returns the absolute value of the expression. |
| CEILING(numeric_exp) | Returns the next largest integer greater than the expression. |
| EXP(numeric_exp) | Returns the exponential value of the expression. |
| FLOOR(numeric_exp) | Returns the largest integer less than or equal to the expression. |
| LOG(float_exp) | Returns the natural (naperian) logarithm of the expression. |
| MOD(integer_exp) | Returns the remainder of integer division as an integer. |
| PI() | Returns the value of pi as a floating-point number. |
| RAND([integer_exp]) | Returns a random floating-point number with an optional seed value. |
| SIGN(numeric_exp) | Returns –1 for values less than 0, 0 for 0 values, and 1 for values greater than 0. |
| SQRT(float_exp) | Returns the square root of a floating-point value. |
Table 7.4 lists the ODBC date, time, and timestamp functions and their equivalent functions, where available, in Visual C++.
| ODBC Date/Time Function | Purpose |
| NOW() | Returns the date and time in TIMESTAMP format. |
| CURDATE() | Returns the current date. |
| CURTIME() | Returns the current time. |
| DAYOFMONTH(date_exp) | Returns the day of the month. |
| DAYOFWEEK(date_exp) | Returns the day of the week (Sunday = 1). |
| DAYOFYEAR(date_exp) | Returns the Julian date. |
| HOUR(time_exp) | Returns the hour (0 to 23). |
| MINUTE(time_exp) | Returns the minute (0 to 59). |
| MONTH(date_exp) | Returns the number of the month. |
| QUARTER(date_exp) | Returns the number of the calendar quarter. |
| SECOND(time_exp) | Returns the second (0 to 60). |
| WEEK(date_exp) | Returns the week number (1 to 52). |
| YEAR(date_exp) | Returns the four-digit year. |
The ODBC administrator applications, ODBCCP32.DLL and ODBCAD32.EXE, create or delete entries in the two ODBC initialization files in your \WINDOWS directory. The purpose of these two files and the relevance of the entries they contain are explained in the following sections.
After installing the ODBC driver manager and administrator files, the first step in providing SQL connectivity is to add ODBC drivers for the types of databases to which your applications connect. When you install an ODBC driver, the driver setup application adds an entry to the [ODBC Drivers] section of the installed drivers initialization file, ODBCINST.INI, and marks the driver as available with an Installed value. For the 32-bit drivers, the same technique is used, with the section named as [ODBC 32 bit Drivers]. It appears that 32-bit drivers use the ODBCINST.INI file for backwards compatibility.
The contents of the ODBCINST.INI file of the workstation computer used to write this book are as follows:
[ODBC Drivers] Microsoft Excel Driver (*.xls)=Installed Microsoft Text Driver (*.txt; *.csv)=Installed SQL Server=Installed Microsoft Access Driver (*.mdb)=Installed Microsoft Dbase Driver (*.dbf)=Installed Microsoft FoxPro Driver (*.dbf)=Installed Access Data (*.mdb)=Installed [ODBC Translators] MS Code Page Translator=Installed [Microsoft Access Driver (*.mdb) (32 bit)] Driver=C:\WINDOWS\SYSTEM\odbcjt32.dll Setup=C:\WINDOWS\SYSTEM\odbcjt32.dll 32Bit=1 [ODBC 32 bit Drivers] Microsoft Access Driver (*.mdb) (32 bit)=Installed Microsoft Dbase Driver (*.dbf) (32 bit)=Installed Microsoft Excel Driver (*.xls) (32 bit)=Installed Microsoft FoxPro Driver (*.dbf) (32 bit)=Installed Microsoft Paradox Driver (*.db ) (32 bit)=Installed Microsoft Text Driver (*.txt; *.csv) (32 bit)=Installed SQL Server (32 bit)=Installed Oracle (32 bit)=Installed CR SQLBase (32 bit)=Installed CR Oracle7 (32 bit)=Installed CR Sybase System 10 (32 bit)=Installed [Microsoft Dbase Driver (*.dbf) (32 bit)] Driver=C:\WINDOWS\SYSTEM\odbcjt32.dll Setup=C:\WINDOWS\SYSTEM\oddbse32.dll 32Bit=1 [Microsoft Excel Driver (*.xls) (32 bit)] Driver=C:\WINDOWS\SYSTEM\odbcjt32.dll Setup=C:\WINDOWS\SYSTEM\odexl32.dll 32Bit=1 [Microsoft FoxPro Driver (*.dbf) (32 bit)] Driver=C:\WINDOWS\SYSTEM\odbcjt32.dll Setup=C:\WINDOWS\SYSTEM\odfox32.dll 32Bit=1 [Microsoft Paradox Driver (*.db ) (32 bit)] Driver=C:\WINDOWS\SYSTEM\odbcjt32.dll Setup=C:\WINDOWS\SYSTEM\odpdx32.dll 32Bit=1 [Microsoft Text Driver (*.txt; *.csv) (32 bit)] Driver=C:\WINDOWS\SYSTEM\odbcjt32.dll Setup=C:\WINDOWS\SYSTEM\odtext32.dll 32Bit=1 [Microsoft Excel Driver (*.xls)] Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll Setup=C:\WINDOWS\SYSTEM\odexl16.dll SQLLevel=0 FileExtns=*.xls FileUsage=1 DriverODBCVer=02.01 ConnectFunctions=YYN APILevel=1 [Microsoft Text Driver (*.txt; *.csv)] Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll Setup=C:\WINDOWS\SYSTEM\odtext16.dll SQLLevel=0 FileExtns=*.,*.asc,*.csv,*.tab,*.txt FileUsage=1 DriverODBCVer=02.01 ConnectFunctions=YYN APILevel=1 [SQL Server] Driver=C:\WINDOWS\SYSTEM\sqlsrvr.dll Setup=C:\WINDOWS\SYSTEM\sqlsrvr.dll APILevel=1 ConnectFunctions=YYY DriverODBCVer=02.01 FileUsage=0 SQLLevel=1 [SQL Server (32 bit)] Driver=C:\WINDOWS\SYSTEM\sqlsrv32.dll Setup=C:\WINDOWS\SYSTEM\sqlsrv32.dll 32Bit=1 [Microsoft Access Driver (*.mdb)] Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll Setup=C:\WINDOWS\SYSTEM\odbcjt16.dll SQLLevel=0 FileExtns=*.mdb FileUsage=2 DriverODBCVer=02.01 ConnectFunctions=YYN APILevel=1 [Microsoft Dbase Driver (*.dbf)] Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll Setup=C:\WINDOWS\SYSTEM\oddbse16.dll SQLLevel=0 FileExtns=*.dbf,*.ndx,*.mdx FileUsage=1 DriverODBCVer=02.01 ConnectFunctions=YYN APILevel=1 [Microsoft FoxPro Driver (*.dbf)] Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll Setup=C:\WINDOWS\SYSTEM\odfox16.dll SQLLevel=0 FileExtns=*.dbf,*.cdx,*.idx,*.ftp FileUsage=1 DriverODBCVer=02.01 ConnectFunctions=YYN APILevel=1 [MS Code Page Translator] Translator=C:\WINDOWS\SYSTEM\mscpxlt.dll Setup=C:\WINDOWS\SYSTEM\mscpxlt.dll [Access Data (*.mdb)] Driver=C:\WINDOWS\SYSTEM\simba.dll Setup=C:\WINDOWS\SYSTEM\simadmin.dll "FileUsage"=2 "FileExtns"=*.mdb "DirectConnect"=0 [Oracle (32 bit)] Driver=C:\WINDOWS\SYSTEM\vsorac32.dll Setup=C:\WINDOWS\SYSTEM\orstub32.dll 32Bit=1 [CR SQLBase (32 bit)] Driver=C:\WINDOWS\SYSTEM\crgup07.dll Setup=C:\WINDOWS\SYSTEM\crgup07.dll 32Bit=1 [CR Oracle7 (32 bit)] Driver=C:\WINDOWS\SYSTEM\cror707.dll Setup=C:\WINDOWS\SYSTEM\cror707.dll 32Bit=1 [CR Sybase System 10 (32 bit)] Driver=C:\WINDOWS\SYSTEM\crsyb07.dll Setup=C:\WINDOWS\SYSTEM\crsyb07.dll 32Bit=1
The preceding ODBCINST.INI file includes entries for the following 16-bit datasources, which can be used with legacy 16-bit applications:
The preceding ODBCINST.INI file includes entries for the following 32-bit datasources, which can be used with newer 32-bit applications:
The datasources appear in the Add Data Source dialog box, which appears when you launch the ODBC Administrator application (either from Control Panel or by executing ODBCAD32.EXE) and then click the Drivers button in the initial Data Sources dialog box. Figure 7.2 shows the drivers listed in the [ODBC Drivers] section of the system registry.
Figure 7.2. The Add Data Source dialog box of the ODBC Administrator application.
With the Windows 3.1x 16-bit ODBC drivers, SIMBA.DLL is the master ODBC driver for all Microsoft single-tier database drivers. ("Simba" is the Swahili word for "lion.") With the exception of Excel worksheet and ASCII text files, the database file types supported by SIMBA.DLL are the same as those supported by the Access database engine: Access, dBASE, FoxPro, Paradox, and Btrieve.
Windows 95 (and Windows NT 3.5x) uses 32-bit ODBC drivers. ODBC no longer uses the original Simba driver model. Instead, all the ODBC drivers work at the same level, called directly by either ODBCJT32.DLL (for ODBC) or MSJT3032.DLL (for DAO). Examples of the use of Microsoft Query and the Access ODBC Desktop Database Drivers appear in the following sections.
Entries in the ODBC.INI initialization file designate the ODBC datasources that appear in the Data Sources dialog box of the ODBC Administrator application. As is the case for ODBCINST.INI, ODBC.INI has a two-tiered structure. A list of datasources appears in ODBC.INI's [ODBC Data Sources] section, followed by a section such as dbCMOLSC that provides additional information required by the ODBC driver for the datasource.
NOTE
Windows 95 (and Windows NT 3.5x) use the system registry rather than .INI files to store information used by applications and subsystems. Most of the ODBC information is contained in the registry keys (Windows 95) [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC] and (usually) [HKEY_USERS\.Default\Software\ODBC]. The subkeys found under these keys are arranged in a manner similar to the 16-bit .INI files.
For 32-bit ODBC drivers, this information is contained in the system registration database. The 16-bit ODBC drivers save information in the ODBC.INI file. The ODBC.INI file that is used in conjunction with the ODBCINST.INI file described in the preceding section appears as follows:
[ODBC Data Sources] MS Access 2.0 Databases=Microsoft Access Driver (*.mdb) Text CSV=Microsoft Text Driver (*.txt; *.csv) STARmanager=Microsoft dBASE Driver (*.dbf) Student Registration=Microsoft Access Driver (*.mdb) Nafta=Access Data (*.mdb) NorthWind V2=Access Data (*.mdb) [MS Access 2.0 Databases] Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll FIL=MS Access; DefaultDir=C:\ACCESS JetIniPath=MSACC20.INI UID=Admin DriverID=25 [Text CSV] Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll DefaultDir=E:\STARBETA Description=Text CSV files DriverId=27 FIL=text; JetIniPath=odbcddp.ini [STARmanager] Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll DefaultDir=E:\STARBETA Description=STARmanger DriverId=21 FIL=dBASE III; JetIniPath=odbcddp.ini [Student Registration] Driver=C:\WINDOWS\SYSTEM\odbcjt16.dll DriverId=25 JetIniPath=odbcddp.ini UID=admin Driver32=C:\WINDOWS\SYSTEM\odbcjt32.dll [ODBC 32 bit Data Sources] dBASE Files=Microsoft dBASE Driver (*.dbf) (32 bit) Excel Files=Microsoft Excel Driver (*.xls) (32 bit) FoxPro Files=Microsoft FoxPro Driver (*.dbf) (32 bit) Paradox Files=Microsoft Paradox Driver (*.db ) (32 bit) CRSS=SQL Server (32 bit) CRGUP=CR SQLBase (32 bit) CROR7=CR Oracle7 (32 bit) CRSYB=CR Sybase System 10 (32 bit) Text Files=Microsoft Text Driver (*.txt; *.csv) (32 bit) Student Registration=Microsoft Access Driver (*.mdb) (32 bit) [dBASE Files] Driver32=C:\WINDOWS\SYSTEM\odbcjt32.dll [Excel Files] Driver32=C:\WINDOWS\SYSTEM\odbcjt32.dll [FoxPro Files] Driver32=C:\WINDOWS\SYSTEM\odbcjt32.dll [Paradox Files] Driver32=C:\WINDOWS\SYSTEM\odbcjt32.dll [Nafta] Driver=C:\WINDOWS\SYSTEM\simba.dll FileType=RedISAM DataDirectory=T:\nafta.mdb SingleUser=True UseSystemDB=True SystemDB=C:\MAPNGO\system.mda [CRSS] Driver32=C:\WINDOWS\SYSTEM\sqlsrv32.dll [CRGUP] Driver32=C:\WINDOWS\SYSTEM\crgup07.dll [CROR7] Driver32=C:\WINDOWS\SYSTEM\cror707.dll [CRSYB] Driver32=C:\WINDOWS\SYSTEM\crsyb07.dll [NorthWind V2] Driver=C:\WINDOWS\SYSTEM\simba.dll Description=MS NorthWind Sample Database FileType=RedISAM DataDirectory=c:\access\sampapps\nwind.mdb SingleUser=False UseSystemDB=False [Text Files] Driver32=C:\WINDOWS\SYSTEM\odbcjt32.dll
Figure 7.3 shows the entries in the Data Sources dialog box of the ODBC Administrator that correspond to the entries in the preceding ODBC.INI file.
Figure 7.3. ODBC datasources for dBASE datasources.
Figure 7.4 shows the registration database entries for the ODBC SQL Server 32-bit driver. The information in the registry typically matches the ODBC.INI file if both the 16-bit ODBC (which uses ODBC.INI) and the 32-bit ODBC (which is in the registry) are in sync.
Figure 7.4. ODBC entries in the system registration database.
If you have RED110.DLL, the Access ODBC driver that is supplied with the Microsoft ODBC Desktop Database Drivers kit and with Microsoft Query, you can compare the performance of queries executed against STDREG32.MDB directly by the Access database engine or by the single-tier ODBC driver for Access databases by adding an ODBC datasource created from STDREG32.MDB. The RED110.DLL file is a 16-bit driver. You could also compare the system's performance to a 32-bit driver/application combination.
The following sections show you how to create an ODBC datasource named Student Registration from the STDREG32.MDB Access database, run queries against the Student Registration datasource, and trace SQL statements and ODBC function calls.
To create the Student Registration ODBC datasource, follow these steps:
Figure 7.5. Choosing the Access database class for the ODBC datasource.
Figure 7.6. The opening dialog box that is used to create a new Access ODBC datasource.
NOTE
If you don't have MS Access 7.0 Database (Microsoft Access Driver (*.mdb)) displayed in your list of datasources, click the Add button and add the driver from there.
You now have an ODBC datasource, Student Registration, that you can use with any application that supports connections to ODBC datasources, such as Visual C++, Access, and Excel with Microsoft Query.
To open the Student Registration datasource, follow these steps:
NOTE
When you're building the examples supplied with Visual C++ 4, they must be located on a drive that you have the ability to write to. If you haven't copied the example from the CD to a hard drive, you must do so before you try to build it. You can't write to the Visual C++ 4 distribution CD.
To trace the behind-the-scenes activity that occurs when you execute a query from any application that uses ODBC to access a datasource, you need to enable ODBC's data logging option. You set this option using the ODBC Administrator (found in Control Panel). You must enable ODBC event logging by clicking the Options button in the ODBC Administrator Data Sources dialog box. The ODBC Options dialog box, shown in Figure 7.8, appears.
Figure 7.8. The ODBC Administrator's ODBC Options dialog box.
In this dialog box, you can specify that all ODBC transactions be traced (that is, logged) to a file. You also can select the Stop Tracing Automatically check box to make the logging active for the current session.
WARNING
When ODBC tracing is enabled, ODBC's performance will be substantially affected. Enable tracing only when needed, and turn tracing off as soon as you're done using it.
If you don't turn logging off, the resulting tracing files ultimately grow to mammoth proportions. Therefore, you must be careful not to fill all your disk's free space with the ODBC trace file, because any application that interacts with ODBC can write thousands of records to the file.
One of the many advantages of upgrading to (or acquiring) Excel 7, Word for Windows 7, or Microsoft Office 95 is that Microsoft Query and the suite of Microsoft Desktop Database Drivers are bundled with these three mega-apps. Microsoft Query is very useful for learning how to write ANSI SQL statements and how to use the Desktop Database drivers that accompany MS Query. MS Query uses ANSI SQL, with modifications to accommodate ODBC data types, not Access SQL to execute SELECT queries. (MS Query doesn't use the Access database engine to process queries.) Thus, you can use MS Query to validate SQL SELECT query statements you write in the form of string variables in your Visual C++ database applications that use the SQL pass-through option.
Microsoft Query also is part of Visual C++ 1.5x. Since Visual C++ 2.x includes Visual C++ 1.5x, you should always be able to find a copy of Microsoft Query there.
TIP
If nothing else, using MS Query is a way to avoid typing a lengthy query string. Do this by designing your query interactively, and then use the Clipboard to cut and paste the resulting SQL string.
The following sections describe how to create an ODBC datasource, MSQueryDBF, and how to use Microsoft Query as a stand-alone application to execute queries against the datasource.
Microsoft Query comes with a sample dBASE III database whose .DBF files are located, along with the Microsoft Query executable files, in your \WINDOWS\MSAPPS\MSQUERY directory. To create a dBASE III ODBC datasource called MSQueryDBF from these files, follow these steps:
NOTE
There are two versions of MS Query: a 16-bit version in MSQUERY.EXE and a 32-bit version in MSQRY32.EXE. You should use the 32-bit version if possible because it's compatible with the 32-bit ODBC drivers used with Windows NT and Windows 95.
Figure 7.9. Microsoft Query's Select Data Source dialog box.
Figure 7.10. Microsoft Query's ODBC Data Sources dialog box.
Figure 7.11. The ODBC Administrator's Add Data Source dialog box.
Figure 7.13. The ODBC dBASE Setup dialog box for the Microsoft Query sample database.
TIP
The collating sequence shown in Figure 7.13 is ASCII, which is the normal collating sequence for text fields of dBASE and FoxPro files created by DOS applications. The sample dBASE files supplied with Microsoft Query are exported from Access's Northwind Traders sample database. These files use the ANSI (Windows) collating sequence. Choose the ANSI collating sequence for files that are created by Windows applications.
Microsoft Query maintains its own list of ODBC datasources in the MSQUERY.INI file that you'll find in your \WINDOWS directory. The typical contents of the MSQUERY.INI file are as follows:
[Table Options] Tables=1 Views=1 Synonyms=1 SysTables=0 [Microsoft Query] Tutorial=0 Maximized=0 [Data Sources] MSQueryDBF=
Because MS Query maintains its own list of datasources, you need to create an explicit ODBC datasource for MS Query using the preceding procedure. Alternatively, you can use the ODBC Administrator application to create the ODBC datasource and then add the datasource to MSQUERY.INI's [Data Sources] section with a text editor or by choosing the ODBC datasource that you created in MS Query's ODBC Data Sources dialog box.
Like the Student Registration ODBC datasource you created earlier in this chapter, you can use the MSQueryDBF datasource with any ODBC-compliant application.
Microsoft Query offers a valuable resource to Visual C++ database programmers. It allows you to manipulate ODBC datasources, create SQL statements graphically, and view the results of a query easily and dynamically. That's the good news. The bad news is that Microsoft Query doesn't always work as well as it should, and it's not a proper Windows 95 application (it doesn't allow long filenames, for example).
NOTE
Microsoft Query comes in two flavors, a 16-bit version and a 32-bit version. The 16-bit version works only with 16-bit ODBC drivers, and the 32-bit version works only with 32-bit ODBC drivers. The 32-bit version of Microsoft Query can be found on the Microsoft Office 95 distribution CD, in the directory U:\OFFICE95\OS\MSAPPS\MSQUERY, where U: is the drive containing the Microsoft Office 95 CD. The executable filename is MSQRY32.EXE. The 16-bit version of Microsoft Query isn't included with Office 95.
In setting up a dBASE data source, we'll assume that you created your Student Registration data source as outlined earlier and that you called it Student Registration. If by chance you didn't install the 32-bit version of the student registration database, you should do so before continuing with this part of the chapter.
NOTE
After you've created your joins, they will continue as part of the database until they're removed. Joins are a feature of the database, not Microsoft Query.
Figure 7.16. Student Registration's Student table with all columns displayed.
NOTE
Up to this point, using MS Query has been similar to the methods you employ in Access's query design window, except that the field names in the table windows are in alphabetical order rather than the left-to-right order of the fields in the tables. Equi-joins are indicated by lines connecting the fields with dot terminators instead of Access's single arrowhead. Access has a query criteria grid that you can display if you want to. MS Query's criteria grid is optional. The following steps demonstrate the difference between using Access's query design mode and that of MS Query.
Figure 7.17. Adding fields to the Microsoft Query grid.
NOTE
You can also drag a field from a table into the open column (the rightmost column) to add a field to the query. If the Automatic Query button (the button with the ! and the semicircular arrows) is depressed, the column will be updated with the values for the new column.
Figure 7.19. The data grid of MS Query after you apply a criterion and a sort order.
Figure 7.20. The ANSI/ODBC SQL statement for the query shown in Figure 7.19.
NOTE
Don't be surprised if your SQL window doesn't appear exactly like the one shown in Figure 7.20. Figure 7.20 is a composite image created from three bitmaps of the SQL window—one with the first part of the statement, and the others incorporating the last parts of the statement. This book has several composite images of this type.
SQL statements generated by Microsoft Query, such as the ones shown in Figure 7.20, differ somewhat from Access SQL statements that you would send to the Access database engine in Visual C++ database applications. The following characteristics of SQL statements created by MS Query distinguish the statements from Access SQL:
This chapter introduced you to version 2.5 of the Microsoft Open Database Connectivity API and provided the basic information you need to use Microsoft and third-party ODBC drivers with Visual C++ applications. I gave examples of creating ODBC datasources from Access files, from dBASE files, and from the Microsoft Query applet. I also covered the differences between Access SQL and ANSI/ODBC SQL statements and showed you when to implement the SQL pass-through option for your queries.
The next chapter deals with advanced SQL techniques that you use to transform time-series and similar data from row-column to column-row format. It also shows you how to write SQL statements in Visual C++ code to append, update, and delete records from database tables.
