📄 vcg07.htm
字号:
<HTML>
<HEAD>
<TITLE>vcg07.htm </TITLE>
<LINK REL="ToC" HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/index.htm">
<LINK REL="Index" HREF="htindex.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/htindex.htm">
<LINK REL="Next" HREF="vcg08.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg08.htm">
<LINK REL="Previous" HREF="vcg06.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg06.htm"></HEAD>
<BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#800080">
<A NAME="I0"></A><P ALIGN=CENTER>
<A HREF="vcg06.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg06.htm" TARGET="_self"><IMG SRC="blanprev.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blanprev.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Previous Page"></A>
<A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/index.htm" TARGET="_self"><IMG SRC="blantoc.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blantoc.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="TOC"></A>
<A HREF="vcg08.htm" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/vcg08.htm" TARGET="_self"><IMG SRC="blannext.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/blannext.gif" WIDTH = 37 HEIGHT = 37 BORDER = 0 ALT="Next Page"></A>
<HR ALIGN=CENTER>
<P>
<UL>
<UL>
<UL>
<LI>
<A HREF="#E68E45" >Understanding the Structure of the ODBC API</A>
<UL>
<LI>
<A HREF="#E69E143" >Single-Tier and Multiple-Tier ODBC Drivers</A>
<LI>
<A HREF="#E69E144" >ODBC API Conformance Levels</A>
<LI>
<A HREF="#E69E145" >ODBC SQL Conformance Levels</A>
<LI>
<A HREF="#E69E146" >ODBC Data Types and the Access Database Engine</A>
<LI>
<A HREF="#E69E147" >Built-In ODBC Scalar Functions</A>
<UL>
<LI>
<A HREF="#E70E45" >String Manipulation Functions</A>
<LI>
<A HREF="#E70E46" >Numeric Scalar Functions</A>
<LI>
<A HREF="#E70E47" >Date, Time, and Timestamp Functions</A></UL>
<LI>
<A HREF="#E69E148" >ODBC Initialization Files</A>
<UL>
<LI>
<A HREF="#E70E48" >ODBCINST.INI</A>
<LI>
<A HREF="#E70E49" >ODBC.INI</A></UL></UL>
<LI>
<A HREF="#E68E46" >Using Desktop ODBC Datasources with Visual C++</A>
<UL>
<LI>
<A HREF="#E69E149" >Adding STDREG32.MDB as an ODBC Datasource</A>
<LI>
<A HREF="#E69E150" >Using the Student Registration Datasource in a Visual C++ Program</A>
<LI>
<A HREF="#E69E151" >Tracing the Execution of ODBC Function Calls and SQL Statements</A></UL>
<LI>
<A HREF="#E68E47" >Using Microsoft Query and the Desktop Database Drivers</A>
<UL>
<LI>
<A HREF="#E69E152" >Creating an ODBC Datasource from dBASE Files</A>
<LI>
<A HREF="#E69E153" >Using an ODBC Datasource with Microsoft Query</A>
<LI>
<A HREF="#E69E154" >Differences Between Access SQL and ANSI/ODBC SQL Statements</A></UL>
<LI>
<A HREF="#E68E48" >Summary</A></UL></UL></UL>
<HR ALIGN=CENTER>
<A NAME="E66E19"></A>
<H1 ALIGN=CENTER>
<CENTER>
<FONT SIZE=6 COLOR="#FF0000"><B>- 7 -</B>
<BR><B>Using the Open Database Connectivity API</B></FONT></CENTER></H1>
<BR>
<P>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.)
<BR>
<P>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.
<BR>
<BR>
<A NAME="E68E45"></A>
<H3 ALIGN=CENTER>
<CENTER>
<FONT SIZE=5 COLOR="#FF0000"><B>Understanding the Structure of the ODBC API</B></FONT></CENTER></H3>
<BR>
<P>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:
<BR>
<UL>
<LI>Installing, setting up, and removing ODBC datasources. A <I>datasource</I> is a named connection to a database. You need an ODBC driver for each different type of database to which your applications connect. The ODBC API is designed primarily for use with client-server RDBMSs, but you also can use ODBC drivers to connect to desktop database files, worksheets, and flat text files. ODBC uses the ODBCCP32.DLL library to set up and remove datasources. ODBCAD32.EXE is a stand-alone, executable version of ODBCCP32.DLL that is supplied with Access and Visual C++.
<BR>
<BR>
<LI>Managing the communication of queries and other SQL statements from client front ends to database server back ends, and the transfer of query result sets or confirmations of the execution of action queries in the reverse direction. The ODBC driver manager, ODBC32.DLL, opens the ODBC driver for the datasource and then passes SQL statements to the driver. After a client-server RDBMS processes the query, the ODBC driver returns the rows through ODBC32.DLL to your application.
<BR>
<BR>
</UL>
<P>The ODBC API implements SQL as a call-level interface (CLI). A <I>call-level interface</I> 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.
<BR>
<P>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.
<BR>
<P><B><A HREF="07vcg01.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/07vcg01.gif">Figure 7.1. The full structure of the ODBC API as it is employed by Visual C++ database applications.</A></B>
<BR>
<P>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."
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>NOTE</B>
<BR>
<BR>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 <I>de facto</I> 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.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<P>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.
<BR>
<BLOCKQUOTE>
<BLOCKQUOTE>
<HR ALIGN=CENTER>
<BR>
<NOTE><B>NOTE</B>
<BR>
<BR>Most of the information in the following sections is derived from Microsoft's <I>Programmer's Reference</I> 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.
<BR>
<BR>An additional document called <I>The Jet Database Engine </I><I>2.0: A User's Overview</I> by Paul Litwin can also be found on MSDN Level I.</NOTE>
<BR>
<HR ALIGN=CENTER>
</BLOCKQUOTE></BLOCKQUOTE>
<BR>
<A NAME="E69E143"></A>
<H4 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>Single-Tier and Multiple-Tier ODBC Drivers</B></FONT></CENTER></H4>
<BR>
<P>ODBC drivers are classified as members of one of the two following categories:
<BR>
<UL>
<LI><I>Single-tier</I> ODBC drivers are designed for use with DBMs that don't have the capability to process ANSI SQL statements. Single-tier drivers translate ANSI SQL statements into a long series of low-level instructions that operate directly on the files that constitute the database. At present, Microsoft and Q+E single-tier ODBC drivers connect to dBASE, FoxPro, Paradox, Btrieve, Excel, and text files. Microsoft is said to be currently working on a Lotus 1-2-3 spreadsheet driver, scheduled to be released in early 1996.
<BR>
<BR>
<LI><I>Multiple-tier</I> ODBC drivers rely on the client-server RDBMS to process SQL statements. Using the back-end server to process queries is a more efficient process than the single-tier approach in a multiuser or networked environment. Network traffic is minimized because the server returns only the rows specified by the criteria of your SQL statement.
<BR>
<BR>
</UL>
<P>This chapter concentrates on the use of single-tier ODBC drivers.
<BR>
<BR>
<A NAME="E69E144"></A>
<H4 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>ODBC API Conformance Levels</B></FONT></CENTER></H4>
<BR>
<P>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:
<BR>
<UL>
<LI>Core-level conformance provides for connecting to the database, preparing and executing SQL statements, receiving query result datasets, committing or rolling back transactions, and retrieving error information.
<BR>
<BR>
<LI>Level 1 conformance is similar to core-level conformance. It adds connectivity to datasources with dialogs designed specifically for the ODBC driver, get- and set-connection options, and obtain-catalog and driver/datasource capabilities.
<BR>
<BR>
<LI>Level 2 conformance is similar to level 1. It adds the capability to list and browse the connections to datasources available to the client, retrieve query result sets in array format, use scrollable cursors, and obtain additional catalog information, such as privileges, keys, and lists of stored procedures.
<BR>
<BR>
</UL>
<P>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.
<BR>
<BR>
<A NAME="E69E145"></A>
<H4 ALIGN=CENTER>
<CENTER>
<FONT SIZE=4 COLOR="#FF0000"><B>ODBC SQL Conformance Levels</B></FONT></CENTER></H4>
<BR>
<P>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.
<BR>
<BR>
<P ALIGN=CENTER>
<CENTER>
<FONT COLOR="#000080"><B>Table 7.1. SQL grammar, data type, and expression support for ODBC grammar conformance levels.</B></FONT></CENTER>
<BR>
<CENTER><TABLE BORDER>
<TR>
<TD><FONT COLOR=#000080>
<I>Level</I>
</FONT>
<TD><FONT COLOR=#000080>
<I>DML </I><I>Statements</I>
</FONT>
<TD><FONT COLOR=#000080>
<I>DDL/DCL </I><I>Statements</I>
</FONT>
<TD><FONT COLOR=#000080>
<I>Data Types</I>
</FONT>
<TD><FONT COLOR=#000080>
<I>Expressions</I></FONT>
<TR>
<TD><FONT COLOR=#000080>
Minimum
</FONT>
<TD><FONT COLOR=#000080>
SELECT,
</FONT>
<TD><FONT COLOR=#000080>
CREATE
</FONT>
<TD><FONT COLOR=#000080>
SQL_CHAR
</FONT>
<TD><FONT COLOR=#000080>
Simple
</FONT>
<TR>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080>
INSERT,
</FONT>
<TD><FONT COLOR=#000080>
TABLE,
</FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080>
arithmetic
</FONT>
<TR>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080>
UPDATED
</FONT>
<TD><FONT COLOR=#000080>
DROP TABLE
</FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TR>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080>
SEARCHED,
</FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TR>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080>
DELETE
</FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TR>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080>
SEARCHED,
</FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TR>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080>
COMMIT
</FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TR>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080>
TRANSACTION,
</FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080><BR></FONT>
<TR>
<TD><FONT COLOR=#000080><BR></FONT>
<TD><FONT COLOR=#000080>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -