ch10.htm

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

    TDataSource 
*dsFormat;

    TDBGrid *DBGrid1;

    TMainMenu *MainMenu1;

    TMenuItem *StringTrick1;

    void __fastcall FormCreate(TObject *Sender);

    void __fastcall ListBox1Click(TObject *Sender);

    void __fastcall StringTrick1Click(TObject *Sender);


private:        // User declarations

public:         // User declarations

    virtual __fastcall TForm1(TComponent* Owner);

};

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

extern TForm1 *Form1;


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

#endif



</FONT></PRE>
<P><A NAME="Heading15"></A><FONT COLOR="#000077"><B>Listing 10.4. The PARAMS1 program
shows how to use the Format function with a SQL 
query.</B></FONT></P>
<PRE><FONT COLOR="#0066FF">//--------------------------------------------------------------------------

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

#pragma hdrstop

#include &quot;Main.h&quot;


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

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

TForm1 *Form1;

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

__fastcall TForm1::TForm1(TComponent* 
Owner)

  : TForm(Owner)

{

}

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

void __fastcall TForm1::FormCreate(TObject *Sender)

{

  Session-&gt;GetTableNames(FormatQuery-&gt;DatabaseName, &quot;&quot;, False, False, 
ListBox1-                         &gt;Items);

}

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

void __fastcall TForm1::ListBox1Click(TObject *Sender)

{

  AnsiString S = 
ListBox1-&gt;Items-&gt;Strings[ListBox1-&gt;ItemIndex];

  S = Format(&quot;Select * from %s&quot;, OPENARRAY(TVarRec, (S)));

  Caption = S;

  FormatQuery-&gt;Close();

  FormatQuery-&gt;SQL-&gt;Clear();

  FormatQuery-&gt;SQL-&gt;Add(S);

  
FormatQuery-&gt;Open();

}

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

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>To create the PARAMS1 program, place a query on the form and set its <TT>DatabaseName</TT>
property to <TT>DBDEMOS</TT>. To create the list of tables, place a 
<TT>TListBox</TT>
object on the form and create the following <TT>FormCreate</TT> method:</P>
<PRE><FONT COLOR="#0066FF">void __fastcall TForm1::FormCreate(TObject *Sender)

{

  Session-&gt;GetTableNames(FormatQuery-&gt;DatabaseName, &quot;&quot;, 
False, False, ListBox1-   &gt;Items);

}

</FONT></PRE>
<P>The call to the <TT>TSession</TT> object's <TT>GetTableNames routine</TT> returns
a complete list of valid table names from the database specified in the first parameter.
The second parameter 
is a string that can contain a file mask, if you so desire.
For instance, you can enter <TT>c*.*</TT> to get a list of all tables beginning with
the letter C. Just pass in an empty string if you want a list of all tables. The
fourth parameter is a 
Boolean value that specifies whether you want to work with
system tables, and the final parameter is a value of type <TT>TStrings</TT> that
holds the output from the function.

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



<BLOCKQUOTE>
	<P>
<HR>
<FONT 
COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Depending on your point of view,
	the <TT>TSession</TT> object is either one of the most interesting, or least interesting,
	BCB database objects. The argument in favor of its not being important is simply
	
that you don't have to know about it in order to do most kinds of database programming.
	The argument in favor of its importance rests on the fact that the <TT>TSession</TT>
	object is a vast repository of information similar to the kind retrieved by 
the <TT>GetTableNames</TT>
	method. In general, the <TT>TSession</TT> object specializes in lists. Here are some
	of the lists you can retrieve with the <TT>TSession</TT> object:

	<UL>
	<LI>Available databases<BR>
	<BR>
	
	<LI>Available tables<BR>
	
<BR>
	
	<LI>Available aliases<BR>
	<BR>
	
	<LI>Available drivers<BR>
	<BR>
	
	<LI>Available stored procedures
	</UL>

	<P>You can also use the <TT>TSession</TT> object to create Aliases, to Modify Aliases,
	and to save these Aliases into the 
<TT>IDAPI.CFG</TT> file. Because the subject of
	the <TT>TSession</TT> object is so powerful, I will give this subject almost the
	whole of Chapter 11, &quot;Working with Field Objects.&quot; 
<HR>


</BLOCKQUOTE>

<P>To enable the user to view the 
contents of the tables listed in the <TT>FormCreate</TT>
method, you should add a <TT>TDataSource</TT> and <TT>TDBGrid</TT> to the form, and
then wire them up.</P>
<P>Next, create a response method for the <TT>ListBox1.OnClick</TT> event:</P>

<PRE><FONT COLOR="#0066FF">void __fastcall TForm1::ListBox1Click(TObject *Sender)

{

  AnsiString S = ListBox1-&gt;Items-&gt;Strings[ListBox1-&gt;ItemIndex];

  S = Format(&quot;Select * from %s&quot;, OPENARRAY(TVarRec, (S)));

  Caption = S;

  
FormatQuery-&gt;Close();

  FormatQuery-&gt;SQL-&gt;Clear();

  FormatQuery-&gt;SQL-&gt;Add(S);

  FormatQuery-&gt;Open();

}

</FONT></PRE>
<P>The first line of the code shown here assigns a string the value from the currently
selected item from a 
list box.</P>
<P>The next line in the program creates a new SQL statement. To do this, it calls
on the <TT>Format</TT> function, and uses the string selected from the list box.
The result is a new SQL statement that requests a dataset containing the 
contents
of a table. For example, the string might look like this:</P>
<PRE><FONT COLOR="#0066FF">select * from ORDERS

</FONT></PRE>
<P>The next line of code checks to make sure that the query is closed:</P>
<PRE><FONT 
COLOR="#0066FF">Query1-&gt;Close()

</FONT></PRE>
<P>The next line then clears out any strings currently sitting in the <TT>SQL</TT>
property:</P>
<PRE><FONT COLOR="#0066FF">Query1-&gt;SQL-&gt;Clear();

</FONT></PRE>
<P>That's the end of the 
discussion of using parameterized queries from inside the
code of your program. The next section shows how to use them without having to write
any C++ code.
<H3><A NAME="Heading17"></A><FONT COLOR="#000077">Passing Parameters Through 
TDataSource</FONT></H3>
<P>In the last chapter, you learned about a technique for creating a one-to-many
relationship between two tables. Now, you'll learn about a second technique for performing
the same action, but this time using a <TT>TQuery</TT> 
object.</P>
<P>The <TT>TQuery</TT> object has a <TT>DataSource</TT> property that can be used
to create a link between itself and another dataset. It doesn't matter whether the
other dataset is a <TT>TTable</TT> object, <TT>TQuery</TT> object, or some 
other
descendant of <TT>TDataSet</TT> that you or another programmer might create. All
you have to do is ensure that the dataset is connected to a data source, and then
you're free to make the link.</P>
<P>In the following explanation, assume that you 
want to create a link between the
<TT>ORDERS</TT> table and the <TT>CUSTOMERS</TT> table, so that whenever you view
a particular customer record, only the orders associated with that customer will
be visible.</P>
<P>Consider the following 
parameterized query:</P>
<PRE><FONT COLOR="#0066FF">Select * from Orders where CustNo = :CustNo

</FONT></PRE>
<P>In this statement, <TT>:CustNo</TT> is a bind variable that needs to be supplied
a value from some source. BCB enables you to use the 
<TT>TQuery DataSource</TT> field
to point at another dataset, which can supply that information to you automatically.
In other words, instead of being forced to use the <TT>Params</TT> property to manually
supply a variable, the appropriate variable 
can simply be plucked from another table.
Furthermore, BCB always first tries to satisfy a parameterized query by using the
<TT>DataSource</TT> property. Only if that fails does it expect to get the variable
from the <TT>Params</TT> property.</P>

<P>Take a moment to consider exactly what happens in these situations. As you saw
in the last chapter, the <TT>CustNo</TT> field forms a link between the <TT>ORDERS</TT>
table and the <TT>CUSTOMER</TT> table. (It's the <TT>Primary Key</TT> in the 
<TT>CUSTOMER</TT>
table, and a <TT>Foreign Key</TT> in the <TT>Orders</TT> table.) Therefore, if both
tables are visible on a form, the appropriate <TT>CustNo</TT> value is always available
in the current record of the <TT>CUSTOMER</TT> table. All you 
need to do is point
the <TT>Query</TT> object in the appropriate direction.</P>
<P>To obtain the bind value, just set the <TT>DataSource</TT> for the <TT>Query</TT>
object to the <TT>TDataSource</TT> object that's associated with the <TT>CUSTOMER</TT>

table. That's all there is to it! Just enter a short SQL statement, link up the <TT>DataSource</TT>
property, and Bingo! You've established a one-to-many relationship like the linked
cursors example from the last chapter!</P>
<P>On the CD-ROM that 
accompanies this book, you'll find an example called <TT>QuickLinks</TT>
that demonstrates how this technique works. To create the <TT>QuickLinks</TT> program,
place two <TT>TQuery</TT>, two <TT>TDataSource</TT>, and two <TT>TDBGrids</TT> on
a form, 
as shown in Figure 10.5.<BR>
<BR>
<A NAME="Heading18"></A><A HREF="10ebu05.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/10/10ebu05.jpg">FIGURE 10.5.</A><FONT COLOR="#000077">
</FONT><I>The QuickLinks program shows how to create a one-to-many relationship using
the <TT>TQuery</TT> object.</I></P>

<P>In the <TT>SQL</TT> property for the first <TT>TQuery</TT> component, enter the
following:</P>
<PRE><FONT COLOR="#0066FF">select * from Customer

</FONT></PRE>
<P>In the second <TT>TQuery</TT> component, enter the following:</P>
<PRE><FONT 
COLOR="#0066FF">select * from Orders where CustNo = :CustNo

</FONT></PRE>
<P>To complete the program, all you have to do is wire up the controls by attaching
<TT>DBGrid1</TT> to <TT>DataSource1</TT>, and <TT>DataSource1</TT> to <TT>Query1</TT>.

Perform the same action for the second set of controls, and then set the <TT>Query2.DataSource</TT>
property to <TT>DataSource1</TT>. This last step is the main action that forms the
link between the two tables. If you now run the program, you'll see 
that the two
tables work together in the desired manner.</P>
<P>If you want to create a link between two tables using multiple fields, you can
simply specify the relevant fields in your query:</P>
<PRE><FONT COLOR="#0066FF">select * from Orders

   
where CustNo = :CustNo and

   CustCountry = :CustCountry