ch10.htm

好书《C++ Builder高级编程技术》

</FONT></PRE>
<P>The important point to understand is that this one-to-many example works simply
because BCB supports parameterized variables. There is no other hand-waving going
on in the 
background. All that's happening is that you're using a basic SQL statement
to view the members of the <TT>ORDERS</TT> table that happen to have a particular
customer number. The customer number in question was passed to you through the 
<TT>DataSource</TT>
property and the bind variable you created.</P>
<P>The examples you've seen so far in this chapter should give you some feeling for
the extreme power and flexibility inherent in the <TT>TQuery</TT> object. If you're
looking for a 
lever powerful enough to move the roadblocks in your client/server
programming world, <TT>TQuery</TT> is likely to be the tool you require.</P>
<P>In the next section, you'll learn more about the <TT>TQuery</TT> object when you
see how to join two 
tables together so that you can view them both in a single dataset.
<H3><A NAME="Heading19"></A><FONT COLOR="#000077">Performing Joins Between Multiple
Tables</FONT></H3>
<P>You've seen that the <TT>CUSTOMERS</TT> and <TT>ORDERS</TT> tables are 
related
in a one-to-many relationship based on the <TT>CustNo</TT> field. The <TT>ORDERS</TT>
table and <TT>ITEMS</TT> tables are also bound in a one-to-many relationship, only
this time the field that connects them is called <TT>OrderNo</TT>.</P>

<P>More specifically, each order that exists in the <TT>ORDERS</TT> table will have
one or more records from the <TT>ITEMS</TT> table associated with it. The records
from the <TT>ITEMS</TT> table specify characteristics, such as price and part number,

of the items associated with a particular sale.</P>
<P>Consider what happens when you go to a restaurant and order steamed shrimp, steamed
artichoke, Caesar salad, and mineral water. The result of this pleasurable exercise
is that you've made one 
order that has four different line items associated with
it:</P>
<PRE><FONT COLOR="#0066FF">ORDERS1: Suzie Customer (Oct 1, 1994):

  ITEMS1: Shrimp          $12.95

  ITEMS2: Artichoke        $6.25

  ITEMS3: Caesar salad     $3.25

  ITEMS4: Mineral 
water    $2.50

</FONT></PRE>
<P>In a situation like this, it's sometimes simplest to join the data from the <TT>ORDERS</TT>
table and the <TT>ITEMS</TT> table, so that the resulting dataset contains information
from both tables:</P>
<PRE><FONT 
COLOR="#0066FF">Suzie     Oct 1, 1994    Shrimp          $12.95

Suzie     Oct 1, 1994    Artichoke        $6.25

etc...

</FONT></PRE>
<P>The act of merging these two tables is called a join, and it is one of the fundamental
operations you can 
perform on a set of two or more tables.</P>
<P>Given the <TT>ORDERS</TT> and <TT>ITEMS</TT> tables from the demos subdirectory,
you can join them in such a way that the <TT>CustNo</TT>, <TT>OrderNo</TT>, and <TT>SaleDate</TT>
fields from the 
<TT>ORDERS</TT> table are merged with the <TT>StockNo</TT>, <TT>Price</TT>,
and <TT>Qty</TT> fields from the <TT>ITEMS</TT> table to form a new dataset containing
all six fields. A grid containing the resulting dataset is shown in Figure 10.6.<BR>

<BR>
<A NAME="Heading20"></A><A HREF="10ebu06.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/10/10ebu06.jpg">FIGURE 10.6.</A><FONT COLOR="#000077">
</FONT><I>The QJOIN program joins the <TT>ORDERS</TT> and <TT>ITEMS</TT> table producing
a dataset with fields from each table.</I></P>
<P>There's a 
substantial difference between linking cursors and joining tables. However,
they both have two things in common:

<UL>
	<LI>They involve two or more tables.
	<P>
	<LI>Each table is linked to the other by one or more shared fields.
</UL>

<P>The act of 
joining the <TT>ORDERS</TT> and <TT>ITEMS</TT> tables can be accomplished
by a single SQL statement that looks like this:</P>
<PRE><FONT COLOR="#0066FF">select

  O.&quot;OrderNo&quot;, O.&quot;CustNo&quot;,

  O.&quot;SaleDate&quot;, 
O.&quot;ShipDate&quot;,

  I.&quot;PartNo &quot;, I.&quot;Qty&quot;,  I.&quot;Discount &quot;

from

  Orders O, Items I

where

  O.OrderNo = I.OrderNo

</FONT></PRE>
<P>This statement consists of four parts:

<UL>
	<LI>The <TT>select</TT> statement 
specifies that you expect a cursor to be returned
	containing some form of dataset.
	<P>
	<LI>Next, there is a list of the fields that you want included in the dataset you
	are requesting. This list includes the <TT>OrderNo</TT>, <TT>CustNo</TT>, 
<TT>SaleDate</TT>,
	<TT>ShipDate</TT>, <TT>PartNo</TT>, <TT>Qty</TT>, and <TT>Discount</TT> fields. The
	first four fields originate in the <TT>ORDERS</TT> table, and the next three fields
	originate in the <TT>ITEMS</TT> table.
	<P>
	<LI>The 
<TT>from</TT> clause states that you're working with two tables, one called
	<TT>ORDERS</TT> and the other called <TT>ITEMS</TT>. For the sake of brevity, the
	statement uses an optional SQL feature that lets you specify the <TT>ORDERS</TT>
	table 
with the letter <TT>O</TT>, and the <TT>ITEMS</TT> table with the letter <TT>I</TT>.
	<P>
	<LI>The <TT>where</TT> clause is vitally important, because it specifies which field
	will link the two tables. Some servers are capable of returning valid 
datasets even
	if you don't include a <TT>where</TT> clause in your join, but the resulting set
	of records will almost surely not be what you want. To get the results you're looking
	for, be sure to include a <TT>where</TT> clause.
</UL>

<P>When 
you've created the SQL statement that you want to use, there is nothing at
all difficult about performing a join. The <TT>QJOIN</TT> example that ships with
BCB demonstrates exactly how to proceed. All you need do is drop a <TT>TQuery</TT>,

<TT>TDataSource</TT>, and <TT>TDBGrid</TT> onto a form and then wire them up in the
standard way. When you're hooked up, you can paste the query statement in the <TT>SQL</TT>
property of the query, fill in the <TT>DatabaseName</TT> property, and then 
set <TT>Active</TT>
to <TT>True</TT>. Now, compile and run the program and take a moment to scroll through
the new dataset you've created from the raw materials in the <TT>ORDERS</TT> and
<TT>ITEMS</TT> tables.

<DL>
	<DT></DT>
</DL>



<BLOCKQUOTE>
	
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>When you are composing SQL statements
	in the SQL field of the <TT>TQuery</TT> object, you may find that the space you are
	working in is a little cramped. To open up your horizons, click the 
Code Editor button
	in the String List Editor dialog. Your code will then be transferred from the String
	List Editor to BCB's main editor. The main editor gives you more room to work and
	provides syntax highlighting for your SQL statements. 
<HR>



</BLOCKQUOTE>

<P>There is not much point to showing you the actual source code for the <TT>QJOIN</TT>
program, because all the magic occurs in the SQL statement quoted previously.
<H3><A NAME="Heading22"></A><FONT COLOR="#000077">The RequestLive 
Property</FONT></H3>
<P>The <TT>RequestLive</TT> field of the <TT>TQuery</TT> object can play an important
role in SQL programming. By default, any query you make with the <TT>TQuery</TT>
object will return a read-only dataset. However, you can 
attempt to get a live query
by setting the <TT>TQuery</TT> <TT>RequestLive</TT> property to <TT>True</TT>. As
a rule, if your query involves only one table, then you can set <TT>RequestLive</TT>
to <TT>True</TT>. If your query involves multiple 
tables, setting <TT>RequestLive</TT>
to <TT>True</TT> might not produce the desired result. You can check the <TT>CanModify</TT>
property to see if your request has succeeded.</P>
<P>In general, I use the <TT>TTable</TT> object rather than the 
<TT>TQuery</TT> object
when I want to edit the results of a direct link between one or more tables. This
has some limitations, but it is the simplest way to proceed in some cases. If you
want to let the user edit tables at will, then you should use 
the <TT>TTable</TT>
object. Of course, there are some things you can't do with <TT>TTable</TT> objects,
such as produce a true join.</P>
<P>If you want to update a table with a SQL query, then you should use the SQL <TT>Update</TT>
or <TT>Insert</TT> 
commands. That's the way SQL is supposed to work. It's a conservative
language. (<TT>Update</TT>, <TT>Insert</TT>, <TT>Delete</TT>, and other SQL statements
will be discussed later in this chapter.)</P>
<P>There is also an <TT>UpdateSQL</TT> component 
that can be useful in these circumstances,
but I often find it simplest to place one or more <TT>TQuery</TT> objects on a form
or data module, and then use them to issue statements that will update a table. In
particular, if you have created a join 
between three tables, you might not be able
to set <TT>RequestLive</TT> to <TT>True</TT>. If that is the case, then you will
have to pop up a separate dialog with a series of simple <TT>TEdit</TT> controls
in it. Use this dialog to get input from the 
user, and then simply use the <TT>TQuery</TT>
component to issue three <TT>Update</TT> commands, one for each table in your join.
When you are done, <TT>Refresh</TT> your join. This is a good system, with a natural,
intuitive rhythm that's easy to 
follow. Furthermore, it helps prevent anyone from
accidentally editing a live dataset when he or she only means to be scrolling around
in it.</P>
<P>Whatever limitations the <TT>RequestLive</TT> property may have are not unique
to BCB. If you want to 
edit tables quickly with a high-performance system, use the
<TT>TTable</TT> object. Of course, you can try to use the <TT>TQuery</TT> object
first, and see how these requests are handled with your particular server. Your ability
to set 
<TT>RequestLive</TT> to <TT>True</TT> is somewhat server-dependent. If you
can't set <TT>RequestLive</TT> to <TT>True</TT>, and you don't want to use <TT>TTable</TT>,
just start writing some SQL statements to perform the update for you. Part of the

purpose of this chapter is to outline enough about <TT>TQuery</TT> and SQL so that
you will know how to write these kinds of statements by the time you finish this
chapter.
<H3><A NAME="Heading23"></A><FONT COLOR="#000077">Parameterized Queries and 
join
Statements</FONT></H3>
<P>You can mix parameterized queries and <TT>join</TT> statements. This is useful
if you want to show the <TT>CUSTOMER</TT> table at the top of a form, and then beneath
it, show another dataset that contains records with 
information from both the <TT>ORDERS</TT>
and <TT>ITEMS</TT> table. The result is a program that enables you to iterate through
a list of customers in the top half of a form, while the bottom half of the form
shows only the purchases associated with 
any particular customer, including a list
of the line items that were bought. This is the type of form you'd produce if you
wanted to create an electronic invoice.</P>
<P>The QJOIN2 program on your system shows how a program of this type looks in 
practice.
The main form for the QJOIN2 program is shown in Figure 10.7.<BR>
<BR>
<A NAME="Heading24"></A><A HREF="10ebu07.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/10/10ebu07.jpg">FIGURE 10.7.</A><FONT COLOR="#000077">
</FONT><I>The QJOIN2 program shows three tables linked together in a logical 
and
coherent fashion.</I></P>
<P>To create this program, drop down a <TT>TTable</TT>, a <TT>TQuery</TT>, two data
sources, and two data grids. Hook up the <TT>TTable</TT>, the first data source,
and the first grid to the <TT>CUSTOMER</TT> table. Wire 
up the remaining controls
and specify <TT>DataSource1</TT> in the <TT>Query1.DataSource</TT> property. Now
add the following SQL statement in the <TT>Query1.SQL</TT> property:</P>
<PRE><FONT COLOR="#0066FF">select

  O.CustNo, O.OrderNo, O.SaleDate,

  
L.PartNo, L.Discount, L.Qty

from

  Orders O, Items L

where

  O.CustNo = :CustNo and

  O.OrderNo = L.OrderNo

</FONT></PRE>
<P>The statement pictured here is very much like the one you saw in the last section,
except that the <TT>where</TT> clause 
has been expanded to include a bind variable:</P>
<PRE><FONT COLOR="#0066FF">where

  O.CustNo = :CustNo and

  O.OrderNo = L.OrderNo

</FONT></PRE>
<P>This clause now specifies two different relationships: one between the <TT>CUSTOMER</TT>
table and 
the <TT>ORDERS</TT> table, and the second between the <TT>ORDERS</TT> table
and the <TT>ITEMS</TT> table. More specifically, the value for the <TT>CustNo</TT>
variable will be supplied by the current record of the <TT>CUSTOMER</TT> table through
the 
link on the <TT>Query1.DataSource</TT> property. The link between the <TT>ORDERS</TT>
table and <TT>ITEMS</TT> table will be the <TT>OrderNo</TT> field.</P>
<P>Conceptually, the QJOIN2 program forces you to wrestle with some fairly complex
ideas. This 
complexity is inherent in the task being performed. BCB, however, enables
you to encapsulate these complex ideas in a few simple mechanical steps. In short,
once you understand the goal you want to achieve, BCB enables you to perform even
complex data 
operations with just a few minutes of work.
<H3><A NAME="Heading25"></A><FONT COLOR="#000077">ExecSQL and the Delete and Insert
Statements</FONT></H3>
<P>After you've composed a SQL statement, there are two different ways to process
it. If you need to 
get a cursor back from the <TT>Query</TT>, you should always call
<TT>Open</TT>. If you don't need to return a cursor, you should call <TT>ExecSQL</TT>.
For instance, if you're inserting, deleting, or updating data, you should call <TT>ExecSQL</TT>.

To state the same matter in slightly different terms, you should use <TT>Open</TT>
whenever you compose a <TT>select</TT> statement, and you should use <TT>ExecSQL</TT>
whenever you write any other kind of statement.</P>
<P>Here's a typical SQL 
statement that you might use to delete a record from a table:</P>
<PRE><FONT COLOR="#0066FF">delete from Country where Name = `Argentina';

</FONT></PRE>
<P>This statement deletes any record from the <TT>COUNTRY</TT> database that has

<TT>Argentina</TT> in the <TT>Name</TT> field.</P>
<P>It doesn't take long to see that this is a case in which you might want to use
a parameterized query. For instance, it would be nice to be able to vary the name
of the country you want to 
delete:</P>
<PRE><FONT COLOR="#0066FF">delete from Country where Name = :CountryName

</FONT></PRE>
<P>In this case, <TT>CountryName</TT> is a variable that can be changed at runtime
by writing code that looks like this:</P>
<PRE><FONT 
COLOR="#0066FF">Query2-&gt;Prepare;

Query2-&gt;Params-&gt;Items[0]-&gt;AsString = &quot;Argentina&quot;;

Query2-&gt;ExecSQL;

Query1-&gt;Refresh;

</FONT></PRE>
<P>The code shown here first calls <TT>Prepare</TT> to inform BCB that it should
pars