|
SQL and the TQuery Object 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. The material will be broken down into the following
main sections:
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.
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. 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: 1. 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. 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. If you have never used the TQuery 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--TQuery--and explains it in some depth. This chapter isn't intended to be a SQL
primer, but rather a description of the TQuery 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 LOCALSQL.HLP file that ships with BCB. (Open help, press
Alt+F+O, and then choose LOCALSQL.HLP. You need to be in a help file for this to
work, not in the Index or Content
section. Alternatively, you may have to browse to the ..\BCB\help subdirectory to find this file.) The SQL Property
The SQL property is probably the single
most important part of TQuery. 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 SQL property at design time, so the
next few sections concentrate on ways to manipulate it programmatically. Most people want to access the SQL 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 TQuery components on your form. Instead, you can
just place one on the form and simply change its SQL 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 SQL property and then covers more
advanced topics, such as parameterized queries. The SQL property is of type TStrings, 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 SQL property, you should study the
class TStrings
or TStringList.
(Don't try to implement a standalone version of the abstract TString class, but instead work with TStringList.) A brief description of TStringList appeared in Chapter 3, "C++Builder
and the VCL," near the end of the chapter in the section called
"Working with Text Files." When using TQuery programmatically, you should
first close the current query and clear out any strings that might already be
residing in the SQL property: Query1->Close();Query1->SQL->Clear();It's always safe to call Close. If the query is already closed,
the call will not cause an error. The next step is to add the new
strings that you want to execute: Query1->SQL->Add("Select * from Country");Query1->SQL->Add("where Name = `You can use the Add 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. To ask BCB to process the
statement and return a cursor containing the results of your query, you can
issue the following statement: Query1->Open();Note that Open 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 ExecSQL rather than Open. The ExecSQL command will be considered in
more depth later in this chapter. 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 Query, then Clear it, and pass a new string to the Add property: CountryQuery->Close();CountryQuery->SQL->Clear();CountryQuery->SQL->Add("Select * from Country");CountryQuery->Open();In this case, CountryQuery is a variable of type TQuery. I tend to append the word Query to my TQuery objects, just as a I append table after a TTable object. The sample program called EASYSQL
demonstrates this process. EASYSQL is shown in Figure 10.1. 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 Name
field begins with the letter C: Select * from Country where Name like `C%'The following syntax enables you
to see all the countries that have the letter C embedded somewhere in their name:
Select * from Country where Name like `%C%';Here's a statement that finds all
the countries whose name ends in the letters ia: Select * from Country where Name like `%ia';If you want to compose a series of
statements like the preceding one, you can expedite matters by using either
parameterized queries, sprintf, or the VCL Format function. These techniques will
all be explained in this chapter. One of the most powerful features
of the SQL property
is its ability to read text files containing SQL statements directly from
disk. This feature is also demonstrated in the EASYSQL program. Here's how it works. There are
several files with the extension SQL in the EASYSQL subdirectory. These files contain
SQL statements such as the ones shown previously. The EASYSQL 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 DatabaseName property for your TQuery object is assigned an alias
before you try this code. In particular, I work with the DBDEMOS alias in all these examples. The Load button has the following
response method for its OnClick event: void __fastcall TForm1::bbLoadClick(TObject *Sender){ if (OpenDialog1->Execute()) { TStringList *StringList = new TStringList(); StringList->LoadFromFile(OpenDialog1->FileName); DMod->RunQuery(StringList); StringList->Free(); }}The DMod RunQuery method looks like this: void TDMod::RunQuery(TStringList *StringList){ CountryQuery->Close(); CountryQuery->SQL = StringList; CountryQuery->Open();}The LoadClick method first loads the OpenDialog 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. OpenDialog1 has its Filter property set to the following
value: OpenDialog1->Filter = "SQL(*.SQL)|*.SQL"As a result, it lists only files
that have an SQL extension, as shown in Figure 10.2. The LoadFromFile 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 SQL 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. NOTE: In this example, I happen to create a StringList and then pass it into the RunQuery function. Alternatively, you
could simply pass a filename to the RunQuery function and let it use the LoadFromFile method of the TQuery SQL object: 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();} This latter technique is probably
the better of the two for this particular case, but it is important for you
to understand that the SQL property consists of a string list, so I include both techniques in
this chapter. In this section, you have seen two
methods of changing the SQL property at runtime. The first technique enables you to add strings
to the SQL
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 LoadFromFile 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. TQuery and
Parameters
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 where or insert 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.) To get started using parameterized
queries, consider again one of the simple SQL statements listed earlier: Select * from Country where Name like `C%'To turn this statement into a
parameterized query, just replace the right side of the like clause with a variable called NameStr: select * from County where Name like :NameStrIn this SQL statement, NameStr 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 NameStr. That colon tells BCB that it
should substitute the NameStr variable with a value that will be supplied
at some future point. It's important to note that the
word NameStr
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. There are two ways to supply
variables to a parameterized SQL statement. One method is to use the Params property of TQuery to supply the value at runtime.
The second is to use the DataSource property to supply information from another
dataset at either runtime or design time. Here are the key properties used to
accomplish these goals: __property TParams *Params;TParam *__fastcall ParamByName(const System::AnsiString Value);void __fastcall Prepare(void);Both TParam and TParams are objects found in DBTABLES.HPP. It is not particularly important
for you to understand how those objects work. When you substitute bind variables
in a parameterized query by using the Params property, you usually take four
steps: 1. Make sure the table is closed. Here's a sample code fragment
showing how this might be done in practice: void TDMod::NewParameterizedQuery(AnsiString S){ CountryQuery->Close(); CountryQuery->Prepare(); CountryQuery->ParamByName("NameStr")->AsString = S; CountryQuery->Open();}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 Params property that lies at the heart
of this process. Params is an indexed property that uses
a syntax similar to the Fields property from TDataSet. For instance, you can access the first bind
variable in a SQL statement by referring to element CountryQuery->Params->Items[0]->AsString := S;Or, if you prefer, you can use ParamByName instead: CountryQuery->ParamByName("NameStr")->AsString = S;There is a classic trade-off here,
in that Params->Items usually executes somewhat faster than ParamByName, because there is no string
handling involved in tracking down the referenced parameter. However, ParamByName is safer, because your code would
not break simply because the order of the fields was changed. If you combine a simple
parameterized SQL statement such as this select * from Country where Name like :NameStrwith the Params statements shown previously, the
result is the following SQL statement: select * from Country where Name like `What's happened here is that the
variable :NameStr
has been assigned the value If you have more than one
parameter in a statement, you can access them by changing the index of the Params property: Params->Items[1]->AsString = "SomeValue";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. Before you use the Params variable, you should first call Prepare. A call to Prepare causes BCB to parse your SQL
statement and ready the Params 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 Query will be executed over and over. If you try to assign a value to the Params variable without first calling Prepare, 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 UnPrepare statement that you should use if
you are very concerned about taking up database resources. After you've called Prepare and assigned the correct values
to the Params
variable, you should call Open 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 In the Examples 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. To create the program, place TQuery, TDataSource, TDBGrid, and TTabSet 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 DatabaseName property to the DBDEMOS alias. Fill in the tabset so that
it lists the alphabet from A to Z, as shown in Figure 10.3. Enter the following string in the SQL property for the query component:
select * from Country where Name like :NameStrNow all that's left to create is a
response method for the OnChange property of the tabset: void __fastcall TForm1::TabSet1Change(TObject *Sender, Integer NewTab, Boolean &AllowChange){ AnsiString S(UpperCase(TabSet1->Tabs->Strings[NewTab]) + "%"); DMod->NewParameterizedQuery(S);}The NewParameterizedQuery method is shown and explained a
few paragraphs back in this same section of the chapter. The code shown here follows the
four simple steps outlined previously. This is what the code does: 1. Closes the query The actual string assigned to the Params property consists of one of the
letters of the alphabet plus the % symbol. A typical query produced
by this method might look like this: Select * from Country where Name like `C%'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 Name field begins with an A. Press the
B tab, and you see only those items with a first letter of B. 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: Select * from Country where Name like :NameStrThis combination of SQL and BCB's
native language provides maximum power and flexibility when you want to
produce your own applications. NOTE: In the last chapter, I showed you how to
write this same type of program using the TTable object rather than the TQuery object. The question then
becomes, which one is better? 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: "Show me all the records where the Size field is above X, and the Weight field is above Y", where Size and Weight 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. To create the PARAMS2 program,
drop a query, data source, and DBgrid on a form, and place two list boxes and TDBImage above the grid, as shown in
Figure 10.4. Use TLabel objects to put the word Size above the first list box, and the word Weight above the second list box. Set
the DataSource
property of the TDBImage control to DataSource1, and type the word BMP in the editor for its DataField property. The SQL statement used in the
PARAMS2 program looks like this: select * from Animals where Animals."Size" > :Size and Animals."Weight" > :WeightTo satisfy the two parameters
specified in this SQL statement, you should create the following method: void TDMod::RunQuery(int Box1, int Box2){ sqlAnimals->Close(); sqlAnimals->Prepare(); sqlAnimals->Params->Items[0]->AsInteger = Box1; sqlAnimals->Params->Items[1]->AsInteger = Box2; sqlAnimals->Open();}The OnClick events for both list boxes should
be set to this simple routine, which calls TDMod::RunQuery: void __fastcall TForm1::ListBox1Click(TObject *Sender){ DMod->RunQuery(ListBox1->Items->Strings[ListBox1->ItemIndex].ToInt(), ListBox1->Items->Strings[ListBox2->ItemIndex].ToInt());}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 As a final touch, the PARAMS2
program displays a picture of the animals in question in the TDBImage control. The blob field of the
table that contains the picture is called BMP. The TDBImage control asks only that you set
its DataSource
property to a valid TDataSource object and its DataField property to the name of the blob
field you want to display. In this case, the DataSource is DataSource1, and the blob field is called BMP. (See Listings 10.1 and 10.2.) //--------------------------------------------------------------------------#include <vcl\vcl.h>#pragma hdrstop#include "Main.h"#include "DMod1.h"//--------------------------------------------------------------------------#pragma resource "*.dfm"TForm1 *Form1;//--------------------------------------------------------------------------__fastcall TForm1::TForm1(TComponent* Owner) : TForm(Owner){}//--------------------------------------------------------------------------void __fastcall TForm1::FormCreate(TObject *Sender){ int i; AnsiString S; for (i = 0; i < 40; i++) { S = i; ListBox1->Items->Add(S); ListBox2->Items->Add(S); } ListBox1->ItemIndex = 0; ListBox2->ItemIndex = 0;}//--------------------------------------------------------------------void __fastcall TForm1::ListBox1Click(TObject *Sender){ DMod->RunQuery(ListBox1->Items->Strings[ListBox1->ItemIndex].ToInt(), ListBox1->Items->Strings[ListBox2->ItemIndex].ToInt());}//--------------------------------------------------------------------void __fastcall TForm1::FormShow(TObject *Sender){ DMod->RunQuery(0, 0);}//--------------------------------------------------------------------Listing 10.2. The
data module for the PARAMS2 program. //--------------------------------------------------------------------------#include <vcl\vcl.h>#pragma hdrstop#include "DMod1.h"//--------------------------------------------------------------------------#pragma resource "*.dfm"TDMod *DMod;//--------------------------------------------------------------------------__fastcall TDMod::TDMod(TComponent* Owner) : TDataModule(Owner){}//--------------------------------------------------------------------------void TDMod::RunQuery(int Box1, int Box2){ AnimalsQuery->Close(); AnimalsQuery->Prepare(); AnimalsQuery->Params->Items[0]->AsInteger = Box1; AnimalsQuery->Params->Items[1]->AsInteger = Box2; AnimalsQuery->Open();}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.
The SQL Property and
the Format Function
I stated earlier that normally you
can use parameterized variables only in cases in which there is a where clause or an insert 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 Format function to create your own special version
of parameterized variables. Alternatively, you can use sprintf to achieve the same ends through
a similar method. Consider the following SQL
statement: Select * from CountryThere 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: Select * from :ACountryUnfortunately, most SQL servers
won't support this syntax, so you're forced to find another solution. At times like these, the Format function can come to the rescue.
The VCL Format
function works a lot like sprintf, 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: S = "Select * from %s";In this string, the syntax %s performs the same role that the :FileName syntax does in a parameterized
query. The one difference, of course, is that you should use %s only when you're working with a
string. If you're working with an Integer, use %d. In other words, it works exactly
as you'd expect it to work from your experience with sprintf. The second parameter passed to Format is an OpenArray. When you've declared two strings
like this: AnsiString ParamString("COUNTRY");AnsiString SQLStatement;you can plug them into a Format statement: SQLStatement = Format("Select * from %s", OPENARRAY(TVarRec, (S));Given the preceding code, after
the Format
function executed you would end up with the following string in the variable SQLStatement: "Select * from COUNTRY"Needless to say, this was exactly
what you hoped to achieve, and the Format function enables you to reach
your goal without any of the restrictions placed on parameterized variables. Of course, this example was fairly
simplistic, but if you wanted, you could create a string that looks like
this: "Select * from %s where %s = %d";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: AnsiString GetQuery(AnsiString S1, AnsiString S2, int Value){ return Format("Select * from %s where %s = %d", OPENARRAY(TVarRec, (S1, S2, Value)));}void __fastcall TForm1::StringTrick1Click(TObject *Sender){ Caption = GetQuery("Customer", "CustNo", 42);}After substitutions are made, this
sets the Caption
of Form1 to
the following string: select * from Customer where CustNo = 42To see this entire process in
action, refer to the PARAMS1 program in the CHAP08 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. //--------------------------------------------------------------------------#ifndef MainH#define MainH//--------------------------------------------------------------------------#include <vcl\Classes.hpp>#include <vcl\Controls.hpp>#include <vcl\StdCtrls.hpp>#include <vcl\Forms.hpp>#include <vcl\DBTables.hpp>#include <vcl\DB.hpp>#include <vcl\DBGrids.hpp>#include <vcl\Grids.hpp>#include <vcl\Menus.hpp>//--------------------------------------------------------------------------class TForm1 : public TForm{__published: // IDE-managed Components TListBox *ListBox1; TQuery *FormatQuery; 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 declarationspublic: // User declarations virtual __fastcall TForm1(TComponent* Owner);};//--------------------------------------------------------------------------extern TForm1 *Form1;//--------------------------------------------------------------------------#endifListing 10.4. The
PARAMS1 program shows how to use the Format function with a SQL query. //--------------------------------------------------------------------------#include <vcl\vcl.h>#pragma hdrstop#include "Main.h"//--------------------------------------------------------------------------#pragma resource "*.dfm"TForm1 *Form1;//--------------------------------------------------------------------------__fastcall TForm1::TForm1(TComponent* Owner) : TForm(Owner){}//--------------------------------------------------------------------------void __fastcall TForm1::FormCreate(TObject *Sender){ Session->GetTableNames(FormatQuery->DatabaseName, "", False, False, ListBox1- >Items);}//--------------------------------------------------------------------void __fastcall TForm1::ListBox1Click(TObject *Sender){ AnsiString S = ListBox1->Items->Strings[ListBox1->ItemIndex]; S = Format("Select * from %s", OPENARRAY(TVarRec, (S))); Caption = S; FormatQuery->Close(); FormatQuery->SQL->Clear(); FormatQuery->SQL->Add(S); FormatQuery->Open();}//--------------------------------------------------------------------AnsiString GetQuery(AnsiString S1, AnsiString S2, int Value){ return Format("Select * from %s where %s = %d", OPENARRAY(TVarRec, (S1, S2, Value)));}void __fastcall TForm1::StringTrick1Click(TObject *Sender){ Caption = GetQuery("Customer", "CustNo", 42);}//--------------------------------------------------------------------To create the PARAMS1 program,
place a query on the form and set its DatabaseName property to DBDEMOS. To create the list of tables,
place a TListBox
object on the form and create the following FormCreate method: void __fastcall TForm1::FormCreate(TObject *Sender){ Session->GetTableNames(FormatQuery->DatabaseName, "", False, False, ListBox1- >Items);}The call to the TSession object's GetTableNames
routine 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 c*.* 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 TStrings
that holds the output from the function. NOTE: Depending on your point of view, the TSession 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 TSession object is a vast repository of
information similar to the kind retrieved by the GetTableNames method. In general, the TSession object specializes in lists. Here
are some of the lists you can retrieve with the TSession object:
You can also use the TSession object to create Aliases, to
Modify Aliases, and to save these Aliases into the IDAPI.CFG file. Because the subject of the TSession object is so powerful, I will
give this subject almost the whole of Chapter 11, "Working with Field
Objects." To enable the user to view the
contents of the tables listed in the FormCreate method, you should add a TDataSource and TDBGrid to the form, and then wire them
up. Next, create a response method for
the ListBox1.OnClick event: void __fastcall TForm1::ListBox1Click(TObject *Sender){ AnsiString S = ListBox1->Items->Strings[ListBox1->ItemIndex]; S = Format("Select * from %s", OPENARRAY(TVarRec, (S))); Caption = S; FormatQuery->Close(); FormatQuery->SQL->Clear(); FormatQuery->SQL->Add(S); FormatQuery->Open();}The first line of the code shown here
assigns a string the value from the currently selected item from a list box. The next line in the program
creates a new SQL statement. To do this, it calls on the Format 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: select * from ORDERSThe next line of code checks to
make sure that the query is closed: Query1->Close()The next line then clears out any
strings currently sitting in the SQL property: Query1->SQL->Clear();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. Passing Parameters
Through TDataSource
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 TQuery object. The TQuery object has a DataSource property that can be used to
create a link between itself and another dataset. It doesn't matter whether
the other dataset is a TTable object, TQuery object, or some other descendant of TDataSet 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. In the following explanation,
assume that you want to create a link between the ORDERS table and the CUSTOMERS table, so that whenever you view
a particular customer record, only the orders associated with that customer
will be visible. Consider the following
parameterized query: Select * from Orders where CustNo = :CustNoIn this statement, :CustNo is a bind variable that needs to
be supplied a value from some source. BCB enables you to use the TQuery DataSource field to point at another
dataset, which can supply that information to you automatically. In other
words, instead of being forced to use the Params 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 DataSource
property. Only if that fails does it expect to get the variable from the Params property. Take a moment to consider exactly
what happens in these situations. As you saw in the last chapter, the CustNo field forms a link between the ORDERS table and the CUSTOMER table. (It's the Primary Key in the CUSTOMER table, and a Foreign Key in the Orders table.) Therefore, if both tables
are visible on a form, the appropriate CustNo value is always available in the
current record of the CUSTOMER table. All you need to do is point the Query object in the appropriate
direction. To obtain the bind value, just set
the DataSource
for the Query
object to the TDataSource
object that's associated with the CUSTOMER table. That's all there is to it!
Just enter a short SQL statement, link up the DataSource property, and Bingo! You've established
a one-to-many relationship like the linked cursors example from the last
chapter! On the CD-ROM that accompanies
this book, you'll find an example called QuickLinks that demonstrates how this
technique works. To create the QuickLinks program, place two TQuery, two TDataSource, and two TDBGrids on a form, as shown in Figure
10.5. In the SQL property for the first TQuery component, enter the following: select * from CustomerIn the second TQuery component, enter the following: select * from Orders where CustNo = :CustNoTo complete the program, all you
have to do is wire up the controls by attaching DBGrid1 to DataSource1, and DataSource1 to Query1. Perform the same action for the
second set of controls, and then set the Query2.DataSource property to DataSource1. 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. If you want to create a link
between two tables using multiple fields, you can simply specify the relevant
fields in your query: select * from Orders where CustNo = :CustNo and CustCountry = :CustCountryThe 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 ORDERS table that happen to have a particular customer number. The customer
number in question was passed to you through the DataSource property and the bind variable
you created. The examples you've seen so far in
this chapter should give you some feeling for the extreme power and
flexibility inherent in the TQuery object. If you're looking for a lever
powerful enough to move the roadblocks in your client/server programming
world, TQuery is
likely to be the tool you require. In the next section, you'll learn
more about the TQuery object when you see how to join two tables together so that you can
view them both in a single dataset. Performing Joins
Between Multiple Tables
You've seen that the CUSTOMERS and ORDERS tables are related in a
one-to-many relationship based on the CustNo field. The ORDERS table and ITEMS tables are also bound in a
one-to-many relationship, only this time the field that connects them is
called OrderNo. More specifically, each order that
exists in the ORDERS
table will have one or more records from the ITEMS table associated with it. The
records from the ITEMS table specify characteristics, such as price and part number, of the
items associated with a particular sale. 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: ORDERS1: Suzie Customer (Oct 1, 1994): ITEMS1: Shrimp $12.95 ITEMS2: Artichoke $6.25 ITEMS3: Caesar salad $3.25 ITEMS4: Mineral water $2.50In a situation like this, it's
sometimes simplest to join the data from the ORDERS table and the ITEMS table, so that the resulting
dataset contains information from both tables: Suzie Oct 1, 1994 Shrimp $12.95Suzie Oct 1, 1994 Artichoke $6.25etc...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. Given the ORDERS and ITEMS tables from the demos
subdirectory, you can join them in such a way that the CustNo, OrderNo, and SaleDate fields from the ORDERS table are merged with the StockNo, Price, and Qty fields from the ITEMS table to form a new dataset
containing all six fields. A grid containing the resulting dataset is shown
in Figure 10.6. There's a substantial difference between linking cursors and joining tables. However, they both have two things in common:
The act of joining the ORDERS and ITEMS tables can be accomplished by a
single SQL statement that looks like this: select O."OrderNo", O."CustNo", O."SaleDate", O."ShipDate", I."PartNo ", I."Qty", I."Discount "from Orders O, Items Iwhere O.OrderNo = I.OrderNoThis statement consists of four
parts:
When you've created the SQL
statement that you want to use, there is nothing at all difficult about
performing a join. The QJOIN example that ships with BCB demonstrates exactly how to proceed. All
you need do is drop a TQuery, TDataSource,
and TDBGrid
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 SQL property of the query, fill in
the DatabaseName property, and then set Active to True. 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 ORDERS and ITEMS
tables. NOTE: When you are composing SQL statements in the
SQL field of the TQuery 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. There is not much point to showing
you the actual source code for the QJOIN program, because all the magic
occurs in the SQL statement quoted previously. The RequestLive
Property
The RequestLive field of the TQuery object can play an important role
in SQL programming. By default, any query you make with the TQuery object will return a read-only
dataset. However, you can attempt to get a live query by setting the TQuery RequestLive property to True. As a rule, if your query
involves only one table, then you can set RequestLive to True. If your query involves multiple
tables, setting RequestLive to True might not produce the desired
result. You can check the CanModify property to see if your request has
succeeded. In general, I use the TTable object rather than the TQuery 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 TTable object. Of course, there are some
things you can't do with TTable objects, such as produce a true join. If you want to update a table with
a SQL query, then you should use the SQL Update or Insert commands. That's the way SQL is
supposed to work. It's a conservative language. (Update, Insert, Delete, and other SQL statements will be
discussed later in this chapter.) There is also an UpdateSQL component that can be useful in
these circumstances, but I often find it simplest to place one or more TQuery 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 RequestLive
to True. If
that is the case, then you will have to pop up a separate dialog with a
series of simple TEdit controls in it. Use this dialog to get input from the user, and then
simply use the TQuery component to issue three Update commands, one for each table in
your join. When you are done, Refresh 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. Whatever limitations the RequestLive property may have are not unique
to BCB. If you want to edit tables quickly with a high-performance system,
use the TTable
object. Of course, you can try to use the TQuery object first, and see how these
requests are handled with your particular server. Your ability to set RequestLive to True is somewhat server-dependent. If
you can't set RequestLive
to True, and
you don't want to use TTable, just start writing some SQL statements to perform the update for
you. Part of the purpose of this chapter is to outline enough about TQuery and SQL so that you will know how
to write these kinds of statements by the time you finish this chapter. Parameterized
Queries and join Statements
You can mix parameterized queries
and join
statements. This is useful if you want to show the CUSTOMER table at the top of a form, and
then beneath it, show another dataset that contains records with information
from both the ORDERS
and ITEMS
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. 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. To create this program, drop down
a TTable, a TQuery, two data sources, and two data
grids. Hook up the TTable, the first data source, and the first grid to the CUSTOMER table. Wire up the remaining
controls and specify DataSource1 in the Query1.DataSource property. Now add the following
SQL statement in the Query1.SQL property: select O.CustNo, O.OrderNo, O.SaleDate, L.PartNo, L.Discount, L.Qtyfrom Orders O, Items LwhereO.CustNo = :CustNo and O.OrderNo = L.OrderNoThe statement pictured here is
very much like the one you saw in the last section, except that the where clause has been expanded to
include a bind variable: where O.CustNo = :CustNo and O.OrderNo = L.OrderNoThis clause now specifies two
different relationships: one between the CUSTOMER table and the ORDERS table, and the second between the
ORDERS table and the ITEMS table. More specifically, the
value for the CustNo
variable will be supplied by the current record of the CUSTOMER table through the link on the Query1.DataSource property. The link between the ORDERS table and ITEMS table will be the OrderNo field. 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. ExecSQL and the
Delete and Insert Statements
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 Query, you should always call Open. If you don't need to return a cursor, you
should call ExecSQL.
For instance, if you're inserting, deleting, or updating data, you should
call ExecSQL.
To state the same matter in slightly different terms, you should use Open whenever you compose a select statement, and you should use ExecSQL whenever you write any other kind
of statement. Here's a typical SQL statement
that you might use to delete a record from a table: delete from Country where Name = `This statement deletes any record
from the COUNTRY
database that has 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: delete from Country where Name = :CountryNameIn this case, CountryName is a variable that can be changed
at runtime by writing code that looks like this: Query2->Prepare;Query2->Params->Items[0]->AsString = "Query2->ExecSQL;Query1->Refresh;The code shown here first calls Prepare to inform BCB that it should
parse the SQL statement you gave it and ready the Params property. The next step is to
insert a value into the Params property, and then to execute the newly prepared SQL statement. Note
that you execute the statement not by calling Open, but by calling ExecSQL. Call ExecSQL when you don't need to return a
dataset. Finally, you display the results of your actions to the user by
asking the first query to refresh itself. The INSERT2 program from the Examples subdirectory demonstrates this
technique. That program uses three different TQuery objects. The first TQuery object works with a TDataSource and a TDBGridid object to display the COUNTRY database on screen. In Figure
10.8, you can see that the program has two buttons: one for deleting records,
and the other for inserting records. The second TQuery object in the SQLInsert program
is used to insert a record into the COUNTRY table, as explained next. The
third TQuery object
is used for deleting records. It has the following statement in its SQL property: delete from Country where Name = :Name;
The code associated with the
Delete button looks like this: void TDMod::Delete(void){ AnsiString S("Delete " + CountryQuery->Fields[0]->AsString + "?"); if (MessageDlg(S, mtConfirmation, TMsgDlgButtons() << mbYes << mbNo, 0) != ID_YES) return; DeleteQuery->Prepare(); DeleteQuery->Params->Items[0]->AsString = CountryQuery->Fields[0]->AsString; DeleteQuery->ExecSQL(); CountryQuery->Refresh();}DeleteQuery snags the name of the record to
delete from the currently selected record in the first query. This enables
the user to scroll through the list of records using the TDBGrid tool, and then delete whatever record
is current. After the deletion, CountryQuery.Refresh is called. A call to Refresh forces the Query to go and obtain the most recent
data from the disk, thereby allowing the program to reflect the deletion at
almost the same moment it is made. (Note that a real-world program meant to
be used with a typical set of users would query the user before performing a
deletion of this sort.) Here is a typical SQL statement
for inserting data into a table: insert into Country (Name, Capital, Continent, Area, Population)values (` `This is a convenient system, but
it has the disadvantage of forcing you to hard-code values into the
statement. To avoid this problem, the Query2 object has the following code in
its SQL
property: insert into Country (Name, Capital, Continent, Area, Population) values (:Name, :Capital, :Continent, :Area, :Population)Note that in this code, all the actual
values intended for insertion are specified by bind variables. These bind
variables are convenient because they enable you to write code that looks
like this: void TDMod::AutoInsert(void){ InsertQuery->Prepare(); InsertQuery->Params->Items[0]->AsString = "Erehwon"; InsertQuery->Params->Items[1]->AsString = "None"; InsertQuery->Params->Items[2]->AsString = "Imagination"; InsertQuery->Params->Items[3]->AsFloat = 0.0; InsertQuery->Params->Items[4]->AsFloat = 1.0; InsertQuery->ExecSQL(); CountryQuery->Refresh();}In the code shown here, you can
use edit controls to dynamically specify the values that you want to insert
at runtime. Notice that once again, the program calls ExecSQL rather than Open. This is because there's no need
to return a cursor from a SQL insert statement. The function ends with a call to Refresh, which assures that InsertQuery goes out to the disk and gets the
most recent data. NOTE: You might want to compare this version of the
INSERT program with the INSERT1 application that uses the TTable object. There are advantages to
both techniques, but you should remember that keeping code as simple as
possible is one way to construct applications that are robust and easy to
maintain. In this section, you've learned
about the differences between ExecSQL and Open. The major point to remember is
that select
statements return a cursor and therefore require a call to Open. delete, insert, and update don't return a cursor, and should
therefore be accompanied by calls to ExecSQL. All of this is demonstrated on
disk in the INSERT2 program. The call to Refresh ensures that the data displayed
to the user reflects the changes made by the delete statement. Specialized TQuery
Properties
By this time, you should have a
good feeling for how to use BCB to create and execute SQL statements. There
are, however, a few properties belonging to, or inherited by, TQuery that have not yet been mentioned:
__property System::Boolean UniDirectional;__property Bde::hDBIStmt StmtHandle;__property Bde::hDBIDb DBHandle; // From TDBDataSet__property Bde::hDBIDb Handle; // From TDBDataSetThe UniDirectional property is used to optimize your
access to a table. If you set UniDirectional to True, you can iterate through a table
more quickly, but you'll be able to move only in a forward direction. The StmtHandle property is related to the Handle property from TDBDataSet; it's included solely so you can
make your own calls directly to the Borland Database Engine. Under normal
circumstances, there would be no need for you to use this property, because
BCB's components can handle the needs of most programmers. However, if you're
familiar with the Borland Database Engine, and if you know that it has some
particular capability that isn't encapsulated in the VCL, you can use TQuery
.StmtHandle or TQuery .Handle to make calls directly to the
engine. The following short code fragment
shows two calls being made directly to the BDE: void __fastcall TForm1::bGetRecordCountClick(TObject *Sender){ int Records; DbiGetRecordCount(Query1->Handle, Records); Edit1->Text = (AnsiString)Records;}//--------------------------------------------------------------------void __fastcall TForm1::bbGetNetUserNameClick(TObject *Sender){ char Name[100]; DbiGetNetUserName(Name); Edit2->Text = (String)Name;}The BDE.HPP unit contains a list of all the
possible calls made to the Borland Database Engine. This file may appear on
your system in the INCLUDE\VCL directory. Having a Little Fun
with SQL
The last two chapters have been
chock-full of information, perhaps even more information than you would want
to absorb all at once. As a result, it might be a good idea to end this
chapter by learning a few simple things you can do with SQL that produce
interesting results. To get started, you will need a
place where you can interactively enter some SQL, just to see what it does.
One simple way to do this is to place a TQuery, TDataSource, TDBGrid, TButton and TMemo objects on a form, as shown in
Figure 10.9. Wire up the data components and set the TQuery to the DBDEMOS alias. If the user selects the button,
the following code will be executed: void __fastcall TForm1::OpenQueryBtnClick(TObject *Sender){ Query1->Close(); Query1->SQL->Clear(); Query1->SQL = Memo1->Lines; Query1->Open();}This code does nothing more than
attempt to execute as a SQL statement whatever text the user types into the
memo control. To make the program somewhat
easier to use, you can add the following code to the OnKeyPress event for the TMemo object: void __fastcall TForm1::Memo1KeyPress(TObject *Sender, char &Key){ if (Key == `\r') OpenQueryBtnClick(NULL);}This code traps presses on the
Enter key and delegates them to the same routine that handles clicks on the
form's button. This enables the user to enter a SQL statement, and then press
the Enter key to see the results of the statement. This means the user never
has to lift up his or her hands from the keyboard while playing with the
program. To get started with the program,
you might enter the following: select * from customerThis statement selects all the
records from the CUSTOMER table. To find out how many records are
in the table, enter the following: select Count(*) from customerTo see just the Company field from the table, enter this:
select company from customerTo see only the Company field from the CUSTOMER table and to have the data
arranged in alphabetical order, write this: select company from customer order by companyTo see the Company field all in caps, enter this: select upper(company) from customerTo see the Company field all in caps, and next to it
the company
field in normal letters, and to arrange the result alphabetically, write
this: select upper(company), company from customer order by companyNote that you could not order the
table by the Company
field in the first of the last two examples. This is because the Company field must be present in the
dataset to sort on it, and it is not considered present in the dataset if it
is only used in an upper clause. To group the data from the table
by state, enter the following: select Company, State from customer order by StateThis statement shows the Company and State fields from the table, and orders
them by state. Many of the fields in the table do not have a value for the
state field, and you will have to scroll past these blank fields before you
can properly see the results of this query. The following statement selects
the OrderNo
and ItemsTotal
fields from the Orders table: select OrderNo, ItemsTotal from ordersTo find the largest value in the ItemsTotal field, enter the following: select Max(ItemsTotal) from ordersAnd finally, here is how to get the
sum of all the values in the ItemsTotal field of the Orders table: select Sum(ItemsTotal) from ordersJust to spice things up a bit, you
can try the following slightly more complex query that returns all the
companies from the CUSTOMER table that had orders with an ItemTotal larger than 100,000: select Company, State from customerwhere CustNo in (select CustNo from Orders where ItemsTotal > 100000)Statements like this that contain
one query embedded in another query are called, naturally enough, subqueries.
All these queries are not only fun
to play with, but they also show that the local SQL that ships with the BDE
is a fairly powerful tool. Indeed, SQL is a flexible and powerful language
for manipulating databases. This brief taste of it should give you some sense
of the tool's possibilities, and some sense of how much work SQL can save you
if you know how to use it. Don't spend hours trying to do something in C++ if
it can be done in two short lines of SQL! Summary In this chapter, you have learned
the main features of the TQuery component. You have seen that you
can use this component to create SQL statements that enable you to manipulate
tables in a wide variety of useful ways. One of the keys to understanding TQuery's SQL property is
the ability to manipulate it at runtime. In this chapter, you saw three
different methods of manipulating this property. The first, and conceptually
simplest, is to merely use the Query1->SQL->Add function whenever you need to
change a query at runtime. Parameterized queries are less wasteful than using
the Add property, but there are some
limits on what you can do with parameterized queries. To get beyond these
limits, you can use the Format function, which enables you to
create almost any kind of SQL statement you could want at runtime. Regardless of how you treat the SQL property, there is no doubt that it is one
of the power centers in the BCB environment. Programmers who want to write powerful
SQL applications need to know almost everything they can about the SQL property. In the next chapter, you will
learn how to use the Fields Editor, as well as other tools to automate some
of the database tasks you have been performing in the last two chapters. VMS Desenvolvimentos
Diversas Dicas, Apostilas, Arquivos Fontes,
Tutoriais, Vídeo Aula, Download de Arquivos Relacionado a Programação em C++
Builder.
|