Microsoft's Open Database Connectivity (ODBC) API was 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 intended to
simplify and standardize the programming of a variety of classes of Windows-based procedures. Other widely used components of WOSA include the Messaging API (MAPI) that is employed by Microsoft Mail and Microsoft Exchange, and the Telephony API (TAPI) for
modem control, call routing, and voice mail. The License Service API (LSAPI), Windows SNA API, Windows Sockets, Microsoft Remote Procedure Call (RPC), Extensions for Real-Time Market Data (WOSA/XRT), and Extensions for Financial Services (WOSA/XFS)
complete the list of WOSA components when this edition was written.
This chapter explains the structure of the ODBC API and how the Jet database engine uses the ODBC API. Also, this chapter gives examples of using Microsoft Query in conjunction with the 32-bit ODBC 2.5 API and 32-bit ODBC 2.5 drivers for desktop
databases included with the Microsoft ODBC Desktop Driver Pack 3.0 that's part of Microsoft Office 95. The next chapter, "Creating Front-Ends for Client/Server Databases," explains how to use the ODBC API and ODBC drivers for client/server RDBMSs
such as Microsoft SQL Server, Sybase System 10/11, Oracle, and Informix.
You cannot use version 2.0 or 3.0 of the ODBC desktop drivers in Visual Basic 4.0 or Access 95 applications. You receive an error message if you attempt to use in Visual Basic 4.0 or Access 95 an ODBC data source created with these drivers. (This restriction does not apply to version 1.0 of the ODBC desktop drivers, which was designed for use with Jet 1.x). Version 3.0 of the ODBC desktop drivers is intended specifically for use with Microsoft Query 2.0.
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 specification—1992) 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 Basic database applications that use the ODBC API have a multi-tiered structure, similar to the structure for the desktop databases supported by the ISAM DLLs for the Jet database engine described in Chapter 6, "Connecting to Other Desktop
Database Tables." The full structure of a Visual Basic database application that uses all the features of the ODBC API, including the Desktop ODBC Driver Pack 3.0 supplied with Microsoft Office 95, appears in Figure 19.1.
Microsoft supplies an ODBC driver for its versions of SQL Server 4.2+ for Windows NT; the Sqlsvr32.dll driver also can be used with Sybase SQL Server through version 4.9.2. (Sqlsvr32.dll also is compatible with Sybase System 10 but does not provide for
advanced features of the newer Sybase RDBMS). The Microsoft ODBC Desktop ODBC Driver Pack 3.0 incorporates the two single-tier drivers (shown in Figure 19.1), which accommodate the most common database and text file formats. Independent software vendors
(ISVs), such as 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 titled "Open Database Connectivity (ODBC)
Drivers" in Appendix A, "Resources for Visual Basic Database Front-Ends."
A cabal of Microsoft's competitors, including Borland International and Lotus/IBM, joined in 1993 to promulgate an alternative database connectivity standard called IDAPI (Integrated Database Application Programming Interface). References to the IDAPI group in the computer trade press attributed its formation to a desire by these competitors to prevent Microsoft from creating a de facto industry standard database connectivity API. Only Borland was actively supporting IDAPI when this edition was written. Being first in the market gave Microsoft's ODBC API the momentum to qualify as today's industry "standard" for database connectivity. Virtually all suppliers or client/server RDBMSs support the ODBC API.
The following sections describe the basic features of the ODBC API and how you can use the ODBC API with desktop database managers and unconventional data sources, such as worksheets and text files.
Most of the information in the following sections is derived from Microsoft's Programmer's Reference for the ODBC SDK version 2.0, the ODBC SDK version 2.10, and from version 2 of the "Jet Database Engine ODBC Connectivity White Paper," 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 the White Paper in Word .doc format from Section 10 (ODBC/Client/Server) of the MSACCESS Forum on CompuServe as RJETV2.ZIP. This White Paper is based on Jet 2.0. An earlier version of the White Paper, RJETWP.HTM (pertaining to Access 1.1), is included on the MSDN CD-ROMs and is available from
http://www.microsoft.com/devonly/strategy/odbc/rjetwp.htm
ODBC drivers are classified as members of one of the two following categories:
This chapter concentrates on the use of single-tier ODBC drivers, leaving the subject of multiple-tier drivers, ODS, and gateway products to the next chapter.
Three levels of conformance to the ODBC API are defined: core level, level 1, and level 2. The general definitions of each of the three levels are as follows:
Core level conformance meets the requirements of the SAG CLI specification. The majority of early commercial ODBC drivers provide level 1 conformance, plus the scrollable cursor feature of level 2. Today, most ODBC drivers provide full level 2
conformance if the RDBMS for which the driver is designed supports level 2 features. Microsoft SQL Server 6.0 and the 32-bit SQL Server driver version 2.5 included with Visual Basic 4.0 provides full level 2 conformance.
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 data sources must be able to process. Table 19.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 at least to the core-level SQL grammar.
| Level | DML Statements | DDL/DCL Statements | Data Types | Expressions |
| Minimum | SELECT, | CREATE | CHAR | Simple |
| INSERT, | TABLE, | arithmetic | ||
| UPDATED | DROP | |||
| SEARCHED, | TABLE | |||
| DELETE | ||||
| SEARCHED, | ||||
| COMMIT | ||||
| TRANSACTION, | ||||
| ROLLBACK | ||||
| TRANSACTION | ||||
| Core | Full SELECT, | ALTER | VARCHAR, | Subqueries |
| positioned | TABLE, | DECIMAL, | and aggregate | |
| UPDATE, and | CREATE | NUMERIC, | functions | |
| positioned | INDEX, | SMALLINT, | ||
| DELETE | DROP | INTEGER, | ||
| INDEX, | REAL, | |||
| CREATE | FLOAT, | |||
| VIEW, | DOUBLE | |||
| DROP | PRECISION | |||
| VIEW, | ||||
| GRANT, | ||||
| REVOKE | ||||
| Extended | LEFT OUTER | Batch SQL | LONG | SUB- |
| JOIN, RIGHT | statements, | VARCHAR, | STRING, | |
| OUTER JOIN | stored | BIT, | ABS | |
| procedures | TINYINT, | |||
| BIGINT, | ||||
| BINARY, | ||||
| VARBINARY, | ||||
| LONG | ||||
| VARBINARY, | ||||
| DATE, TIME, | ||||
| 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.
At the time this book was written, there were no commercial ODBC drivers that fully supported the equivalent of Access SQL's PIVOT and TRANSFORM keywords used to create crosstab queries. The crosstab query, a subject of the next chapter, is 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 is likely that future versions of RDBMS and the ODBC drivers that accompany them will include PIVOT and TRANSFORM (or their equivalents) as database-specific SQL reserved words.
[VB4_NEW]The ODBC API has gone through several iterations since the release of 16-bit ODBC 1.0 for Windows 3.x. ODBC 2.0 is a 32-bit version of ODBC designed for use with Windows NT and with Windows 3.1 having the Win32s API DLLs installed. ODBC
2.0 is capable of invoking universal thunking (UT) layers to permit 16-bit ODBC drivers to be used with a 32-bit driver manager, Odbc32.dll. The following entries in a 16-bit ODBC.INI specify the thunking layers:
[Win32s ODBC Driver Manager] Driver=odbc32.dll,... Thunk=odbc16ut.dll,... [Win32s ODBC] Main=odbccp32.dll,... Thunk=cpn16ut.dll,...
For use under Windows NT, 32-bit generic thunking (GT) layers are required for 16-bit Windows on Windows (WOW) applications to use 32-bit ODBC drivers. The following entries appear in the 32-bit Odbc.ini file for Windows NT:
[Generic Thunk ODBC Driver Manager] Thunk1=odbc32gt.dll,... Thunk2=odbc16gt.dll,... [Generic Thunk ODBC] Thunk1=ds32gt.dll,... Thunk2=ds16gt.dll,...
ODBC 2.10, the latest version of ODBC for which an SDK was available when this edition was written, was designed to support Windows 95 and Windows NT 3.5, not Win32s. (Microsoft recommends using ODBC 2.0 for Win32s applications.) Version 2.10 includes
both 16-bit and 32-bit implementations, supports MIPS and DEC Alpha RISC systems, and fixes a number of installation problems associated with version 2.0. The 16-bit implementation of ODBC included with Visual Basic 4.0 is version 2.10.
ODBC 2.5 is provided with Visual Basic 4.0, Access 95, and Microsoft Office 95. Version 2.5 only supports 32-bit ODBC drivers under Windows 95, which is a problem for users of RDBMSs whose suppliers have not released 32-bit drivers. When this chapter
was written, the Microsoft ODBC SDK for version 2.5 was not a released product.
You can determine the version of the ODBC DLLs by opening the files in a DOS or Windows hex viewer/editor and moving to the end of the file to display the "ProductVersion" entry. With 32-bit DLLs that use Unicode, page up until you find "P r o d u c t V e r s i o n". As an example, the version number of Visual Basic 4.0's 16-bit Odbc.ini is 2.10.2401 (2401 is the build number).
Table 5.7 of 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 19.1 differ slightly from the formal SQL-92 naming conventions. You use the ODBC data type keywords in SQL statements unless you have specified the SQL passthrough option. Jet 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 Jet 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.
It is 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 Jet 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 Jet's double-precision field, the message #Deleted appears in a text box bound to the field.
Jet has a repertoire of field data types that is broader than that of any of the desktop databases presently supported by commercial ODBC drivers. Field data type conversion from Jet databases to client/server RDBMS field data types is one of the
subjects covered in the next chapter.
The ODBC API contains a variety of built-in functions you can use to make the ANSI SQL code you send to the RDBMS's ODBC driver with the SQL passthrough 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 within French braces ({}). The escape syntax for all ODBC functions is
{fn ODBCFunction([Argument(s)])}
Thus, if you want to return the first name and last name of a person 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() only supports concatenation of two strings. (This is unlike concatenation with Visual Basic's & symbol, which enables you to concatenate numeric data types with strings.) Note that the
standard literal string identifier character is the single quote (', an apostrophe) in ANSI SQL.
The sections that follow provide lists that compare the ODBC scalar functions for string, numeric, and date/time values with the equivalent functions of Visual Basic 4.0 (where equivalents exist). This information is derived from the Programmer's
Reference for the Microsoft Open Database Connectivity Software Development Kit version 2.0 and the ODBC SDK version 2.10, which is included on the MSDN CD-ROMs.
In most cases, you can use the built-in functions of Visual Basic 4.0 in your application code to perform the same operations offered by the ODBC scalar functions. However, you may need to use the ODBC scalar functions to create joins between table fields of different data types.
Table 19.2 lists the ODBC string functions and their equivalent functions, where available, in Visual Basic 4.0. When you use the Jet database engine, Jet converts Visual Basic functions that appear in your Jet SQL statements to their ANSI/ODBC SQL
equivalents.
| ODBC String Function | Visual Basic 4.0 | Purpose |
| ASCII(string_exp) | Asc(string_exp) | Returns the ASCII code value of the leftmost character of a string |
| CHAR(integer_exp) | Chr$(integer_exp) | Returns the ASCII character whose code is integer_exp |
| CONCAT(string1, string2) | & | Concatenates string1 and string2 |
| INSERT(string1, start length, string2) | None | Replaces the lengthcharacters of string1 beginning at string2 |
| LEFT(string_exp, count) | Left$(string_exp, count) | Returns the leftmost count characters |
| LENGTH(string_exp) | Len(string_exp) | Returns an integer representing the length of the string |
| LOCATE(string1,string2[, start]) | InStr(string1, string2[, start]) | Returns an integer representing the position of string2 in string1 |
| LCASE(string_exp) | LCase$(string_exp) | Returns an all-lowercase string |
| REPEAT(string_exp, count) | String$(string_exp, count) | of string_exp repeated count times |
| RIGHT(string_exp, count) | Right$(string_exp, count) | Returns the rightmost count characters |
| RTRIM(string_exp) | RTrim$(string_exp) | Removes trailing blanks |
| SUBSTRING(string_exp, start, length) | Mid$(string_exp, start, length) | Returns length characters beginning at start |
| UCASE(string_exp) | UCase$(string_exp) | Returns an all-uppercase string |
Table 19.3 lists the ODBC string functions (except for trigonometric functions) and their equivalent functions, where available, in Visual Basic 4.0.
| ODBC Numeric Function | Visual Basic 4.0 | Purpose |
| ABS(numeric_exp) | Abs(numeric_exp) | Returns the absolute value of the expression |
| CEILING(numeric_exp) | Int(numeric_exp) | Returns the smallest integer greater than the expression |
| EXP(numeric_exp) | Exp(numeric_exp) | Returns the exponential value of the expression |
| FLOOR(numeric_exp) | Fix(numeric_exp) | Returns the largest integer less than or equal to the expression |
| LOG(float_exp) | Log(float_exp) | Returns the natural (Naperian) logarithm of the expression |
| MOD(integer_exp) | Mod(integer_exp) | Returns the remainder of integer division as an integer |
| PI() | None |
Returns the value of  as a floating-point number
|
| RAND([integer_exp]) | Rnd([integer_exp]) | Returns a random floating-point number with an optional seed value |
| SIGN(numeric_exp) | Sgn(numeric_exp) | Returns -1 for values less than 0, 0 for 0 values, and 1 for values greater than 0 |
| SQRT(float_exp) | Sqr(float_exp) | Returns the square root of a floating point value |
Table 19.4 lists the ODBC date, time, and timestamp functions and their equivalent functions, where available, in Visual Basic 4.0.
| ODBC Date/Time Function | Visual Basic 3.0 | Purpose |
| NOW() | Now | Returns the date and time in TIMESTAMP format |
| CURDATE() | Date$ | Returns the current date |
| CURTIME() | Time$ | Returns the current time |
| DAYOFMONTH(date_exp) | DatePart ("d", date_exp) | Returns the day of the month |
| DAYOFWEEK(date_exp) | DatePart("w", date_exp) | Returns the day of the week (Sunday = 1) |
| DAYOFYEAR(date_exp) | DatePart("y", date_exp) | Returns the Julian date |
| HOUR(time_exp) | None | Returns the hour (0 to 23) |
| MINUTE(time_exp) | None | Returns the minute (0 to 59) |
| MONTH(date_exp) | DatePart("m", date_exp) | Returns the number of the month |
| QUARTER(date_exp) | DatePart("q", date_exp) | Returns the number of the calendar quarter |
| SECOND(time_exp) | None | Returns the second (0 to 60) |
| WEEK(date_exp) | DatePart("ww", date_exp) | Returns the week number (1 to 52—used primarily by European firms) |
| YEAR(date_exp) | DatePart("yyyy", date_exp) | Returns the four-digit year |
The 16-bit ODBC administrator applications, ODBCINST.DLL and ODBCADM.EXE, and 32-bit Odbcad32.exe create or delete entries in the two ODBC initialization files in your \WINDOWS or \Win95 folder and the Windows 95 or Windows NT Registry. The purpose of
these two initialization files, the Registry entries, and the relevance of the entries they contain are explained in the sections that follow.
[VB4_NEW]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 a 16-bit ODBC driver, the
driver setup application adds an entry to the [ODBC Drivers] section of the installed drivers initialization file—ODBCINST.INI of Windows 3.x or Odbcinst.ini of Windows 95 and Windows NT 3.51+—and marks the driver as available with an
Installed value. Entries for 32-bit ODBC drivers appear in the [ODBC 32 bit Drivers] section. The content of the \Win95\Odbcinst.ini file of the workstation computer used to write this edition appears as follows:
[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 Text Driver (*.txt; *.csv) (32 bit)=Installed Microsoft FoxPro Driver (*.dbf) (32 bit)=Installed Microsoft Paradox Driver (*.db ) (32 bit)=Installed MS Code Page Translator (32 bit)=Installed SQL Server (32 bit)=Installed [Microsoft Access Driver (*.mdb) (32 bit)] Driver=D:\WIN95\SYSTEM\odbcjt32.dll Setup=D:\WIN95\SYSTEM\odbcjt32.dll 32Bit=1 [Microsoft dBase Driver (*.dbf) (32 bit)] Driver=D:\WIN95\SYSTEM\odbcjt32.dll Setup=D:\WIN95\SYSTEM\oddbse32.dll 32Bit=1 [Microsoft Excel Driver (*.xls) (32 bit)] Driver=D:\WIN95\SYSTEM\odbcjt32.dll Setup=D:\WIN95\SYSTEM\odexl32.dll 32Bit=1 [Microsoft Text Driver (*.txt; *.csv) (32 bit)] Driver=D:\WIN95\SYSTEM\odbcjt32.dll Setup=D:\WIN95\SYSTEM\odtext32.dll 32Bit=1 [Microsoft FoxPro Driver (*.dbf) (32 bit)] Driver=D:\WIN95\SYSTEM\odbcjt32.dll Setup=D:\WIN95\SYSTEM\odfox32.dll 32Bit=1 [Microsoft Paradox Driver (*.db ) (32 bit)] Driver=D:\WIN95\SYSTEM\odbcjt32.dll Setup=D:\WIN95\SYSTEM\odpdx32.dll 32Bit=1 [SQL Server (32 bit)] Driver=D:\WIN95\SYSTEM\sqlsrv32.dll Setup=D:\WIN95\SYSTEM\sqlsrv32.dll 32Bit=1 [ODBC Translators] MS Code Page Translator=Installed [MS Code Page Translator (32 bit)] Driver=D:\WIN95\SYSTEM\sqlsrv32.dll Setup=D:\WIN95\SYSTEM\MSCPXL32.DLL 32Bit=1 [ODBC Drivers] SQL Server=Installed Access 2.0 for MS Office (*.mdb)=Installed [Access 2.0 for MS Office (*.mdb)] Driver=D:\WIN95\SYSTEM\ODBCJT16.DLL Setup=D:\WIN95\SYSTEM\ODBCJT16.DLL [SQL Server] Driver=D:\WIN95\SYSTEM\sqlsrvr.dll Setup=D:\WIN95\SYSTEM\sqlsrvr.dll [MS Code Page Translator] Translator=D:\WIN95\SYSTEM\mscpxlt.dll Setup=D:\WIN95\SYSTEM\mscpxlt.dll
The preceding Odbcinst.ini file includes entries for the following 32-bit and 16-bit ODBC drivers:
Only the 32-bit data sources appear in the Drivers dialog when you launch the 32-bit ODBC Administrator application from Control Panel and then click the Drivers button of the initial ODBC Data Sources dialog. The top dialog of Figure 19.2 shows the
32-bit ODBC Administrator displaying the drivers listed in the [ODBC 32 bit Drivers] section of the preceding Odbdinst.ini file. The bottom dialog of Figure 19.2 shows the drivers listed in the [ODBC Drivers] section.
Figure 19.2. The Drivers dialog of the 32-bit (top) and 16-bit (bottom) ODBC Administrator applications.
[VB4_NEW]Entries in the Odbc.ini initialization file designate the ODBC data sources that appear in the Data Sources dialog of the ODBC Administrator application. As is the case for Odbcinst.ini, Odbc.ini has a two-tiered structure. A list of data
sources appears in the Odbc.ini [ODBC Data Sources] section, followed by a section (dbCMOLSC, for example) that provides additional information required by the ODBC driver for the data source.
The content of the Odbc.ini file that is used in conjunction with the Odbcinst.ini file (described in the preceding section) appears as follows:
[ODBC 32 bit Data Sources] MS Access 7.0 Database=Microsoft Access Driver (*.mdb) (32 bit) 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) Text Files=Microsoft Text Driver (*.txt; *.csv) (32 bit) Northwind=SQL Server (32 bit) DataExchange=SQL Server (32 bit) SCMVSPRV=SQL Server (32 bit) NWIND=SQL Server (32 bit) pubs=SQL Server (32 bit) [MS Access 7.0 Database] Driver32=D:\WIN95\SYSTEM\odbcjt32.dll [dBASE Files] Driver32=D:\WIN95\SYSTEM\odbcjt32.dll [Excel Files] Driver32=D:\WIN95\SYSTEM\odbcjt32.dll [FoxPro Files] Driver32=D:\WIN95\SYSTEM\odbcjt32.dll [Paradox Files] Driver32=D:\WIN95\SYSTEM\odbcjt32.dll [Text Files] Driver32=D:\WIN95\SYSTEM\odbcjt32.dll [ODBC Data Sources] MS Access 2.0 Databases=Access 2.0 for MS Office (*.mdb) Northwind=SQL Server [MS Access 2.0 Databases] Driver=D:\WIN95\SYSTEM\ODBCJT16.DLL DefaultDir=E:\ACCESS JetIniPath=MSACC20.INI UID=Admin [DataExchange] Driver32=D:\WIN95\SYSTEM\sqlsrv32.dll [SCMVSPRV] Driver32=D:\WIN95\SYSTEM\sqlsrv32.dll [pubs] Driver32=D:\WIN95\SYSTEM\sqlsrv32.dll [Northwind] Driver=D:\WIN95\SYSTEM\SQLSRVR.DLL Description=Northwind Sample Database Server=OAKLEAF0 Database=nwind OemToAnsi=No LastUser=sa Language= Driver32=D:\WIN95\SYSTEM\sqlsrv32.dll [NWIND] Driver=D:\WIN95\SYSTEM\SQLSRVR.DLL Description=Northwind Sample Database Server=OAKLEAF0 Database=nwind OemToAnsi=No LastUser=sa Language= Driver32=D:\WIN95\SYSTEM\sqlsrv32.dll
The first six entries in the [ODBC 32 bit Data Sources] section are created automatically by Microsoft Office 95 (if you specify the data access option when installing Office 95). Northwind and NWIND are two ODBC data sources for the same SQL Server
database, nwind, which was created by exporting the tables from Access 2.0's NWIND.MDB database. The top dialog of Figure 19.3 illustrates some of the entries in the Data Sources dialog of the 32-bit ODBC Administrator that correspond to the entries in the
preceding Odbc.ini file. The bottom dialog illustrates the 16-bit ODBC Administrator's view of the data sources. Both 16-bit and 32-bit data sources appear in the 16-bit ODBC Administrator dialog.
ODBC data sources for which a 16-bit and a 32-bit driver are available specify both drivers in a single data source entry. As an example, the Northwind data source includes an entry for the 16-bit SQL Server driver (Driver=d:\path\SQLSVR.DRV) and the 32-bit driver (Driver32=d:\path\sqlsvr32.drv).
Figure 19.3. ODBC data sources for desktop and SQL Server databases shown in the 32-bit (top) and 16-bit (bottom) version of ODBC Administrator.
Information on 32-bit ODBC drivers and data sources that use 32-bit ODBC drivers contained in Odbcinst.ini and Odbc.ini is duplicated in the Registry. The HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI key includes a set of subkeys that duplicates the
sections of Odbcinst.ini (see Figure 19.4). The subkeys of HKEY_CURRENT_USER\Software\ODBC\ODBC.INI are the same as the sections of Odbc.ini, as illustrated by Figure 19.5. Data sources might vary for multiple users of the same computer—this explains
the location of data source information in the HKEY_CURRENT_USER subkey. Information on 16-bit drivers and data sources that rely only on 16-bit drivers, such as [MS Access 2.0 Databases], does not appear in the Windows 95 or the Windows NT Registry.
Figure 19.4. Entries in the ODBCINST.INI Registry subkey for installed 32-bit ODBC 2.5 drivers.
Duplication of 32-bit entries in the Odbcinst.ini, Odbc.ini, and the Registry appears to be intended for backward compatibility when upgrading Windows 3.1x to Windows 95. Many developers directly edit the Windows 3.x ODBCINST.INI and
ODBC.INI files either manually or in code (using the Windows API's WritePrivateProfileString function). Use the 32-bit ODBC Administrator to edit these entries so as to create the required Registry entries. The SaveSettings function of 32-bit Visual
Basic 4.0 only is capable of adding entries to the HKEY_CURRENT_USER\Software\VB and VBA Program Settings\AppName\SectionName hive.
There is little need to modify the default values for ODBC settings that the Jet 2.5 and 3.0 database engines use with the 16-bit and 32-bit ODBC drivers, respectively. The majority of the settings only apply to SQL databases, such as Microsoft SQL
Server. The default option settings are satisfactory for the majority of client/server applications that connect to current releases of SQL databases. If you need to change an ODBC setting, you must create the appropriate registry entry for 32-bit
applications or add an [ODBC] section to VB.INI or APPNAME.INI with the ODBC setting name and new value. You use entries in the .INI file for 16-bit Visual Basic applications that use the Jet 2.5 database engine; 32-bit applications require Registry
entries in the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\Engines\ODBC hive.
Table 19.5 lists the ODBC settings recognized by versions 2.5 and 3.0 of the Jet database engine. For 16-bit Jet 2.5, you add an [ODBC] section to VB.INI or APPNAME.INI and entries selected from the ODBC Setting Name column, followed by = and
the value shown in the Purpose column. Here's a typical set of entries to enable tracing of both ODBC API and ODBC SQL calls:
[ODBC] TraceODBCAPI=1 TraceSQLMode=1
For Jet 3.0, you set the string value of the subkey of the ODBC Setting Name column to the value you want as specified in the Purpose column. Figure 19.6 shows the Registry settings that correspond to the preceding example for .INI files.
| ODBC Setting Name | Purpose |
| AsyncRetryInterval | Milliseconds of polling interval for asynchronous query completion (500) |
| AttachableObjects | Server object types allowed to attach to .mdb databases ('TABLE', 'VIEW', 'SYSTEM TABLE', 'ALIAS', 'SYNONYM') |
| AttachCaseSensitive | 0 for case-insensitive table name matching; 1 for case-sensitive matching (0) |
| ConnectionTimeout | Seconds before an idle cached connection times out (600) |
| DisableAsync | 0 to use asynchronous query execution, if possible; 1 to force synchronous query execution (0) |
| FastRequery | 1 to use a prepared SELECT statement for parameterized queries; 0 to recompile the SELECT statement on each execution (0) |
| JetTryAuth | 1 to use the current Microsoft Access user name and password to log in to the server; 0 to always prompt (1) |
| LoginTimeout | Seconds before a login attempt times out (20) |
| PreparedInsert | 0 to insert only non-Null values into columns; 1 to insert data into all columns, regardless of Null values in the query (0) |
| PreparedUpdate | 0 to update only with non-Null values; 1 to update data in all columns, regardless of Null values in the query (0) |
| QueryTimeout | Seconds before a running times out (60) |
| SnapshotOnly | 0 to allow Dynaset- or Snapshot-type Recordset objects; 1 for Snapshot-type Recordset objects only (0) |
| TraceODBCAPI | 1 to trace execution of ODBC API calls to Odbcapi.txt; 0 for no tracing (0) |
| TraceSQLMode | 1 to trace execution of SQL statements to Sqlout.txt; 0 for no tracing (0) |
The "Changing Microsoft ODBC Settings" topic of the online help for Visual Basic 4.0 states that installation of the Microsoft ODBC database driver "writes a set of default values to the Windows registry in the Engines and ISAM Formats subkeys for Jet 3.0." This statement implies that Setup creates the ...\Jet\3.0\Engine\ODBC hive and adds the values for you. Setup does add the ISAM Formats key and subkeys for each of the formats supported by Jet 3.0, but not the ...\ODBC hive and values. You must use RegEdit's Edit | New | Key menu command to add the ODBC hive, and then use Edit | New | String Value to add New Value #1. Rename New Value #1 to the ODBC setting name, and then use Edit | Modify to open the Edit String dialog. Enter the new value in the Value data text box and click OK to close the dialog.
Most of the entries in Table 19.5 are self-explanatory. However, the entries in the following list deserve a lengthier description than can be accommodated in a tabular format:
One of the many advantages of upgrading to (or acquiring) Microsoft Office 95 is that Microsoft Query 2.0 and the Microsoft Desktop Database Driver Pack 3.0 are bundled with this software suite. Microsoft Query is very useful for learning how to write
generic ANSI SQL statements and how to use the ODBC drivers that accompany MS Query. MS Query uses ANSI SQL (with modifications to accommodate ODBC data types and file directories), not Jet SQL, to execute SELECT queries. Therefore, you can use MS Query to
validate SQL SELECT query statements you write in the form of string variables in your Visual Basic database applications that use the SQL passthrough option. If you don't have Microsoft Access, MS Query is your only option for graphically creating SQL
SELECT queries.
The sections that follow describe how to create an ODBC data source, Bibliography, from the Jet 2.0 Biblio.mdb database and how to use Microsoft Query as a stand-alone application to execute queries against the data source.
The Odbcjt32.dll ODBC driver can handle Jet 1.x, 2.x, and 3.0 database files interchangeably. To use the 32-bit ODBC Administrator to create an ODBC data source based on the Biblio.mdb database included with Visual Basic 4.0, follow these
steps:
You can verify that the 32-bit ODBC Administrator creates entries for your new data source in Odbc.ini and in the HKEY_CURRENT_USER\Software\ODBC\Bibliography hive. Figure 19.11 shows the keys and values for the Bibliography data source. The
...\Bibliography\Engines\Jet 2.x hive indicates that Biblio.mdb is a Jet 2.x database and stores the default values of the MaxBufferSize and PageTimeout ODBC settings that appear at the bottom of the ODBC Microsoft Access 7.0 Setup dialog.
Figure 19.11. Registry entries for the 32-bit Bibliography ODBC data source.
Follow these steps to launch Microsoft Query independently of Microsoft Excel or Word and to experiment with the Bibliography ODBC data source:
Figure 19.17. Displaying a criterion in the middle pane of MS Query's window.
Executing queries against ODBC data sources results in an extraordinary number of function calls to the ODBC driver manager. Figure 19.18 shows just the first few function calls required to create a connection to the ODBC data source and execute the
queries generated by following the preceding steps.
Logging ODBC SQL calls creates an extraordinary amount of data in Sql.log and drastically slows the execution speed of queries. The queries executed in this section result in a Sql.log file size of about 1.1MB. Once you've reviewed at least part of the contents of Sql.log, launch Control Panel, open the 32-bit SQL Administrator, click the Options button, and clear the Trace ODBC Calls check box. Then, delete Sql.log to conserve disk space.
The SQL statement generated by Microsoft Query, shown in Figure 19.15, differs considerably from the Jet SQL statement you send to the Jet database engine in Visual Basic 4.0 database applications. The characteristics of SQL statements created by MS
Query that distinguish the statements from Jet SQL are as follows:
Figure 19.19. Microsoft Query's SQL dialog displaying the escape syntax for a LEFT OUTER JOIN.
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. An example was given of creating an ODBC data source from an Access
database file and using the ODBC data source with Microsoft Query. Differences between the ANSI/ODBC SQL syntax that you use with Microsoft Query and when you implement the SQL passthrough option for your queries also were covered.
The next chapter, "Creating Front-Ends for Client/Server Databases," shows you how to create ODBC data sources for Microsoft SQL Server 4.x and 6.x databases and how to optimize Visual Basic 4.0 applications that connect to
client/server RDBMSs.
