📄 ch14.htm
字号:
<PRE><FONT COLOR="#0066FF"><B>SELECT * FROM ORDERS</B>
ORDEREDON NAME PARTNUM QUANTITY REMARKS
=========== ========== =========== =========== =======
15-MAY-1996 TRUE WHEEL 23 6 PAID
19-MAY-1996 TRUE WHEEL 76 3 PAID
2-SEP-1996 TRUE WHEEL 10 1 PAID
30-JUN-1996 TRUE WHEEL 42 8 PAID
30-JUN-1996 BIKE SPEC 54 10 PAID
30-MAY-1996 BIKE SPEC 10 2 PAID
30-MAY-1996 BIKE SPEC 23 8 PAID
17-JAN-1996 BIKE SPEC 76 11 PAID
17-JAN-1996 LE SHOPPE 76 5 PAID
1-JUN-1996 LE SHOPPE 10 3 PAID
1-JUN-1996 AAA BIKE 10 1 PAID
1-JUL-1996 AAA BIKE 76 4 PAID
1-JUL-1996 AAA BIKE 46 14 PAID
11-JUL-1996 JACKS BIKE 76 14 PAID</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT * FROM PART</B>
PARTNUM DESCRIPTION PRICE
=========== ==================== ===========
54 PEDALS 54.25
42 SEATS 24.50
46 TIRES 15.25
23 MOUNTAIN BIKE 350.45
76 ROAD BIKE 530.00
10 TANDEM 1200.00
</FONT></PRE>
<P>After you enter this data, the next step is to create an ODBC connection. Open
the Control Panel (if you are in Win 3.1, 3.11, or Windows 95) and double-click the
ODBC icon.
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Several flavors of SQL engines load
ODBC. Visual C++, Delphi, and Oracle7 load ODBC as part of their setup. Fortunately,
ODBC is becoming as common as printer drivers.
<HR>
</BLOCKQUOTE>
<P>The initial ODBC screen is shown in Figure 14.6.</P>
<P><A NAME="06"></A><A HREF="06-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/06-1.htm"><B>Figure 14.6.</B></A></P>
<P><I>ODBC's Data Sources selection.</I></P>
<P>This screen shows the current ODBC connections. You want to create a new connection.
Assuming you used InterBase and called the new database TYSSQL (give yourself 10
bonus points if you know what TYSSQL stands for), press the Add button and select
the InterBase Driver, as shown in Figure 14.7.</P>
<P><A NAME="07"></A><A HREF="07.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/07.htm"><B>Figure 14.7.</B></A></P>
<P><I>Driver selection.</I></P>
<P>From this selection you move to the setup screen. Fill it in as shown in Figure
14.8.</P>
<P><A NAME="08"></A><A HREF="08.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/08.htm"><B>Figure 14.8.</B></A></P>
<P><I>Driver setup.</I></P>
<P>You can use your own name or something short and easy to type, depending on the
account you set up for yourself. The only tricky bit here, at least for us, was figuring
out what InterBase wanted as a database name. Those of you coming from a PC or small
database background will have to get used to some odd-looking pathnames. These pathnames
tell the SQL engine where to look for the database in the galaxy of computers that
could be connected via LANs.
<H2><FONT COLOR="#000077">Using Microsoft Query to Perform a Join</FONT></H2>
<P>Now that you have made an ODBC connection, we need to make a slight detour to
a rather useful tool called Microsoft Query. This program is loaded along with Visual
C++. We have used it to solve enough database and coding problems to pay for the
cost of the compiler several times over. Query normally installs itself in its own
program group. Find it and open it. It should look like Figure 14.9.</P>
<P><A NAME="09"></A><A HREF="09.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/09.htm"><B>Figure 14.9.</B></A></P>
<P><I>Microsoft Query.</I></P>
<P>Select File | New Query. Your TYSSQL ODBC link does not appear, so click the Other
button to bring up the ODBC Data Sources dialog box, shown in Figure 14.10, and select
TYSSQL.</P>
<P><A NAME="10"></A><A HREF="10.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/10.htm"><B>Figure 14.10.</B></A></P>
<P><I>Data Sources dialog box.</I></P>
<P>Click OK to return to the Select Data Source dialog box. Select TYSSQL and click
Use, as shown in Figure 14.11.</P>
<P><A NAME="11"></A><A HREF="11.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/11.htm"><B>Figure 14.11.</B></A></P>
<P><I>Select Data Source dialog box.</I></P>
<P>Again, small database users aren't accustomed to logging on. Nevertheless, type
your password to move through the screen.</P>
<P>The Add Tables dialog box, shown in Figure 14.12, presents the tables associated
with the database to which you are connected. Select <TT>PART</TT>, <TT>ORDERS</TT>,
and <TT>CUSTOMER</TT>, and click Close.</P>
<P><A NAME="12"></A><A HREF="12.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/12.htm"><B>Figure 14.12.</B></A></P>
<P><I>Selecting tables in Query.</I></P>
<P>Your screen should look like Figure 14.13. Double-click <TT>ADDRESS</TT> and <TT>NAME</TT>
from the <TT>CUSTOMER</TT> table. Then double-click <TT>ORDEREDON</TT> and <TT>PARTNUM</TT>
from <TT>ORDERS</TT>.</P>
<P><A NAME="13"></A><A HREF="13.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/13.htm"><B>Figure 14.13.</B></A></P>
<P><I>Visual representation of a table in Query.</I></P>
<P>Now for some magic! Click the button marked SQL in the toolbar. Your screen should
now look like Figure 14.14.</P>
<P><A NAME="14"></A><A HREF="14.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/14.htm"><B>Figure 14.14.</B></A></P>
<P><I>The query that Query built.</I></P>
<P>This tool has two functions. The first is to check the ODBC connection. If it
works here, it should work in the program. This step can help you determine whether
a problem is in the database or in the program. The second use is to generate and
check queries. Add the following line to the SQL box and click OK:</P>
<PRE><FONT COLOR="#0066FF">WHERE CUSTOMER.NAME = ORDERS.NAME AND PART.PARTNUM = ORDERS.PARTNUM
</FONT></PRE>
<P>Figure 14.15 shows the remarkable result.</P>
<P><A NAME="15"></A><A HREF="15.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/15.htm"><B>Figure 14.15.</B></A></P>
<P><I>Query's graphic representation of a join.</I></P>
<P>You have just performed a join! Not only that, but the fields you joined on have
been graphically connected in the table diagrams (note the zigzag lines between <TT>NAME</TT>
and <TT>PARTNUM</TT>).</P>
<P>Query is an important tool to have in your SQL arsenal on the Windows software
platform. It enables you examine and manipulate tables and queries. You can also
use it to create tables and manipulate data. If you work in Windows with ODBC and
SQL, either buy this tool yourself or have your company or client buy it for you.
It is not as interesting as a network version of DOOM, but it will save you time
and money. Now that you have established an ODBC link, you can use it in a program.
<H2><FONT COLOR="#000077">Using Visual C++ and SQL</FONT></H2>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The source code for this example
is located in Appendix B, "Source Code Listings for the C++ Program Used on
Day 14."
<HR>
</BLOCKQUOTE>
<P>Call up Visual C++ and select AppWizard, as shown in Figure 14.16. The name and
subdirectory for your project do not have to be identical.</P>
<P><A NAME="16"></A><A HREF="16.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/16.htm"><B>Figure 14.16.</B></A></P>
<P><I>Initial project setup.</I></P>
<P>Click the Options button and fill out the screen as shown in Figure 14.17.</P>
<P><A NAME="17"></A><A HREF="17.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/17.htm"><B>Figure 14.17.</B></A></P>
<P><I>The Options dialog box.</I></P>
<P>Click OK and then choose Database Options. Select Database Support, No File Support
as shown in Figure 14.18.</P>
<P><A NAME="18"></A><A HREF="18.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/18.htm"><B>Figure 14.18.</B></A></P>
<P><I>The Database Options dialog box.</I></P>
<P>Click the Data Source button and make the choices shown in Figure 14.19.</P>
<P><A NAME="19"></A><A HREF="19.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/19.htm"><B>Figure 14.19.</B></A></P>
<P><I>Selecting a data source.</I></P>
<P>Then select the <TT>CUSTOMER</TT> table from the Select a Table dialog box, shown
in Figure 14.20.</P>
<P><A NAME="20"></A><A HREF="20.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/20.htm"><B>Figure 14.20.</B></A></P>
<P><I>Selecting a table.</I></P>
<P>Now you have selected the <TT>CUSTOMER</TT> table from the TYSSQL database. Go
back to the AppWizard basic screen by clicking OK twice. Then click OK again to display
the new application information (see Figure 14.21), showing the specifications of
a new skeleton application.</P>
<P><A NAME="21"></A><A HREF="21.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/21.htm"><B>Figure 14.21.</B></A></P>
<P><I>AppWizard's new application information.</I></P>
<P>After the program is generated, you need to use the resource editor to design
your main screen. Select Tools | App Studio to launch App Studio. The form you design
will be simple--just enough to show some of the columns in your table as you scroll
through the rows. Your finished form should look something like Figure 14.22.</P>
<P><A NAME="22"></A><A HREF="22.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/22.htm"><B>Figure 14.22.</B></A></P>
<P><I>Finished form in App Studio.</I></P>
<P>For simplicity we named the edit boxes <TT>IDC_NAME</TT>,<TT> IDC_ADDRESS</TT>,
<TT>IDC_STATE</TT>, and<TT> IDC_ZIP</TT>, although you can name them whatever you
choose. Press Ctrl+W to send the Class Wizard page to the Member Variables and set
the variables according to Figure 14.23.</P>
<P><A NAME="23"></A><A HREF="23.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/23.htm"><B>Figure 14.23.</B></A></P>
<P><I>Adding member variables in Class Wizard.</I></P>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The program was nice enough to provide
links to the table to which you are connected. Links are one of the benefits of working
through Microsoft's wizards or Borland's experts.
<HR>
</BLOCKQUOTE>
<P>Save your work; then press Alt+Tab to return to the compiler and compile the program.
If all went well, your output should look like Figure 14.24. If it doesn't, retrace
your steps and try again.</P>
<P><A NAME="24"></A><A HREF="24.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/24.htm"><B>Figure 14.24.</B></A></P>
<P><I>A clean compile for the test program.</I></P>
<P>Now run your program. It should appear, after that pesky logon screen, and look
like Figure 14.25.</P>
<P><A NAME="25"></A><A HREF="25.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/25.htm"><B>Figure 14.25.</B></A></P>
<P><I>The test program.</I></P>
<P>An impressive program, considering that you have written zero lines of code so
far. Use the arrow keys on the toolbar to move back and forth in the database. Notice
that the order of the data is the same as its input order. It is not alphabetical
(unless you typed it in that way). How can you change the order?</P>
<P>Your connection to the database is encapsulated in a class called <TT>Ctyssqlset</TT>,
which the AppWizard created for you. Look at the header file (<TT>tyssqset.h</TT>):</P>
<PRE><FONT COLOR="#0066FF">// tyssqset.h : interface of the CTyssqlSet class
//
////////////////////////////////////////////////////////////////////////
class CTyssqlSet : public CRecordset
{
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -