vcg05.htm

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

<LI>MS Query displays the Add Tables dialog box. Select the Authors table and click the Add button. Then click the Close button.

<BR>

<BR>

<LI>MS Query displays its Query 1 MDI child window. Click the SQL button on the toolbar.

<BR>

<BR>

<LI>Enter SELECT * FROM Authors in the SQL Statement window as a simple query to check whether MS Query works, as shown in Figure 5.1.

<BR>

<BR>

<P><B><A HREF="05vcg01.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/05vcg01.gif">Figure 5.1. An MS Query application with an SQL test query.</A></B>

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>Access SQL statements require a semicolon statement terminator. The MS Query application doesn't need a semicolon at the end of the SQL statement. Adding a semicolon will disable MS Query's graphical query representation, but the query will still work as expected.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<LI>Click the OK button in the SQL dialog box. The query result set appears in the child window.

<BR>

<BR>

<LI>Click the SQL toolbar button. The query, reformatted to fully qualify all names, appears in the SQL dialog box. The query now reads SELECT Authors.Au_ID, Authors.Author FROM Authors Authors. Figure 5.2 shows a portion of the query result set and the reformatted SQL query.

<BR>

<BR>

<P><B><A HREF="05vcg02.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/05vcg02.gif">Figure 5.2. The query result window and reformatted query in the MS Query application.</A></B>

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>A typical result of this type of query, which returns 46 rows in .0547 seconds, is approximately 840 rows/second. A 486DX2/66 with local bus video and 16M of RAM was used for these tests. These rates represent quite acceptable performance for a Windows database front end.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<LI>Reopen the SQL dialog box and clear the current SQL query edit box of the SQL Statement window. Enter SELECT * FROM Publishers WHERE State = 'NY' in the SQL Statement window and then click the OK button. The results of this query appear in Figure 5.3.

<BR>

<BR>

<P><B><A HREF="05vcg03.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/05vcg03.gif">Figure 5.3. A query that returns records for publishers located in New York.</A></B>

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>In this case, the query-data return rate and the display rate are about 24 rows per second. The query-data return rate was reduced because there are more columns in the Publishers table (eight) than in the Authors table (two). However, if the query-data return rate is inversely proportional to the number of columns, the rate should be 840 * 2 / 8, or 210 rows per second. The extrapolated grid display rate, 170 * 2 / 8, is 42.5 rows per second, which is closer to the 24 rows per second rate of the prior example and can be accounted for by the greater average length of the data in the fields. Part of the difference between 24 and 210 rows per second for the query-data return rate is because the Access database engine must load the data from the table on the fixed disk into a temporary buffer. If you run the query again, you'll find that the rate increases to 8 / 0.0625, or 128 rows per second. The remainder of the difference in the query-data return rate is because the Access database engine must test each value of the State field for 'NY'.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<LI>Open the SQL dialog box again and add ORDER BY Zip to the end of your SQL statement. Figure 5.4 shows the query and its result.

<BR>

<BR>

<P><B><A HREF="05vcg04.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/05vcg04.gif">Figure 5.4. The records for publishers in New York sorted by zip code.</A></B>

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>The data return rate will now have dropped to about 68 rows per second. The decrease in speed can be attributed to the sort operation that you added to the query. The data-return rates and data-display rates you achieve will depend on the speed of the computer you use. As a rule, each clause you add to a query will decrease the data-return rate because of the additional data-manipulation operations that are required.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<LI>Replace the * in the SELECT statement, which returns all fields, with PubID, 'Company Name', City so that only three of the fields appear in the SnapShot window. The result, shown in Figure 5.5, demonstrates that you don't have to include the fields that you use for the WHERE and ORDER BY clauses in the <I>field_names</I> list of your SELECT statement.

<BR>

<BR>

</OL>

<P><B><A HREF="05vcg05.gif" tppabs="http://202.113.16.101/%7eeb%7e/Database%20Developer's%20Guide%20with%20Visual%20C++%204,%20Second%20Edition/05vcg05.gif">Figure 5.5. The query return set displaying only three fields of the Publishers table.</A></B>

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>The single quotes (') surrounding Company Name are necessary when a field name or table name contains a space. Only Access databases permit spaces and punctuation other than the underscore (_) in field names. Using spaces in field and table names, or in the names of any other database objects, is not considered good database-programming practice. Spaces in database field names and table names appear in this book only when such names are included in sample databases created by others.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>The MS Query toolbar provides a number of buttons that let you search for records in the table, filter the records so that only selected records appear, and sort the records on selected fields. A <I>filter</I> is the equivalent of adding a WHERE <I>field_name</I><I> </I><I>where_expression</I> clause to your SQL statement. The sort buttons add an ORDER BY <I>field_names</I> clause.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>Microsoft designed the MS Query application to demonstrate the features of SQL and ODBC that pertain to manipulating and displaying data contained in the tables of databases. MS Query is a rich source of SQL examples. It also contains useful examples of user interface design techniques for database applications and MDI child forms.

<BR>

<BR>

<A NAME="E69E123"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>SQL Operators and Expressions</B></FONT></CENTER></H4>

<BR>

<P>As I mentioned earlier in this chapter, SQL provides the basic arithmetic operators (&lt;, &lt;=, =, =&gt;, &gt;, and &lt;&gt;). SQL also has a set of operators that are used in conjunction with values of fields of the text data type (LIKE and IN) and that deal with NULL values in fields (IS NULL and IS NOT NULL). The Access database engine also supports the use of many string and numeric functions in SQL statements to calculate column values of query return sets. (Few of these functions are included in ANSI SQL.)

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>Access supports the use of user-defined functions (UDFs) in SQL statements to calculate column values in queries. Visual C++ and ODBC support only native functions that are reserved words, such as Val(). Functions other than SQL aggregate functions are called <I>implementation-specific</I> in ANSI SQL. Implementation-specific means that the supplier of the DBM is free to add functions to the supplier's implementation of ANSI SQL.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>The majority of the operators you use in SQL statements are <I>dyadic</I>. Dyadic functions require two operands. (All arithmetic functions and BETWEEN are dyadic.) Operators such as LIKE, IN, IS NULL, and IS NOT NULL are <I>monadic</I>. Monadic operators require only one operand. All expressions that you create with comparison operators return True or False, not a value. The sections that follow describe the use of the common dyadic and monadic operators of ANSI SQL.

<BR>

<BR>

<A NAME="E70E35"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Dyadic Arithmetic Operators and Functions</B></FONT></CENTER></H5>

<BR>

<P>The use of arithmetic operators with SQL doesn't differ greatly from their use in Visual C++ or other computer languages. The following is a list of the points you need to remember about arithmetic operators and functions used in SQL statements (especially in WHERE clauses):

<BR>

<UL>

<LI>The = and &lt;&gt; comparison operators are used for both text and numeric field data types. The angle-brace pair &quot;not-equal&quot; symbol (&lt;&gt;) is equivalent to the != combination used to represent &quot;not equal&quot; in ANSI SQL. (The equals sign isn't used as an assignment operator in SQL.)

<BR>

<BR>

<LI>The arithmetic comparison operators&#151;&lt;, &lt;=, =&gt;, and &gt;&#151;are intended primarily for use with operands that have numeric field data types. If you use the preceding comparison operators with values of the text field data type, the numeric ANSI values of each character of the two fields are compared in left-to-right sequence.

<BR>

<BR>

<LI>The remaining arithmetic operators&#151;+, -, *, /, and ^ or ** (the implementation-specific exponentiation operator )&#151;aren't comparison operators. These operators apply only to calculated columns of query result sets, the subject of the next section.

<BR>

<BR>

<LI>To compare the values of text fields that represent numbers, such as the zip code field of the Publishers table of BIBLIO.MDB, you can use the Val() function in a WHERE clause to process the text values as the numeric equivalent when you use the Access database engine. An example of this usage is SELECT * FROM Publishers WHERE Zip &gt; 12000.

<BR>

<BR>

</UL>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>If you attempt to execute the preceding SQL statement in MS Query (but not MSQRY32), you might receive an error message (usually with no text), or sometimes MS Query will simply GPF. The error is caused by Null values in the Zip Code data cells of several publishers in the table. Most expressions don't accept Null argument values. Thus, you need to add an IS NOT NULL criterion to your WHERE clause. If you use the form WHERE (Zip &gt; '12000' AND (Zip IS NOT NULL), you get the same error message, because the order in which the criteria are processed is the sequence in which the criteria appear in your SQL statement. Using WHERE Zip IS NOT NULL AND Zip &gt; '12000' solves the problem. The syntax of the NULL predicates is explained in the section &quot;Monadic Text Operators, Null Value Predicates, and Functions.&quot;</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<UL>

<LI>The BETWEEN predicate in ANSI SQL and the Between operator in Access SQL are used with numeric or date-time field data types. The syntax is <I>field_name</I> BETWEEN <I>Value1</I> AND <I>Value2</I>. This syntax is equivalent to the expression <I>field_name</I> =&gt; <I>Value1</I> OR <I>field_name</I> &lt;= <I>Value2</I>. Access SQL requires you to surround date-time values with number signs (#), as in <I>DateField</I> Between #1-1-93# And #12-31-93#. You can negate the BETWEEN predicate by preceding BETWEEN with NOT.

<BR>

<BR>

</UL>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>