|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

| ## Charlie Calvert's C++ Builder Unleashed ## - 10 - ## 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: Using the `TQuery` object Using SQL with local and remote servers to select, update, delete, and insert records Using SQL statements to create joins, linked cursors, and programs that search for individual records 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. 2. You can compose SQL statements that ask a server to perform specialized tasks unavailable through BCB's native language. 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 = `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.

### 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 :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:

  1. Make sure the table is closed.
  2. Ready the `Query` object by issuing the `Prepare` command (optional, but highly recommended).
  3. Assign the correct values to the `Params` property.
  4. Open the query.

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:

  1. Closes the query
  2. Prepares the `Params` property
  3. Assigns a string to the `Params` property
  4. Executes the resultant SQL statement by calling `Query1.Open`

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.

### 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 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.

### 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 = :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.

### 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.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:

- They involve two or more tables.

- Each table is linked to the other by one or more shared fields.

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:

- The `select` statement specifies that you expect a cursor to be returned containing some form of dataset.

- Next, there is a list of the fields that you want included in the dataset you are requesting. This list includes the `OrderNo`, `CustNo`, `SaleDate`, `ShipDate`, `PartNo`, `Qty`, and `Discount` fields. The first four fields originate in the `ORDERS` table, and the next three fields originate in the `ITEMS` table.

- The `from` clause states that you're working with two tables, one called `ORDERS` and the other called `ITEMS`. For the sake of brevity, the statement uses an optional SQL feature that lets you specify the `ORDERS` table with the letter `O`, and the `ITEMS` table with the letter `I`.

- The `where` clause is vitally important, because it specifies which field will link the two tables. Some servers are capable of returning valid datasets even if you don't include a `where` clause in your join, but the resulting set of records will almost surely not be what you want. To get the results you're looking for, be sure to include a `where` clause.

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.
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.

### 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 = `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.

### 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 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.

### 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.
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!

### 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.

©Copyright, Macmillan Computer Publishing. All rights reserved.

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.