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:
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 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 = `Argentina'");
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.
FIGURE 10.1.
The EASYSQL program shows how to issue multiple queries from a single TQuery
object.
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.
FIGURE 10.2.
The Open dialog from the EASYSQL program enables you to select a prepared
SQL statement from an ASCII file stored on
disk.
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.
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 :NameStr
In 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:
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 0 in the Params array:
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 :NameStr
with the Params statements shown previously, the result is the following SQL statement:
select * from Country where Name like `Argentina'
What's happened here is that the variable :NameStr has been assigned the value Argentina by the Params property, thereby enabling you to complete a simple SQL statement.
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 Argentina.
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.
FIGURE
10.3.
The EASYSQL2 program shows how to use parameterized queries.
Enter the following string in the SQL property for the query component:
select * from Country where Name like :NameStr
Now 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:
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 :NameStr
This 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?
Well, there is no definitive answer to this question. If you come from the client/server world and have been writing SQL statements for years, you will almost certainly prefer the TQuery object. If you come from the C++ world, you will probably prefer the TTable component because you are likely to find SQL awkward to write, at least at first.
In general, TTable is easier to use, and less prone to error, so it is a good choice in many cases. I certainly use it a great deal in my own programs.
If you are running against large SQL databases, you can use TQuery to optimize your code for maximum performance. There is at least some merit to the idea that you can use TTable when working with local data, but consider using TQuery if you are running against SQL server data, and particularly if you are working with a large SQL database. Most SQL servers were designed around the idea that users will access records one at a time, or in small groups. The concept of treating an entire table as a series of rows--which is the fundamental theory behind the TTable object--can run against the grain of some servers, particularly when they are carrying a large load.
Finally, I should add that no one can hope to do any serious contemporary database development without understanding something about SQL. SQL is a vital part of the client/server world, and if you don't understand how to use the TQuery object, your viability as a professional client/server programmer would be seriously, perhaps hopelessly, impaired. In general, a good database programmer has to be an expert in SQL, just as a good systems programmer should be an expert in C++ or Object Pascal.
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.
FIGURE
10.4.
The form for the PARAMS2 program, as it appears at runtime.
The SQL statement used in the PARAMS2 program looks like this:
select * from Animals
where
Animals."Size" > :Size and
Animals."Weight" > :Weight
To 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 4 in the Size list box, and then iterate through the choices 2, 4, 6, 8, and 10 in the Weight list box.
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.)
Listing 10.1. The PARAMS2 program
shows how to work with a parameterized query that has two fields.
//--------------------------------------------------------------------------
#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.
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 Country
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:
Select * from :ACountry
Unfortunately, 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 = 42
To 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.
Listing 10.3. The header for the
PARAMS1 program.
//--------------------------------------------------------------------------
#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 declarations
public: // User declarations
virtual __fastcall TForm1(TComponent* Owner);
};
//--------------------------------------------------------------------------
extern TForm1 *Form1;
//--------------------------------------------------------------------------
#endif
Listing 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:
- Available databases
- Available tables
- Available aliases
- Available drivers
- Available stored procedures
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 ORDERS
The 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.
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 = :CustNo
In 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.
FIGURE 10.5.
The QuickLinks program shows how to create a one-to-many relationship using
the TQuery object.
In the SQL property for the first TQuery component, enter the following:
select * from Customer
In the second TQuery component, enter the following:
select * from Orders where CustNo = :CustNo
To 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 = :CustCountry
The 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.
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.50
In 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.95 Suzie Oct 1, 1994 Artichoke $6.25 etc...
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.
FIGURE 10.6.
The QJOIN program joins the ORDERS and ITEMS table producing
a dataset with fields from each table.
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 I where O.OrderNo = I.OrderNo
This 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 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.
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.
FIGURE 10.7.
The QJOIN2 program shows three tables linked together in a logical
and
coherent fashion.
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.Qty from Orders O, Items L where O.CustNo = :CustNo and O.OrderNo = L.OrderNo
The 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.OrderNo
This 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.
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 = `Argentina';
This statement deletes any record from the COUNTRY database that has Argentina in the Name field.
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 = :CountryName
In 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 = "Argentina"; 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;
FIGURE 10.8. The SQLInsert program uses three TQuery components
and one TDataSource
component.
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 (`Argentina', `Buenos Ares', `South America', 2777815, 32300003)
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.
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 TDBDataSet
The 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.
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.
FIGURE 10.9.
The main form from the SQLFun program enables you to type SQL,
press a button, and see the
result.
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 customer
This 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 customer
To see just the Company field from the table, enter this:
select company from customer
To 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 company
To see the Company field all in caps, enter this:
select upper(company) from customer
To 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 company
Note 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 State
This 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 orders
To find the largest value in the ItemsTotal field, enter the following:
select Max(ItemsTotal) from orders
And finally, here is how to get the sum of all the values in the ItemsTotal field of the Orders table:
select Sum(ItemsTotal) from orders
Just 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 customer where 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!
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.
© Copyright, Macmillan Computer Publishing. All rights reserved.