vcg07.htm

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

<TR>

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



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



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



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

SQL_TIMESTAMP

</FONT>

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

</TABLE></CENTER><P>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.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>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.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<A NAME="E69E146"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>ODBC Data Types and the Access Database Engine</B></FONT></CENTER></H4>

<BR>

<P>Table 5.8 in Chapter 5, &quot;Learning Structured Query Language,&quot; 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.

<BR>

<P>There is no provision in Access field data types to specify the precision and scale of a numeric data type. <I>Precision</I> is the number of digits that compose the number (including digits in the fractional portion of the number). <I>Scale</I> 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.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>CAUTION</B>

<BR>

<BR>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.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<P>Access has a repertoire of field data types that is broader than that of any desktop database presently supported by commercial ODBC drivers.

<BR>

<BR>

<A NAME="E69E147"></A>

<H4 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Built-In ODBC Scalar Functions</B></FONT></CENTER></H4>

<BR>

<P>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. <I>Escape shorthand syntax,</I> called simply <I>escape syntax</I> in this book, encloses the shorthand syntax in French braces ({}). The escape syntax for all ODBC functions is

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">{fn <I>ODBCFunction</I>([<I>Argument</I>(<I>s</I>)])}</FONT></PRE>

<P>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:

<BR>

<BR>

<PRE>

<FONT COLOR="#000080">{fn CONCAT(first_name, CONCAT(' ', last_name))}</FONT></PRE>

<P>You need an embedded CONCAT() function, because CONCAT() supports concatenation of only two strings. (This is unlike concatenation with Visual C++'s &amp; symbol, which lets you concatenate any number of strings.) Note that the standard literal string identifier character is the single quote (') in ANSI SQL.

<BR>

<P>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 <I>Programmer's Reference</I> for the Microsoft Open Database Connectivity Software Development Kit.

<BR>

<BLOCKQUOTE>

<BLOCKQUOTE>

<HR ALIGN=CENTER>

<BR>

<NOTE><B>NOTE</B>

<BR>

<BR>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.</NOTE>

<BR>

<HR ALIGN=CENTER>

</BLOCKQUOTE></BLOCKQUOTE>

<BR>

<A NAME="E70E45"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>String Manipulation Functions</B></FONT></CENTER></H5>

<BR>

<P>Table 7.2 lists the ODBC string functions and their equivalent functions, where available, in Visual C++.

<BR>

<BR>

<P ALIGN=CENTER>

<CENTER>

<FONT COLOR="#000080"><B>Table 7.2. The scalar string functions of ODBC version 1.0.</B></FONT></CENTER>

<BR>



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

<TR>

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

<I>ODBC String Function</I>

</FONT>

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

<I>Purpose</I>

</FONT>

<TR>

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

ASCII(<I>string_exp</I>)

</FONT>

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

Returns the ASCII code value of the leftmost character of a string.

</FONT>

<TR>

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

CHAR(<I>integer_exp</I>)

</FONT>

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

Returns the ASCII character whose code is <I>integer_exp</I>.

</FONT>

<TR>

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

CONCAT(<I>string1</I>, <I>string2</I>)

</FONT>

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

Concatenates <I>string1</I> and <I>string2</I>.

</FONT>

<TR>

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

INSERT(<I>string1</I>, <I>start</I>,

</FONT>

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

Replaces the <I>length </I>characters of

</FONT>

<TR>

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

<I>length</I>, <I>string2</I>)

</FONT>

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

<I>string1 </I>beginning at <I>start</I> with <I>string2</I>.

</FONT>

<TR>

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

LEFT(<I>string_exp</I>, <I>count</I>)

</FONT>

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

Returns the leftmost <I>count</I> characters.

</FONT>

<TR>

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

LENGTH(<I>string_exp</I>)

</FONT>

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

Returns an integer representing the length of the string.

</FONT>

<TR>

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

LOCATE(<I>string1</I>,

</FONT>

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

Returns an integer representing the

</FONT>

<TR>

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

<I>string2</I>[, <I>start</I>])

</FONT>

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

position of <I>string2</I> in <I>string1</I>.

</FONT>

<TR>

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

LCASE(<I>string_exp</I>)

</FONT>

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

Returns an all-lowercase string.

</FONT>

<TR>

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

REPEAT(<I>string_exp</I>, <I>count</I>)

</FONT>

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

Returns a string consisting of <I>string_exp</I> repeated <I>count</I> times.

</FONT>

<TR>

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

RIGHT(<I>string_exp</I>, <I>count</I>)

</FONT>

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

Returns the rightmost <I>count</I> characters.

</FONT>

<TR>

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

RTRIM(<I>string_exp</I>)

</FONT>

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

Removes trailing blank spaces.

</FONT>

<TR>

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

SUBSTRING(<I>string_exp</I>,

</FONT>

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

Returns <I>length</I> characters beginning at

</FONT>

<TR>

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

<I>start</I>, <I>length</I>)

</FONT>

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

<I>start</I>.

</FONT>

<TR>

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

UCASE(<I>string_exp</I>)

</FONT>

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

Returns an all-uppercase string.</FONT>

</TABLE></CENTER><BR>

<A NAME="E70E46"></A>

<H5 ALIGN=CENTER>

<CENTER>

<FONT SIZE=4 COLOR="#FF0000"><B>Numeric Scalar Functions</B></FONT></CENTER></H5>

<BR>

<P>Table 7.3 lists the ODBC numeric functions, except for trigonometric functions, and their equivalent functions, where available, in Visual C++.

<BR>

<BR>

<P ALIGN=CENTER>

<CENTER>

<FONT COLOR="#000080"><B>Table 7.3. The numeric scalar functions available in ODBC version 1.0.</B></FONT></CENTER>

<BR>



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

<TR>

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

<I>ODBC Numeric Function</I>

</FONT>

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

<I>Purpose</I>

</FONT>

<TR>

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

ABS(<I>numeric_exp</I>)

</FONT>

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

Returns the absolute value of the expression.

</FONT>

<TR>

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

CEILING(<I>numeric_exp</I>)

</FONT>

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

Returns the next largest integer greater than the expression.

</FONT>

<TR>

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

EXP(<I>numeric_exp</I>)

</FONT>

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

Returns the exponential value of the expression.

</FONT>

<TR>

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

FLOOR(<I>numeric_exp</I>)

</FONT>

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

Returns the largest integer less than or equal to the expression.

</FONT>

<TR>

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

LOG(<I>float_exp</I>)

</FONT>

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

Returns the natural (naperian) logarithm of the expression.

</FONT>

<TR>

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

MOD(<I>integer_exp</I>)

</FONT>

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

Returns the remainder of integer division as an integer.

</FONT>

<TR>

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

PI()

</FONT>

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

Returns the value of pi as a floating-point number.

</FONT>

<TR>

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

RAND([<I>integer_exp</I>])

</FONT>

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

Returns a random floating-point number with an optional seed value.

</FONT>

<TR>

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

SIGN(<I>numeric_exp</I>)

</FONT>

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

Returns &#150;1 for values less than 0, 0 for 0 values, and 1 for values greater than 0.

</FONT>

<TR>

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

SQRT(<I>float_exp</I>)

</FONT>

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

Returns the square root of a floating-point value.</FONT>