📄 ch10.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=iso-8859-1">
<META NAME="Author" Content="Steph Mineart">
<TITLE>Ch 10 -- SQL and the TQuery Object</TITLE>
</HEAD>
<BODY
BACKGROUND="bg1.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/bg1.gif" BGCOLOR="#FFFFFF">
<P ALIGN="CENTER"><IMG SRC="sams.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/sams.gif" WIDTH="75" HEIGHT="24" ALIGN="BOTTOM"
BORDER="0"><BR>
<BR>
<A HREF="index-3.htm" tppabs="http://pbs.mcp.com/ebooks/0672310228/index.htm"><IMG SRC="toc.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/toc.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM"
ALT="TOC" BORDER="0" NAME="toc4"></A><A HREF="ch09.htm" tppabs="http://pbs.mcp.com/ebooks/0672310228/ch09.htm"><IMG SRC="back-1.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/back.gif"
WIDTH="40" HEIGHT="40" ALIGN="BOTTOM" ALT="BACK" BORDER="0" NAME="toc1"></A><A HREF="ch11.htm" tppabs="http://pbs.mcp.com/ebooks/0672310228/ch11.htm"><IMG
SRC="forward.gif" tppabs="http://pbs.mcp.com/ebooks/0672310228/buttonart/forward.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM"
ALT="FORWARD" BORDER="0"
NAME="toc2"></A></P>
<H2 ALIGN="CENTER"><FONT COLOR="#000077">Charlie Calvert's C++ Builder Unleashed</FONT></H2>
<P>
<H2 ALIGN="CENTER"><A NAME="Heading1"></A><FONT COLOR="#000077">- 10 -</FONT></H2>
<H2 ALIGN="CENTER"><A
NAME="Heading2"></A><FONT COLOR="#000077">SQL and the TQuery
Object</FONT></H2>
<P>This chapter is about queries. It's a subject that lies at the heart of client/server
programming, so this is one of the more important chapters in the book.</P>
<P>The
material will be broken down into the following main sections:
<UL>
<LI>Using the <TT>TQuery</TT> object
<P>
<LI>Using SQL with local and remote servers to select, update, delete, and insert
records
<P>
<LI>Using SQL statements to create joins,
linked cursors, and programs that search
for individual records
</UL>
<P>The acronym SQL stands for Structured Query Language, and is usually pronounced
sequel or by saying each letter (Ess Que El ). Whichever way you choose to pronounce
it, SQL is
a powerful database language that is easily accessible from within BCB
but is distinct from BCB's native language. BCB can use SQL statements to retrieve
tables from a database, to perform joins between tables, to create one-to-many relationships,
or
to request almost any feature that your server can provide.</P>
<P>BCB ships with two SQL engines, one built into the BDE for use with Paradox and
dBASE, and the other built into InterBase. In addition, you can also gain access
to other SQL databases
such as MS SQL Server, Sybase, Oracle, DB2, and Informix.
As a rule, the InterBase SQL engine is more powerful than the one built into Paradox
or dBASE tables, but they both provide a wide range of services. The key point, however,
is that you can
perform SQL queries even if you're working on a stand-alone machine
and don't have access to a server.</P>
<P>BCB provides support for pass-through SQL, which means that you can compose SQL
statements and then have them sent directly (with one or two
exceptions) to an Oracle,
Sybase, InterBase, or other server. Pass-through SQL is a powerful feature for two
reasons:
<DL>
<DD><B>1.</B> Most servers can process SQL statements very quickly, which means that
you can use SQL on remote data to get an
extremely fast response to your requests.<BR>
<BR>
<B>2.</B> You can compose SQL statements that ask a server to perform specialized
tasks unavailable through BCB's native language.
</DL>
<P>In the last chapter, you learned a lot about how BCB
works internally and how
to utilize its native capabilities. Now it's time to see how BCB interacts with the
database tools that exist either on your current machine or on a network.</P>
<P>If you have never used the <TT>TQuery</TT> object before, you
should review the
section on that control found in Chapter 8, "Database Basics and Database Tools,"
which gives an overview of all the fundamental database tools found in BCB. This
chapter focuses on one of those tools--<TT>TQuery</TT>--and
explains it in some depth.</P>
<P>This chapter isn't intended to be a SQL primer, but rather a description of the
<TT>TQuery</TT> object and the basic tasks you can perform with it. Even if you don't
know anything about SQL, this chapter will still be
helpful to you, and you'll end
up learning a number of basic facts about how to compose a SQL statement. However,
for a detailed analysis of the language, you should turn to one of the many books
and public documents available on this subject. For
instance, I am partial to The
Practical SQL Handbook, Bowman et al, Addison Wesley. You also can refer to the handy
reference in the online help for the WISQL utility. Additional information is available
in the form of a <TT>LOCALSQL.HLP</TT> file
that ships with BCB. (Open help, press
Alt+F+O, and then choose <TT>LOCALSQL.HLP</TT>. You need to be in a help file for
this to work, not in the <TT>Index</TT> or <TT>Content</TT> section. Alternatively,
you may have to browse to the
<TT>..\BCB\help</TT> subdirectory to find this file.)
<H3><A NAME="Heading3"></A><FONT COLOR="#000077">The SQL Property</FONT></H3>
<P>The <TT>SQL</TT> property is probably the single most important part of <TT>TQuery</TT>.
You can access this
property from the Object Inspector during design time or programmatically
at runtime. In Chapter 6 you saw how to access the <TT>SQL</TT> property at design
time, so the next few sections concentrate on ways to manipulate it programmatically.</P>
<P>Most people want to access the <TT>SQL</TT> property at runtime in order to dynamically
change the statement associated with a query. For instance, if you want to issue
three SQL statements while your program is running, there's no need for you to
place
three <TT>TQuery</TT> components on your form. Instead, you can just place one on
the form and simply change its <TT>SQL</TT> property three times. The most efficient,
most powerful, and simplest means of doing this is through parameterized
queries,
which are explained in the next section. However, this chapter first examines the
basic features of the <TT>SQL</TT> property and then covers more advanced topics,
such as parameterized queries.</P>
<P>The <TT>SQL</TT> property is of type
<TT>TStrings</TT>, which means that it is
a series of strings kept in a list. The list acts very much as if it were an array,
but it's actually a special class with its own unique capabilities. If you want to
find out everything you can about the
<TT>SQL</TT> property, you should study the
class <TT>TStrings</TT> or <TT>TStringList</TT>. (Don't try to implement a standalone
version of the abstract <TT>TString</TT> class, but instead work with <TT>TStringList</TT>.)
A brief description of
<TT>TStringList</TT> appeared in Chapter 3, "C++Builder
and the VCL," near the end of the chapter in the section called "Working
with Text Files."</P>
<P>When using <TT>TQuery</TT> programmatically, you should first close the
current
query and clear out any strings that might already be residing in the <TT>SQL</TT>
property:</P>
<PRE><FONT COLOR="#0066FF">Query1->Close();
Query1->SQL->Clear();
</FONT></PRE>
<P>It's always safe to call <TT>Close</TT>. If the
query is already closed, the call
will not cause an error.</P>
<P>The next step is to add the new strings that you want to execute:</P>
<PRE><FONT COLOR="#0066FF">Query1->SQL->Add("Select * from Country");
Query1->SQL->Add("where Name = `Argentina'");
</FONT></PRE>
<P>You can use the <TT>Add</TT> property to append from one to X number of strings
to a SQL query, where X is limited only by the amount of memory on your machine.
Clearly I
could have used one statement to add the short SQL command shown in the
last two lines of code; however, I wanted to give you an example of how to add multiple,
or very long, strings to the SQL property.</P>
<P>To ask BCB to process the statement and
return a cursor containing the results
of your query, you can issue the following statement:</P>
<PRE><FONT COLOR="#0066FF">Query1->Open();
</FONT></PRE>
<P>Note that <TT>Open</TT> is the command you should give when you want to return
rows from a
table. If you don't want to get any data back--for instance, if you are
deleting or inserting data--you should call <TT>ExecSQL</TT> rather than <TT>Open</TT>.
The <TT>ExecSQL</TT> command will be considered in more depth later in this chapter.</P>
<P>Whenever you want to change a SQL statement, you can simply go through the process
outlined previously a second time. In particular, you can close the current <TT>Query</TT>,
then <TT>Clear</TT> it, and pass a new string to the <TT>Add</TT>
property:</P>
<PRE><FONT COLOR="#0066FF">CountryQuery->Close();
CountryQuery->SQL->Clear();
CountryQuery->SQL->Add("Select * from Country");
CountryQuery->Open();
</FONT></PRE>
<P>In this case, <TT>CountryQuery</TT> is
a variable of type <TT>TQuery</TT>. I tend
to append the word <TT>Query</TT> to my <TT>TQuery</TT> objects, just as a I append
<TT>table</TT> after a <TT>TTable</TT> object.</P>
<P>The sample program called EASYSQL demonstrates this process. EASYSQL
is shown
in Figure 10.1.<BR>
<BR>
<A NAME="Heading4"></A><A HREF="10ebu01.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/10/10ebu01.jpg">FIGURE 10.1.</A><FONT COLOR="#000077">
</FONT><I>The EASYSQL program shows how to issue multiple queries from a single <TT>TQuery</TT>
object.</I></P>
<P>The EASYSQL
program uses a feature of local SQL that lets you use case-insensitive
wild cards. For instance, the following SQL statement returns a dataset containing
all the records in which the <TT>Name</TT> field begins with the letter <TT>C</TT>:</P>
<PRE><FONT COLOR="#0066FF">Select * from Country where Name like `C%'
</FONT></PRE>
<P>The following syntax enables you to see all the countries that have the letter
<TT>C</TT> embedded somewhere in their name:</P>
<PRE><FONT COLOR="#0066FF">Select *
from Country where Name like `%C%';
</FONT></PRE>
<P>Here's a statement that finds all the countries whose name ends in the letters
<TT>ia</TT>:</P>
<PRE><FONT COLOR="#0066FF">Select * from Country where Name like `%ia';
</FONT></PRE>
<P>If you want
to compose a series of statements like the preceding one, you can
expedite matters by using either parameterized queries, <TT>sprintf</TT>, or the
VCL <TT>Format</TT> function. These techniques will all be explained in this chapter.</P>
<P>One of the
most powerful features of the <TT>SQL</TT> property is its ability
to read text files containing SQL statements directly from disk. This feature is
also demonstrated in the <TT>EASYSQL</TT> program.</P>
<P>Here's how it works. There are several files
with the extension SQL in the <TT>EASYSQL</TT>
subdirectory. These files contain SQL statements such as the ones shown previously.
The <TT>EASYSQL</TT> program has a Load button that enables you to select one of
these text files and then run the SQL
statement stored in that file. Be sure that
the <TT>DatabaseName</TT> property for your <TT>TQuery</TT> object is assigned an
alias before you try this code. In particular, I work with the <TT>DBDEMOS</TT> alias
in all these examples.</P>
<P>The Load
button has the following response method for its <TT>OnClick</TT> event:</P>
<PRE><FONT COLOR="#0066FF">void __fastcall TForm1::bbLoadClick(TObject *Sender)
{
if (OpenDialog1->Execute())
{
TStringList *StringList = new TStringList();
StringList->LoadFromFile(OpenDialog1->FileName);
DMod->RunQuery(StringList);
StringList->Free();
}
}
</FONT></PRE>
<P>The <TT>DMod</TT> <TT>RunQuery</TT> method looks like this:</P>
<PRE><FONT COLOR="#0066FF">void
TDMod::RunQuery(TStringList *StringList)
{
CountryQuery->Close();
CountryQuery->SQL = StringList;
CountryQuery->Open();
}
</FONT></PRE>
<P>The <TT>LoadClick</TT> method first loads the <TT>OpenDialog</TT> component and
enables the
user to select a file with a SQL extension. The code checks to see whether
the user has selected a file. If a file has been selected, the current query is closed,
and the selected file is loaded from disk and displayed to the user.</P>
<P><TT>OpenDialog1</TT> has its <TT>Filter</TT> property set to the following value:</P>
<PRE><FONT COLOR="#0066FF">OpenDialog1->Filter = "SQL(*.SQL)|*.SQL"
</FONT></PRE>
<P>As a result, it lists only files that have an SQL extension, as
shown in Figure
10.2.<BR>
<BR>
<A NAME="Heading5"></A><A HREF="10ebu02.jpg" tppabs="http://pbs.mcp.com/ebooks/0672310228/art/10/10ebu02.jpg">FIGURE 10.2.</A><FONT COLOR="#000077">
</FONT><I>The Open dialog from the EASYSQL program enables you to select a prepared
SQL statement from an ASCII file stored on
disk.</I></P>
<P>The <TT>LoadFromFile</TT> function enables you to load an entire text file at
runtime by issuing a single command. The trick, then, is to store SQL statements
in text files and load them at runtime. Because the <TT>SQL</TT> property
can contain
an essentially unlimited number of strings, there is no practical limit to the size
of the SQL statement that you could load in this fashion. You can use this technique
to quickly execute a series of very complex SQL statements.
<DL>
<DT></DT>
</DL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE</B></FONT><B>: </B>In this example, I happen to create
a <TT>StringList</TT> and then pass it into the <TT>RunQuery</TT> function. Alternatively,
you could simply pass a
filename to the <TT>RunQuery</TT> function and let it use
the <TT>LoadFromFile</TT> method of the <TT>TQuery</TT> SQL object:</P>
<PRE><FONT COLOR="#0066FF">void __fastcall TForm1::bbLoad2Click(TObject *Sender)
{
if (OpenDialog1->Execute())
DMod->RunQuery2(OpenDialog1->FileName);
}
void TDMod::RunQuery2(AnsiString S)
{
CountryQuery->Close();
CountryQuery->SQL->Clear();
CountryQuery->SQL->LoadFromFile(S);
CountryQuery->Open();
}</FONT></PRE>
<P>This latter technique is probably the better of the two for this particular case,
but it is important for you to understand that the <TT>SQL</TT> property consists
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -