ch14.htm

Learn SQL in an easy way...

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"><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, &quot;Source Code Listings for the C++ Program Used on
	Day 14.&quot; 
<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"><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"><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"><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"><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"><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"><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"><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"><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"><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"><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
{
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"><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"><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"><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"><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"><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"><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"><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"><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"><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"><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"><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"><B>Figure 14.37.</B></A></P>
<P><I>Selecting all the fields.</I></P>
<P><A NAME="38"></A><A HREF="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"><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"><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"><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"><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/ch13.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../wk2rev/wk2rev.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>