vcg05.htm

Visual C++与数据库的连接经典实例

<P>Consider yourself fortunate that you're using Visual C++ 4.0 to process SQL statements. You don't need to worry about how many rows a query will return or what data types occur in the query result set's columns. The CRecordSet object receiving the data handles all of these details for you. With Visual C++ 4's incremental compile and incremental link, you don't need to recompile and link your entire Visual C++ application each time you change a query statement; just change the statement and rebuild your application. Visual C++ compiles and links only the functions that have been changed. The process is really quite fast.

<BR>

<BR>

<A NAME="E69E118"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Types of ANSI SQL</B></FONT></CENTER></H4>

<BR>

<P>The current ANSI SQL standards recognize four different methods of executing SQL statements. The method you use depends on your application programming environment, as described in the following list:

<BR>

<UL>

<LI><I>Interactive SQL</I> lets you enter SQL statements at a command line prompt, similar to dBASE's dot prompt. As mentioned in Chapter 1, &quot;Positioning Visual C++ in the Desktop Database Market,&quot; the use of the interactive dBASE command LIST is quite similar to the SELECT statement in interactive SQL. Mainframe and client-server RDBMSs also provide interactive SQL capability; Microsoft SQL Server provides the isql application for this purpose. Using interactive SQL is also called <I>direct invocation</I>. Interactive SQL is called a <I>bulk process;</I> if you enter a query at the SQL prompt, the result of your query appears on-screen. DBMs offer a variety of methods of providing a scrollable display of interactive query result sets.

<BR>

<BR>

<LI><I>Embedded SQL</I> lets you execute SQL statements by preceding the SQL statement with a keyword, such as EXEC SQL in C. Typically, you declare variables that you intend to use to receive data from an SQL query between EXEC SQL BEGIN DECLARE SECTION and EXEC SQL END DECLARE SECTION statements. You need a precompiler that is specific to the language and to the RDBMS to be used. The advantage of embedded SQL is that you assign attribute classes to a single variable in a one-step process. The disadvantage is that you have to deal with query-return sets on a row-by-row basis rather than the bulk process of interactive SQL.

<BR>

<BR>

<LI><I>Module SQL</I> lets you compile SQL statements separately from your 3GL source code and then link the compiled object modules into your executable program. SQL modules are similar to Visual C++ 4.0 code modules. The modules include declarations of variables and temporary tables to contain query result sets, and you can pass argument values from your 3GL to parameters of procedures declared in SQL modules. The stored procedures that execute precompiled queries on database servers have many characteristics in common with module SQL.

<BR>

<BR>

<LI><I>Dynamic SQL</I> lets you create SQL statements whose contents you can't predict when you write the statement. (The preceding SQL types are classified as <I>static SQL</I>.) As an example of dynamic SQL, suppose you want to design a Visual C++ application that can process queries against a variety of databases. Dynamic SQL lets you send queries to the database in the form of strings. For example, you can send a query to the database and obtain detailed information from the database catalog that describes the tables and fields of tables in the database. Once you know the structure of the database, you or the user of your application can construct a custom query that adds the correct field names to the query. Visual C++'s implementation of Access SQL resembles a combination of dynamic and static SQL, although the Access database engine handles the details of reading the catalog information for you automatically when your application creates a Recordset object from the database. Chapter 6, &quot;Understanding the Access Database Engine and DAO,&quot; describes the methods you use to extract catalog information contained in Visual C++ collections.

<BR>

<BR>

</UL>

<P>Technically, static SQL and dynamic SQL are called methods of <I>binding</I> SQL statements to database application programs. Binding refers to how you combine or attach SQL statements to your source or object code, how you pass values to SQL statements, and how you process query result sets. A third method of binding SQL statements is the <I>call-level </I><I>interface</I> (CLI). The Microsoft Open Database Connectivity (ODBC) API uses the CLI developed by the SQL Access Group (SAG), a consortium of RDBMS publishers and users. A CLI accepts SQL statements from your application in the form of strings and passes the statements directly to the server for execution. The server notifies the CLI when the data is available and then returns the data to your application. Details of the ODBC CLI are given in Chapter 7.

<BR>

<P>If you're a COBOL coder or a C/C++ programmer who is accustomed to writing embedded SQL statements, you'll need to adjust to Visual C++'s automatic creation of virtual tables when you execute a SELECT query, rather than executing CURSOR-related FETCH statements to obtain the query result rows one-by-one.

<BR>

<BR>

<A NAME="E68E34"></A>

<H3 ALIGN=CENTER>

<CENTER>

<FONT SIZE=5 COLOR="#FF0000"><B>Writing ANSI SQL Statements</B></FONT></CENTER></H3>

<BR>

<P>ANSI SQL statements have a very flexible format. Unlike all dialects of BASIC, which separate statements with newline pairs (a carriage return and a line feed), and C, C++, and Pascal, which use semicolons as statement terminators, SQL doesn't require you to separate the elements that constitute a complete SQL statement with newline pairs, semicolons, or even a space in most cases. (SQL ignores most white space, which comprises newline pairs, tabs, and extra spaces.) Thus, you can use white space to format your SQL statements to make them more readable. The examples of SQL statements in this book place groups of related identifiers and SQL reserved words on separate lines and use indentation to identify continued lines. Here's an example of an Access SQL crosstab query statement that uses this formatting convention:

<BR>

<PRE>

<FONT COLOR="#000080">TRANSFORM Sum(CLng([Order Details].UnitPrice*Quantity*

   (1 - Discount)*100)/100) AS ProductAmount

   SELECT Products.ProductName, Orders.CustomerID

   FROM Orders, Products, [Order Details],

   Orders INNER JOIN [Order Details] ON Orders.OrderID =

      [Order Details].OrderID,

   Products INNER JOIN [Order Details] ON Products.ProductID =

      [Order Details].ProductID

   WHERE Year(OrderDate)=1994

   GROUP BY Products.ProductName, Orders.CustomerID

   ORDER BY Products.ProductName

   PIVOT &quot;Qtr &quot; &amp; DatePart(&quot;q&quot;,OrderDate) In(&quot;Qtr 1&quot;,

      &quot;Qtr 2&quot;,&quot;Qtr 3&quot;,&quot;Qtr 4&quot;)</FONT></PRE>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>The square brackets surrounding the [Order Details] table name are specific to Access SQL and are used to group table or field names that contain spaces or other punctuation that is illegal in the naming rules for tables and fields of SQL RDBMSs. Access SQL also uses the double quotation mark (&quot;) to replace the single quotation mark (or apostrophe) ('), which acts as the string identifier character in most implementations of SQL. The preceding example of the SQL statement for a crosstab query is based on the tables in Access 95's Northwind.MDB sample database. Many field names in Access 2.0's NWIND.MDB contain spaces; spaces are removed from field names in NorthWind.MDB.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>The sections that follow describe how you categorize SQL statements and how the formal grammar of SQL is represented. They also provide examples of writing a variety of select queries in ANSI SQL.

<BR>

<BR>

<A NAME="E69E119"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Categories of SQL Statements</B></FONT></CENTER></H4>

<BR>

<P>ANSI SQL is divided into the following six basic categories of statements, presented here in the order of most frequent use:

<BR>

<UL>

<LI><I>Data-query language</I> (DQL) statements, also called <I>data retrieval</I> statements, obtain data from tables and determine how that data is presented to your application. The SELECT reserved word is the most commonly used verb in DQL (and in all of SQL). Other commonly used DQL reserved words are WHERE, ORDER BY, GROUP BY, and HAVING; these DQL reserved words often are used in conjunction with other categories of SQL statements.

<BR>

<BR>

<LI><I>Data-manipulation language</I> (DML) statements include the INSERT, UPDATE, and DELETE verbs, which append, modify, and delete rows in tables, respectively. DML verbs are used to construct action queries. Some books place DQL statements in the DML category.

<BR>

<BR>

<LI><I>Transaction-processing language</I> (TPL) statements are used when you need to make sure that all the rows of tables affected by a DML statement are updated at once. TPL statements include BEGIN TRANSACTION, COMMIT, and ROLLBACK.

<BR>

<BR>

<LI><I>Data-control language</I> (DCL) statements determine access of individual users and groups of users to objects in the database through permissions that you GRANT or REVOKE. Some RDBMSs let you GRANT permissions to or REVOKE permissions from individual columns of tables.

<BR>

<BR>

<LI><I>Data-definition language</I> (DDL) statements let you create new tables in a database (CREATE TABLE), add indexes to tables (CREATE INDEX), establish constraints on field values (NOT NULL, CHECK, and CONSTRAINT), define relations between tables (PRIMARY KEY, FOREIGN KEY, and REFERENCES), and delete tables and indexes (DROP TABLE and DROP INDEX). DDL also includes many reserved words that relate to obtaining data from the database catalog. This book classifies DDL queries as action queries because DDL queries don't return records.

<BR>

<BR>

<LI>Cursor-control language (CCL) statements, such as DECLARE CURSOR, FETCHINTO, and UPDATE WHERE CURRENT, operate on individual rows of one or more tables.

<BR>

<BR>

</UL>

<P>It's not obligatory that a publisher of a DBM who claims to conform to ANSI SQL support all of the reserved words in the SQL-92 standard. In fact, it's probably safe to state that, at the time this book was written, no commercial RDBMS implemented all the SQL-92 keywords for interactive SQL. The Jet 3.0 database engine, for example, doesn't support any DCL reserved words. You use the Data Access Object's programmatic security objects with Visual C++ reserved words and keywords instead. The Jet 3.0 engine doesn't need to support CCL statements, because neither Visual C++ 4.0 nor Access manipulates cursors per se. Visual C++ 4.0's Data control creates the equivalent of a scrollable (bidirectionally movable) cursor. The Remote Data Object supports the scrollable cursors of Microsoft SQL Server 6.0.

<BR>

<P>This book uses the terminology defined by Appendix C of the Programmer's Reference for the Microsoft ODBC Software Development Kit (SDK) to define the following levels of SQL grammatical compliance:

<BR>

<UL>

<LI><I>Minimum:</I> The statements (grammar) that barely qualify a DBM as an SQL DBM but not an RDBMS. A DBM that provides only the minimum grammar is not salable in today's market.

<BR>

<BR>

<LI><I>Core:</I> Comprising minimum grammar plus basic DDL and DCL commands, additional DML functions, data types other than CHAR, SQL aggregate functions such as SUM() and AVG(), and a wider variety of allowable expressions to select records. Most desktop DBMs, to which SQL has been added, support core SQL grammar and little more.

<BR>

<BR>

<LI><I>Extended:</I> Comprising minimum and core grammar, plus DML outer joins, more complex expressions in DML statements, all ANSI SQL data types (as well as long varchar and long varbinary), batch SQL statements, and procedure calls. Extended SQL grammar has two levels of conformance&#151;1 and 2.

<BR>

<BR>

</UL>

<BR>

<A NAME="E69E120"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>The Formal Grammar of SQL</B></FONT></CENTER></H4>

<BR>

<P>The formal grammar of SQL is represented in the Backus Naur Form (BNF), which is used to specify the formal grammar of many computer programming languages. Here is the full BNF form of the verb that specifies the operation that a query is to perform on a database:

<BR>

<PRE>

<FONT COLOR="#000080">&lt;<I>action</I>&gt; ::=

SELECT

|DELETE

|INSERT [ &lt;<I>left paren</I>&gt; &lt;<I>privilege column list</I>&gt; &lt;<I>right paren</I>&gt;]

|UPDATE [ &lt;<I>left paren</I>&gt; &lt;<I>privilege column list</I>&gt; &lt;<I>right paren</I>&gt;]

|REFERENCES  [ &lt;<I>left paren</I>&gt; &lt;<I>privilege column list</I>&gt; &lt;<I>right paren</I>&gt;]

|USAGE

...

&lt;<I>privilege column list</I>&gt; ::= &lt;<I>column name list</I>&gt;

...

&lt;<I>column name list</I>&gt; ::= &lt;<I>column name</I>&gt; [{&lt;<I>comma</I>&gt;, &lt;<I>column name</I>&gt;} ...]</FONT></PRE>

<P>To use BNF representation, you locate the class (&lt;<I>action</I>&gt; in the preceding example) where the reserved word is included. Members of the class are separated by the vertical bar (|) character. Optional parameters of reserved words and elements are enclosed in square brackets ([]). Literal values, such as &lt;<I>privilege</I><I> </I><I>column</I><I> </I><I>list</I>&gt;, are enclosed in angle braces (&lt;&gt;), and elements that must be grouped, such as a comma preceding a second &lt;<I>column</I><I> </I><I>name</I>&gt;, are enclosed in French braces ({}). You then search the list of elements to find the allowable composition of an element. In this example, the &lt;<I>privilege</I><I> </I><I>column</I><I> </I><I>list</I>&gt; is composed of the &lt;<I>column</I><I> </I><I>name</I><I> </I><I>list</I>&gt;. Then check to see if &lt;<I>column</I><I> </I><I>name</I><I> </I><I>list</I>&gt; has a composition (in this case, one or more &lt;<I>column</I><I> </I><I>name</I>&gt; elements). This process is tedious, especially when the elements aren't arranged in alphabetical order.

<BR>

<P>Microsoft uses a simplified form of BNF to describe the grammar supported by the present version of the ODBC API. The Access SQL syntax rules eliminate the use of the ::= characters to indicate the allowable substitution of values for an element. Instead, they substitute a tabular format, as shown in Table 5.1. Ellipses (...) in the table indicate that you have to search for the element; the element is not contiguous with the preceding element of the table.

<BR>

<BR>

<P ALIGN=CENTER>

<CENTER>

<FONT COLOR="#000080"><B>Table 5.1. The partial syntax of the Access SQL SELECT statement.</B></FONT></CENTER>

<BR>



<CENTER><TABLE  BORDERCOLOR=#000040 BORDER=1 CELLSPACING=2 CELLPADDING=3 >

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<I>Element</I>

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

<I>Syntax</I>

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

select-statement

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

SELECT[ALL|DISTINCT|DISTINCTROW] select-list table-expression

</FONT>

<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

...

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080><BR></FONT>



<TR>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>

select-list

</FONT>

<TD VALIGN=top  BGCOLOR=#80FFFF ><FONT COLOR=#000080>