ch10.htm

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

  sqlAnimals->Close();

  sqlAnimals->Prepare();

  sqlAnimals->Params->Items[0]->AsInteger = Box1;

  sqlAnimals->Params->Items[1]->AsInteger = Box2;

  sqlAnimals->Open();

}


</FONT></PRE>
<P>The <TT>OnClick</TT> events for both list boxes should be set to this simple routine,
which calls <TT>TDMod::RunQuery</TT>:</P>
<PRE><FONT COLOR="#0066FF">void __fastcall TForm1::ListBox1Click(TObject *Sender)

{

  
DMod-&gt;RunQuery(ListBox1-&gt;Items-&gt;Strings[ListBox1-&gt;ItemIndex].ToInt(),

                 ListBox1-&gt;Items-&gt;Strings[ListBox2-&gt;ItemIndex].ToInt());

}

</FONT></PRE>
<P>When you run the PARAMS2 program, both list boxes are 
automatically filled with
numbers that range from 0 to 42. By selecting a value from the first list box, you
specify the size of the animal you want to find. By selecting one from the second
list box, you select its weight. Using both values together, 
you are able to resolve
both parameterized variables, thereby effectively selecting a range of animals to
view. For instance, select 4 in the Size list box, and then iterate through the choices
2, 4, 6, 8, and 10 in the Weight list box.</P>
<P>As a 
final touch, the PARAMS2 program displays a picture of the animals in question
in the <TT>TDBImage</TT> control. The blob field of the table that contains the picture
is called <TT>BMP</TT>. The <TT>TDBImage</TT> control asks only that you set its

<TT>DataSource</TT> property to a valid <TT>TDataSource</TT> object and its <TT>DataField</TT>
property to the name of the blob field you want to display. In this case, the <TT>DataSource</TT>
is <TT>DataSource1</TT>, and the blob field is called 
<TT>BMP</TT>. (See Listings
10.1 and 10.2.)<BR>
<BR>
<A NAME="Heading11"></A><FONT COLOR="#000077"><B>Listing 10.1. The PARAMS2 program
shows how to work with a parameterized query that has two fields.</B></FONT></P>
<PRE><FONT 
COLOR="#0066FF">//--------------------------------------------------------------------------

#include &lt;vcl\vcl.h&gt;

#pragma hdrstop

#include &quot;Main.h&quot;

#include &quot;DMod1.h&quot;


//--------------------------------------------------------------------------

#pragma resource &quot;*.dfm&quot;

TForm1 *Form1;

//--------------------------------------------------------------------------

__fastcall TForm1::TForm1(TComponent* 
Owner)

  : TForm(Owner)

{

}

//--------------------------------------------------------------------------

void __fastcall TForm1::FormCreate(TObject *Sender)

{

  int i;

  AnsiString S;

  for (i = 0; i &lt; 40; i++)

  {

    S = i;

    
ListBox1-&gt;Items-&gt;Add(S);

    ListBox2-&gt;Items-&gt;Add(S);

  }

  ListBox1-&gt;ItemIndex = 0;

  ListBox2-&gt;ItemIndex = 0;

}

//--------------------------------------------------------------------

void __fastcall 
TForm1::ListBox1Click(TObject *Sender)

{

  DMod-&gt;RunQuery(ListBox1-&gt;Items-&gt;Strings[ListBox1-&gt;ItemIndex].ToInt(),

                 ListBox1-&gt;Items-&gt;Strings[ListBox2-&gt;ItemIndex].ToInt());

}


//--------------------------------------------------------------------

void __fastcall TForm1::FormShow(TObject *Sender)

{

  DMod-&gt;RunQuery(0, 0);

}

//--------------------------------------------------------------------

</FONT></PRE>
<P><A 
NAME="Heading12"></A><FONT COLOR="#000077"><B>Listing 10.2. The data module
for the PARAMS2 program.</B></FONT></P>
<PRE><FONT COLOR="#0066FF">//--------------------------------------------------------------------------

#include &lt;vcl\vcl.h&gt;


#pragma hdrstop

#include &quot;DMod1.h&quot;

//--------------------------------------------------------------------------

#pragma resource &quot;*.dfm&quot;

TDMod *DMod;

//--------------------------------------------------------------------------


__fastcall TDMod::TDMod(TComponent* Owner)

  : TDataModule(Owner)

{

}

//--------------------------------------------------------------------------

void TDMod::RunQuery(int Box1, int Box2)

{

  AnimalsQuery-&gt;Close();

  
AnimalsQuery-&gt;Prepare();

  AnimalsQuery-&gt;Params-&gt;Items[0]-&gt;AsInteger = Box1;

  AnimalsQuery-&gt;Params-&gt;Items[1]-&gt;AsInteger = Box2;

  AnimalsQuery-&gt;Open();

}

</FONT></PRE>
<P>The interesting thing about the PARAMS2 program is 
that it lets inexperienced
database users ask a relatively complex question of a table. In particular, it lets
users ask for a list of all the animals that are larger than a certain size or weight.
Users can ask this question of the program without 
having to understand anything
about SQL.
<H3><A NAME="Heading13"></A><FONT COLOR="#000077">The SQL Property and the Format
Function</FONT></H3>
<P>I stated earlier that normally you can use parameterized variables only in cases
in which there is a 
<TT>where</TT> clause or an <TT>insert</TT> clause. There are
times, however, when these guidelines can be a bit limiting. If you find that you
need more flexibility, you can use BCB's <TT>Format</TT> function to create your
own special version of 
parameterized variables. Alternatively, you can use <TT>sprintf</TT>
to achieve the same ends through a similar method.</P>
<P>Consider the following SQL statement:</P>
<PRE><FONT COLOR="#0066FF">Select * from Country

</FONT></PRE>
<P>There are 
definitely times when you might want to parameterize the last word in
this statement so that it could vary over the life of a program:</P>
<PRE><FONT COLOR="#0066FF">Select * from :ACountry

</FONT></PRE>
<P>Unfortunately, most SQL servers won't 
support this syntax, so you're forced to
find another solution.</P>
<P>At times like these, the <TT>Format</TT> function can come to the rescue. The
VCL <TT>Format</TT> function works a lot like <TT>sprintf</TT>, except that it is
focused on 
AnsiStrings rather than C strings. All you really need to know about it
is that it enables you to substitute variables of almost any type for certain words
in a string. More specifically, you can compose a string that looks like this:</P>
<PRE><FONT 
COLOR="#0066FF">S = &quot;Select * from %s&quot;;

</FONT></PRE>
<P>In this string, the syntax <TT>%s</TT> performs the same role that the <TT>:FileName</TT>
syntax does in a parameterized query. The one difference, of course, is that you
should use 
<TT>%s</TT> only when you're working with a string. If you're working
with an <TT>Integer</TT>, use <TT>%d</TT>. In other words, it works exactly as you'd
expect it to work from your experience with <TT>sprintf</TT>.</P>
<P>The second parameter passed 
to <TT>Format</TT> is an <TT>OpenArray</TT>. When
you've declared two strings like this:</P>
<PRE><FONT COLOR="#0066FF">AnsiString ParamString(&quot;COUNTRY&quot;);

AnsiString SQLStatement;

</FONT></PRE>
<P>you can plug them into a <TT>Format</TT> 
statement:</P>
<PRE><FONT COLOR="#0066FF">SQLStatement = Format(&quot;Select * from %s&quot;, OPENARRAY(TVarRec, (S));

</FONT></PRE>
<P>Given the preceding code, after the <TT>Format</TT> function executed you would
end up with the following string 
in the variable <TT>SQLStatement</TT>:</P>
<PRE><FONT COLOR="#0066FF">&quot;Select * from COUNTRY&quot;

</FONT></PRE>
<P>Needless to say, this was exactly what you hoped to achieve, and the <TT>Format</TT>
function enables you to reach your goal 
without any of the restrictions placed on
parameterized variables.</P>
<P>Of course, this example was fairly simplistic, but if you wanted, you could create
a string that looks like this:</P>
<PRE><FONT COLOR="#0066FF">&quot;Select * from %s where %s 
= %d&quot;;

</FONT></PRE>
<P>This string contains three variables that can be changed at runtime, and it should
give you some hints as to the kind of flexibility you can achieve using this system.
For instance, you could write code that looks like 
this:</P>
<PRE><FONT COLOR="#0066FF">AnsiString GetQuery(AnsiString S1, AnsiString S2, int Value)

{

  return Format(&quot;Select * from %s where %s = %d&quot;, OPENARRAY(TVarRec, (S1, S2,                                                   Value)));


}

void __fastcall TForm1::StringTrick1Click(TObject *Sender)

{

  Caption = GetQuery(&quot;Customer&quot;, &quot;CustNo&quot;, 42);

}

</FONT></PRE>
<P>After substitutions are made, this sets the <TT>Caption</TT> of <TT>Form1</TT>
to the following 
string:</P>
<PRE><FONT COLOR="#0066FF">select * from Customer where CustNo = 42

</FONT></PRE>
<P>To see this entire process in action, refer to the <TT>PARAMS1</TT> program in
the <TT>CHAP08</TT> subdirectory. This program, shown in Listings 10.3 and 
10.4,
lets you pick from a list of tables and display the contents of each table in a data
grid.<BR>
<BR>
<A NAME="Heading14"></A><FONT COLOR="#000077"><B>Listing 10.3. The header for the
PARAMS1 program.</B></FONT></P>
<PRE><FONT 
COLOR="#0066FF">//--------------------------------------------------------------------------

#ifndef MainH

#define MainH

//--------------------------------------------------------------------------

#include &lt;vcl\Classes.hpp&gt;

#include 
&lt;vcl\Controls.hpp&gt;

#include &lt;vcl\StdCtrls.hpp&gt;

#include &lt;vcl\Forms.hpp&gt;

#include &lt;vcl\DBTables.hpp&gt;

#include &lt;vcl\DB.hpp&gt;

#include &lt;vcl\DBGrids.hpp&gt;

#include &lt;vcl\Grids.hpp&gt;

#include 
&lt;vcl\Menus.hpp&gt;

//--------------------------------------------------------------------------

class TForm1 : public TForm

{

__published:    // IDE-managed Components

    TListBox *ListBox1;

    TQuery *FormatQuery;