ch10.htm

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

	of a string list, so I include both techniques in this chapter. 
<HR>



</BLOCKQUOTE>

<P>In this section, you have seen two methods of changing the <TT>SQL</TT> property
at runtime. The first technique enables you to add strings to the <TT>SQL</TT> property,
run a query, change the strings, and run the query again. The 
second technique enables
you to load one or more statements from a file. The <TT>LoadFromFile</TT> technique
is obviously quite elegant. The first technique can be very powerful at times, but
it can be a bit awkward if all you want to do is change one 
word in a SQL statement.
In the next section, you'll learn about how you can eliminate this awkwardness by
using parameterized queries.
<H3><A NAME="Heading7"></A><FONT COLOR="#000077">TQuery and Parameters</FONT></H3>
<P>BCB enables you to compose a 
flexible form of query statement called a parameterized
query. A parameterized query enables you to substitute variables for single words
in the <TT>where</TT> or <TT>insert</TT> clause of a SQL statement. These variables
can then be changed at any 
time throughout the life of the query. (If you're using
local SQL, you'll be able to make substitutions on almost any word in a SQL statement,
but this same capability is not included on most servers.)</P>
<P>To get started using parameterized 
queries, consider again one of the simple SQL
statements listed earlier:</P>
<PRE><FONT COLOR="#0066FF">Select * from Country where Name like `C%'

</FONT></PRE>
<P>To turn this statement into a parameterized query, just replace the right side
of the 
<TT>like</TT> clause with a variable called <TT>NameStr</TT>:</P>
<PRE><FONT COLOR="#0066FF">select * from County where Name like :NameStr

</FONT></PRE>
<P>In this SQL statement, <TT>NameStr</TT> is no longer a predefined constant, but
instead can 
change at either design time or runtime. The SQL parser knows that it
is dealing with a parameter instead of a constant because a colon is prepended to
the word <TT>NameStr</TT>. That colon tells BCB that it should substitute the <TT>NameStr</TT>

variable with a value that will be supplied at some future point.</P>
<P>It's important to note that the word <TT>NameStr</TT> was chosen entirely at random.
You can use any valid variable name in this case, just as you can choose a wide range
of 
identifiers when you declare a string variable in one of your programs.</P>
<P>There are two ways to supply variables to a parameterized SQL statement. One method
is to use the <TT>Params</TT> property of <TT>TQuery</TT> to supply the value at

runtime. The second is to use the <TT>DataSource</TT> property to supply information
from another dataset at either runtime or design time. Here are the key properties
used to accomplish these goals:</P>
<PRE><FONT COLOR="#0066FF">__property TParams 
*Params;

TParam *__fastcall ParamByName(const System::AnsiString Value);

void __fastcall Prepare(void);

</FONT></PRE>
<P>Both <TT>TParam</TT> and <TT>TParams</TT> are objects found in <TT>DBTABLES.HPP</TT>.
It is not particularly important for you 
to understand how those objects work.</P>
<P>When you substitute bind variables in a parameterized query by using the <TT>Params</TT>
property, you usually take four steps:

<DL>
	<DD><B>1.</B> Make sure the table is closed.<BR>
	<BR>
	<B>2.</B> Ready 
the <TT>Query</TT> object by issuing the <TT>Prepare</TT> command
	(optional, but highly recommended).<BR>
	<BR>
	<B>3.</B> Assign the correct values to the <TT>Params</TT> property.<BR>
	<BR>
	<B>4.</B> Open the query.
</DL>

<P>Here's a sample code 
fragment showing how this might be done in practice:</P>
<PRE><FONT COLOR="#0066FF">void TDMod::NewParameterizedQuery(AnsiString S)

{

  CountryQuery-&gt;Close();

  CountryQuery-&gt;Prepare();

  
CountryQuery-&gt;ParamByName(&quot;NameStr&quot;)-&gt;AsString = S;

  CountryQuery-&gt;Open();

}

</FONT></PRE>
<P>If you're not familiar with parameterized queries, the preceding code might appear
a bit mysterious. To understand it thoroughly, 
you'll need to do a careful line-by-line
analysis. The simplest way to begin is with the third line, because it is the <TT>Params</TT>
property that lies at the heart of this process.</P>
<P><TT>Params</TT> is an indexed property that uses a syntax 
similar to the <TT>Fields</TT>
property from <TT>TDataSet</TT>. For instance, you can access the first bind variable
in a SQL statement by referring to element <TT>0</TT> in the <TT>Params</TT> array:</P>
<PRE><FONT 
COLOR="#0066FF">CountryQuery-&gt;Params-&gt;Items[0]-&gt;AsString := S;

</FONT></PRE>
<P>Or, if you prefer, you can use <TT>ParamByName</TT> instead:</P>
<PRE><FONT COLOR="#0066FF">CountryQuery-&gt;ParamByName(&quot;NameStr&quot;)-&gt;AsString = S;


</FONT></PRE>
<P>There is a classic trade-off here, in that <TT>Params-&gt;Items</TT> usually executes
somewhat faster than <TT>ParamByName</TT>, because there is no string handling involved
in tracking down the referenced parameter. However, 
<TT>ParamByName</TT> is safer,
because your code would not break simply because the order of the fields was changed.</P>
<P>If you combine a simple parameterized SQL statement such as this</P>
<PRE><FONT COLOR="#0066FF">select * from Country where 
Name like :NameStr

</FONT></PRE>
<P>with the <TT>Params</TT> statements shown previously, the result is the following
SQL statement:</P>
<PRE><FONT COLOR="#0066FF">select * from Country where Name like `Argentina'

</FONT></PRE>
<P>What's happened 
here is that the variable <TT>:NameStr</TT> has been assigned
the value <TT>Argentina</TT> by the <TT>Params</TT> property, thereby enabling you
to complete a simple SQL statement.</P>
<P>If you have more than one parameter in a statement, you can 
access them by changing
the index of the <TT>Params</TT> property:</P>
<PRE><FONT COLOR="#0066FF">Params-&gt;Items[1]-&gt;AsString = &quot;SomeValue&quot;;

</FONT></PRE>
<P>So far, you've seen that a parameterized query uses bind variables, which 
always
begin with a colon, to designate the places where parameters will be passed. With
this concept in mind, you can move on to the other lines in the previous code fragment.</P>
<P>Before you use the <TT>Params</TT> variable, you should first call 
<TT>Prepare</TT>.
A call to <TT>Prepare</TT> causes BCB to parse your SQL statement and ready the <TT>Params</TT>
property so that it's prepared to accept the appropriate number of variables. This
is particularly important if you are about to enter a 
loop where the same <TT>Query</TT>
will be executed over and over. If you try to assign a value to the <TT>Params</TT>
variable without first calling <TT>Prepare</TT>, your code will still work, but the
routine may not be as highly optimized. The 
issue here is that in a loop, BCB will
have to call internally at each iteration, rather than having it called once by the
programmer before the loop begins. There is also an <TT>UnPrepare</TT> statement
that you should use if you are very concerned 
about taking up database resources.</P>
<P>After you've called <TT>Prepare</TT> and assigned the correct values to the <TT>Params</TT>
variable, you should call <TT>Open</TT> to complete the binding of the variables
and produce the dataset that you 
hope to find. In this particular case, given the
input shown previously, the dataset includes the contents of the record where the
name field is set to <TT>Argentina</TT>.</P>
<P>In the <TT>Examples</TT> subdirectory, you'll find a program called 
EASYSQL2 that
demonstrates how to use parameterized queries. The EASYSQL2 program performs a function
very similar to the one shown earlier in the first EASYSQL program. However, this
new version shows how parameterized queries can be used to increase 
the flexibility
of a SQL statement.</P>
<P>To create the program, place <TT>TQuery</TT>, <TT>TDataSource</TT>, <TT>TDBGrid</TT>,
and <TT>TTabSet</TT> components on a form, or in a program that uses both a form
and data module. Hook up the data 
controls and set the query's <TT>DatabaseName</TT>
property to the <TT>DBDEMOS</TT> alias. Fill in the tabset so that it lists the alphabet
from A to Z, as shown in Figure 10.3.<BR>
<BR>
<A NAME="Heading8"></A><A HREF="10ebu03.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/10/10ebu03.jpg">FIGURE 
10.3.</A><FONT COLOR="#000077">
</FONT><I>The EASYSQL2 program shows how to use parameterized queries.</I></P>
<P>Enter the following string in the <TT>SQL</TT> property for the query component:</P>
<PRE><FONT COLOR="#0066FF">select * from Country 
where Name like :NameStr

</FONT></PRE>
<P>Now all that's left to create is a response method for the <TT>OnChange</TT> property
of the tabset:</P>
<PRE><FONT COLOR="#0066FF">void __fastcall TForm1::TabSet1Change(TObject *Sender, Integer NewTab,

                                     
Boolean &amp;AllowChange)

{

  AnsiString S(UpperCase(TabSet1-&gt;Tabs-&gt;Strings[NewTab]) + &quot;%&quot;);

  DMod-&gt;NewParameterizedQuery(S);

}

</FONT></PRE>
<P>The <TT>NewParameterizedQuery</TT> method is shown and explained a few paragraphs

back in this same section of the chapter.</P>
<P>The code shown here follows the four simple steps outlined previously. This is
what the code does:

<DL>
	<DD><B>1.</B> Closes the query<BR>
	<BR>
	<B>2.</B> Prepares the <TT>Params</TT> property<BR>
	
<BR>
	<B>3.</B> Assigns a string to the <TT>Params</TT> property<BR>
	<BR>
	<B>4.</B> Executes the resultant SQL statement by calling <TT>Query1.Open</TT>
</DL>

<P>The actual string assigned to the <TT>Params</TT> property consists of one of
the 
letters of the alphabet plus the <TT>%</TT> symbol. A typical query produced
by this method might look like this:</P>
<PRE><FONT COLOR="#0066FF">Select * from Country where Name like `C%'

</FONT></PRE>
<P>The end result, then, is that the EASYSQL2 
program lets you view the contents
of the table in alphabetical sequence. Press the tab labeled A, and you see only
those records in the database for which the first letter of the <TT>Name</TT> field
begins with an A. Press the B tab, and you see only 
those items with a first letter
of B.</P>
<P>The important point, of course, is that you were able to produce the previous
program by writing only six lines of C++ code, plus one line of SQL:</P>
<PRE><FONT COLOR="#0066FF">Select * from Country where 
Name like :NameStr

</FONT></PRE>
<P>This combination of SQL and BCB's native language provides maximum power and flexibility
when you want to produce your own applications.

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



<BLOCKQUOTE>
	<P>
<HR>
<FONT 
COLOR="#000077"><B>NOTE:</B></FONT><B> </B>In the last chapter, I showed you
	how to write this same type of program using the <TT>TTable</TT> object rather than
	the <TT>TQuery</TT> object. The question then becomes, which one is better? <BR>
	<BR>
	
Well, there is no definitive answer to this question. If you come from the client/server
	world and have been writing SQL statements for years, you will almost certainly prefer
	the <TT>TQuery</TT> object. If you come from the C++ world, you will 
probably prefer
	the <TT>TTable</TT> component because you are likely to find SQL awkward to write,
	at least at first. <BR>
	<BR>
	In general, <TT>TTable</TT> is easier to use, and less prone to error, so it is a
	good choice in many cases. I 
certainly use it a great deal in my own programs. <BR>
	<BR>
	If you are running against large SQL databases, you can use <TT>TQuery</TT> to optimize
	your code for maximum performance. There is at least some merit to the idea that
	you can use 
<TT>TTable</TT> when working with local data, but consider using <TT>TQuery</TT>
	if you are running against SQL server data, and particularly if you are working with
	a large SQL database. Most SQL servers were designed around the idea that users 
will
	access records one at a time, or in small groups. The concept of treating an entire
	table as a series of rows--which is the fundamental theory behind the <TT>TTable</TT>
	object--can run against the grain of some servers, particularly when they 
are carrying
	a large load. <BR>
	<BR>
	Finally, I should add that no one can hope to do any serious contemporary database
	development without understanding something about SQL. SQL is a vital part of the
	client/server world, and if you don't 
understand how to use the <TT>TQuery</TT> object,
	your viability as a professional client/server programmer would be seriously, perhaps
	hopelessly, impaired. In general, a good database programmer has to be an expert
	in SQL, just as a good systems 
programmer should be an expert in C++ or Object Pascal.
	
<HR>


</BLOCKQUOTE>

<P>Further examples of parameterized queries are found on the CD-ROM that accompanies
this book as PARAMS2 and PARAMS3. The PARAMS2 program is particularly interesting

because it shows how to work with two parameterized variables at once. In particular,
it makes the following request: &quot;Show me all the records where the <TT>Size</TT>
field is above X, and the <TT>Weight</TT> field is above Y&quot;, where 
<TT>Size</TT>
and <TT>Weight</TT> are fields defining the size and weight of the animals listed
in the table. In other words, it lets you list animals by their size and weight.</P>
<P>To create the PARAMS2 program, drop a query, data source, and 
<TT>DBgrid</TT>
on a form, and place two list boxes and <TT>TDBImage</TT> above the grid, as shown
in Figure 10.4. Use <TT>TLabel</TT> objects to put the word <TT>Size</TT> above the
first list box, and the word <TT>Weight</TT> above the second list 
box. Set the <TT>DataSource</TT>
property of the <TT>TDBImage</TT> control to <TT>DataSource1</TT>, and type the word
<TT>BMP</TT> in the editor for its <TT>DataField</TT> property.<BR>
<BR>
<A NAME="Heading10"></A><A HREF="10ebu04.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/10/10ebu04.jpg">FIGURE 
10.4.</A><FONT COLOR="#000077">
</FONT><I>The form for the PARAMS2 program, as it appears at runtime.</I></P>
<P>The SQL statement used in the PARAMS2 program looks like this:</P>
<PRE><FONT COLOR="#0066FF">select * from Animals

  where

     
Animals.&quot;Size&quot; &gt; :Size and

     Animals.&quot;Weight&quot; &gt; :Weight

</FONT></PRE>
<P>To satisfy the two parameters specified in this SQL statement, you should create
the following method:</P>
<PRE><FONT COLOR="#0066FF">void 
TDMod::RunQuery(int Box1, int Box2)

{