ch14.htm

21精通SQL

DECLARE_DYNAMIC(CTyssqlSet)
public:
CTyssqlSet(CDatabase* pDatabase = NULL);
// Field/Param Data
//{{AFX_FIELD(CTyssqlSet, CRecordset)
Cstring    m_NAME;
Cstring    m_ADDRESS;
Cstring    m_STATE;
Cstring    m_ZIP;
Cstring    m_PHONE;
Cstring    m_REMARKS;
//}}AFX_FIELD
// Implementation
protected:
virtual CString GetDefaultConnect();// Default connection string
virtual CString GetDefaultSQL();// default SQL for Recordset
virtual void DoFieldExchange(CFieldExchange* pFX);// RFX support
};
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Note that member variables have been constructed for all the columns in the table.
Also notice the functions <TT>GetDefaultConnect</TT> and <TT>GetDefaultSQL</TT>;
here's their implementations from <TT>tyssqset.cpp</TT>:</P>
<PRE><FONT COLOR="#0066FF">CString CTyssqlSet::GetDefaultConnect()
{
return ODBC;DSN=TYSSQL;&quot;;
}
CString CTyssqlSet::GetDefaultSQL()
{
return &quot;CUSTOMER&quot;;
}
</FONT></PRE>
<P><TT>GetDefaultConnect</TT> makes the ODBC connection. You shouldn't change it.
However, <TT>GetDefaultSQL</TT> enables you to do some interesting things. Change
it to this:</P>
<PRE><FONT COLOR="#0066FF">return &quot;SELECT * FROM CUSTOMER ORDER BY NAME&quot;;
</FONT></PRE>
<P>Recompile, and magically your table is sorted by name, as shown in Figure 14.26.</P>
<P><A NAME="26"></A><A HREF="26.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/26.htm"><B>Figure 14.26.</B></A></P>
<P><I>Database order changed by SQL.</I></P>
<P>Without going into a tutorial on the Microsoft Foundation Class, let us just say
that you can manipulate <TT>CRecordSet</TT> and <TT>Cdatabase</TT> objects, join
and drop tables, update and insert rows, and generally have all the fun possible
in SQL. You have looked as far over the edge as you can, and we have pointed the
way to integrate SQL into C++ applications. Topics suggested for further study are
<TT>CRecordSet</TT> and <TT>Cdatabase</TT> (both in the C++ books online that should
come as part of the C++ software), ODBC API (the subject of several books), and the
APIs provided by Oracle and Sybase (which are both similar to the ODBC API).
<H2><FONT COLOR="#000077">Using Delphi and SQL</FONT></H2>
<P>Another important database tool on the Windows software platform is Delphi. The
splash that comes up as the program is loading has a picture of the Oracle at Delphi,
surrounded by the letters SQL. In the C++ example you rewrote one line of code. Using
Delphi, you will join two tables without writing a single line of code!


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The code for this program is located
	in Appendix C, &quot;Source Code Listings for the Delphi Program Used on Day 14.&quot;
	
<HR>


</BLOCKQUOTE>

<P>Double-click Delphi's icon to get it started. At rest the program looks like Figure
14.27.</P>
<P><A NAME="27"></A><A HREF="27.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/27.htm"><B>Figure 14.27.</B></A></P>
<P><I>The Delphi programming environment.</I></P>
<P>Delphi requires you to register any ODBC connections you are going to use in your
programming. Select BDE (Borland Database Environment) from the Tools menu and then
fill out the dialog box shown in Figure 14.28.</P>
<P><A NAME="28"></A><A HREF="28.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/28.htm"><B>Figure 14.28.</B></A></P>
<P><I>Registering your connections.</I></P>
<P>Click the Aliases tab shown at the bottom of Figure 14.28 and assign the name
TYSSQL, as shown in Figure 14.29.</P>
<P><A NAME="29"></A><A HREF="29.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/29.htm"><B>Figure 14.29.</B></A></P>
<P><I>Adding a new alias.</I></P>
<P>Select File | New Form to make the following selections. Start by choosing the
Database Form from the Experts tab, as shown in Figure 14.30.</P>
<P><A NAME="30"></A><A HREF="30.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/30.htm"><B>Figure 14.30.</B></A></P>
<P><I>The Experts page in the Browse gallery.</I></P>
<P>Then choose the master/detail form and <TT>TQuery</TT> objects, as shown in Figure
14.31.</P>
<P><A NAME="31"></A><A HREF="31.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/31.htm"><B>Figure 14.31.</B></A></P>
<P><I>The Database Form Expert dialog box.</I></P>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Delphi enables you to work with
	either a query or a table. If you need flexibility, we recommend the <TT>TQuery</TT>
	object. If you need the whole table without modification, use the <TT>TTable</TT>
	object. 
<HR>


</BLOCKQUOTE>

<P>Now select the TYSSQL data source you set up earlier, as shown in Figure 14.32.</P>
<P><A NAME="32"></A><A HREF="32.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/32.htm"><B>Figure 14.32.</B></A></P>
<P><I>Choosing a data source.</I></P>
<P>Choose the <TT>PART</TT> table as the master, as shown in Figure 14.33.</P>
<P><A NAME="33"></A><A HREF="33.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/33.htm"><B>Figure 14.33.</B></A></P>
<P><I>Choosing a table.</I></P>
<P>Choose all its fields, as shown in Figure 14.34.</P>
<P><A NAME="34"></A><A HREF="34.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/34.htm"><B>Figure 14.34.</B></A></P>
<P><I>Adding all the fields.</I></P>
<P>Pick the Horizontal display mode, as shown in Figure 14.35.</P>
<P><A NAME="35"></A><A HREF="35.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/35.htm"><B>Figure 14.35.</B></A></P>
<P><I>Display mode selection.</I></P>
<P>Then choose <TT>ORDERS</TT>, select all its fields, and select Grid for its display
mode, as shown in Figures 14.36, 14.37, and 14.38.</P>
<P><A NAME="36"></A><A HREF="36.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/36.htm"><B>Figure 14.36.</B></A></P>
<P><I>Choosing the table for the detail part of the form.</I></P>
<P><A NAME="37"></A><A HREF="37.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/37.htm"><B>Figure 14.37.</B></A></P>
<P><I>Selecting all the fields.</I></P>
<P><A NAME="38"></A><A HREF="38.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/38.htm"><B>Figure 14.38.</B></A></P>
<P><I>Selecting the orientation.</I></P>
<P>Now the software enables you to make a join. Make the join on <TT>PARTNUM</TT>,
as shown in Figure 14.39.</P>
<P><A NAME="39"></A><A HREF="39.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/39.htm"><B>Figure 14.39.</B></A></P>
<P><I>Making the join.</I></P>
<P>Now go ahead and generate the form. The result looks like Figure 14.40.</P>
<P><A NAME="40"></A><A HREF="40.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/40.htm"><B>Figure 14.40.</B></A></P>
<P><I>The finished form.</I></P>
<P>Compile and run the program. As you select different parts, the order for them
should appear in the lower table, as shown in Figure 14.41.</P>
<P><A NAME="41"></A><A HREF="41.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/41.htm"><B>Figure 14.41.</B></A></P>
<P><I>The finished program.</I></P>
<P>Close the project and click one or both of the query objects on the form. When
you click an object, the Object Inspector to the left of the screen in Figure 14.42
shows the various properties.</P>
<P><A NAME="42"></A><A HREF="42.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/42.htm"><B>Figure 14.42.</B></A></P>
<P><I>The query in the TQuery object.</I></P>
<P>Try experimenting with the query to see what happens. Just think what you can
do when you start writing code!
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>Today you learned where to start applying SQL using the ordinary, everyday stuff
you find lying on your hard drive. The best way to build on what you have learned
is to go out and query. Query as much as you can.
<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2>
<P><B>Q What is the difference between the ODBC API and the Oracle and Sybase APIs?</B></P>
<P><B>A</B> On a function-by-function level, Oracle and Sybase are remarkably similar,
which is not a coincidence. Multiple corporate teamings and divorces have led to
libraries that were derived from somewhat of a common base. ODBC's API is more generic--it
isn't specific to any database. If you need to do something specific to a database
or tune the performance of a specific database, you might consider using that database's
API library in your code.</P>
<P><B>Q With all the available products, how do I know what to use?</B></P>
<P><B>A</B> In a business environment, product selection is usually a compromise
between management and &quot;techies.&quot; Management looks at the cost of a product;
techies will look at the features and how the product can make their lives easier.
In the best of all programming worlds, that compromise will get your job done quickly
and efficiently.
<H2><FONT COLOR="#000077">Workshop</FONT></H2>
<P>The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, &quot;Answers to Quizzes and Exercises.&quot;
<H3><FONT COLOR="#000077">Quiz</FONT></H3>

<DL>
	<DD><B>1.</B> In which object does Microsoft Visual C++ place its SQL?
	<P><B>2. </B>In which object does Delphi place its SQL?</P>
	<P><B>3.</B> What is ODBC?</P>
	<P><B>4.</B> What does Delphi do?
</DL>

<H3><FONT COLOR="#000077">Exercises</FONT></H3>

<DL>
	<DD><B>1.</B> Change the sort order in the C++ example from ascending to descending
	on the <TT>State</TT> field.
	<P><B>2.</B> Go out, find an application that needs SQL, and use it.
</DL>

<H1></H1>
<CENTER>
<P>
<HR>
<A HREF="ch13.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch13.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="wk2rev.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/wk2rev.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR>
<BR>
<BR>
<IMG SRC="corp.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="copy.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>