February 1998

TQuery with parameters

by Gerry Myers

As part of my current project, I needed to display records from a database table. My immediate choice was to use a grid (TDBGrid) linked to the database table (TTable)--simple enough and straightforward. I could easily drop the grid, table, and data source components onto my form, link everything at design time, and be finished in a matter of minutes.

However, after some thought I realized that the users would probably (later, of course) want some mechanism to view a subset of the data. As you probably know if you've used the TTable/TDBGrid combination, unless you specify a master/slave relationship or some kind of filter, every record in the table is displayed. In this case, I didn't have another table to serve as the master, and I didn't want to use a filter. Instead of the ever-familiar TTable, I decided to use a TQuery component to connect to the underlying database table. The main advantage for my application was that TQuery would let me specify parameters (variables) in the query's SQL statement that I could set during runtime.

If you've been using TTables for all your database connections, you may want to consider TQuery instead on your next project. The flexibility of the SQL alone makes it a worthwhile tool--but with the addition of parameters, the TQuery becomes an extremely powerful database component. In this article, we'll discuss TQuery parameters and how to set them in your code.

TQuery and SQL

The heart of the TQuery component is the SQL property, shown in Figure A. The SQL is the "rule" that the query uses to determine which fields and records to pull out of which database tables. It also can specify constraints (for instance, ORDER BY) and can even contain subqueries that determine how the records will be presented.

Figure A: The TQuery component has these properties.
[ Figure A ]

Listing A shows a simple SQL statement. It selects all fields (columns) from the underlying database table (Measure.db), but only those records (rows) that have the string DegF in the Units field. You'd use such a statement if the user wanted to see only records that deal with temperature in degrees Fahrenheit.

Listing A: A simple SQL statement

SELECT * FROM Measure.db
WHERE Units = "DegF"
As I mentioned earlier, you could perform a similar task with a TTable by having another table serve as the master. You'd link the two tables to form a master/slave relationship tied through the Units field. When the user selected a record in the master, that record's Units field would automatically be used to pull out only those records in the slave's table with matching Units values.

That technique requires another table and depends on the user to select the master's records (although it's possible to select the master's records through code). There's a much more straightforward approach that requires fewer components and links. It makes use of the TQuery component instead of the TTable and also introduces parameters into the SQL statement.

Parameters

A parameter is a variable in the SQL statement that you can fill at design time or runtime. There are no structures to declare or fill out and no additional program variables to worry about--the TQuery component automatically recognizes the parameter and allows you to set its value. The parameter is generally the value of a database field. For example, the SQL statement shown in Listing B is similar to the one in Listing A, but it uses a parameter instead of the hard-coded DegF Units value. However, SQL parameters aren't limited to the WHERE portion of the query--you can specify which fields to SELECT, the sort order (ORDER BY), the database from which to pull the records, and much more. You also can easily use two or more parameters in the same SQL statement to allow greater flexibility. Note that a parameter must begin with a semicolon (:) and end with the name of the field containing the value.

Listing B: A simple SQL statement with a parameter

SELECT * FROM Measure.db
WHERE Units = :Units
Where exactly does the :Units parameter get its value? You set its value in your code with just a few simple lines. The link between your TQuery component and the underlying table is made for you automatically when you specify both the TQuery's Database property and the table name in the SQL statement's FROM section.

As you can see in Figure A, my example database is called Articles; the Measure.db table in that database will supply the data. Measure.db has the following fields: MeasId, Description, Units, Chassis, Slot, Channel, and Reading. The Units field will be searched for a match to the parameter value. Only those records in Measure.db whose Units value matches the :Units value will be displayed in the grid.

Defining parameters at design time

The easiest way to use parameters with a TQuery is to define the SQL statement and parameters at design time. Double-click the TQuery's SQL property in the Object Inspector to open the standard String List Editor window; you can then enter your SQL statement directly. Defining the SQL statement that contains a parameter is only half the task; you must also define the actual parameter. You do so by double-clicking the TQuery's Params property to open the Parameters dialog box shown in Figure B. The Parameter Name is displayed automatically, since you entered it (:Units) in the SQL statement. The TQuery knows that it has a parameter in its SQL statement, but it needs to be told the parameter's data type and whether the parameter has an initial value. In this case, the Units field is of String (ASCII) type, and its initial value is NULL.

Figure B: You define a parameter in the TQuery's Parameters dialog box.
[ Figure B ]

Defining parameters at runtime

You can also define the SQL statement and its parameters during runtime. When you drop the TQuery component on the form, you could choose to ignore the SQL and Params properties and let your code set them. In order to run the program without an SQL statement in the TQuery, you must leave the TQuery's Active property set to false. As a matter of fact, if you try to set it to true during design-time (without an SQL statement), you'll get an error message. When you've gathered all the information you need to construct your SQL statement, you'll build the query using code like that found in Listing C. The first line makes sure the TQuery component is closed (Active=false). This is just good practice, since messing with the SQL property on an open query will generate a program error.

Listing C: Building an SQL statement with a parameter at runtime

Query1->Close();
Query1->UnPrepare();
Query1->SQL->Clear();
Query1->SQL->Add
( 
  "SELECT * FROM Measure.db 
   WHERE Units = :Units" 
);
The next line "unprepares" the query. Before a query is executed, it's best to "prepare" the underlying database. Doing so lets the database know ahead of time that a query is coming, so it can optimize the way it will handle the query. You need to prepare the query only the first time you open it, as long as the query doesn't change. Therefore, before you make any changes, it's a good idea to unprepare the query so the underlying database can release any resources that it set aside for the query's repeated execution.

Next in the example code comes the actual SQL property, which is nothing more than a TStrings object. To ensure that any previous query is erased, you clear the SQL value. You then write the new query to the SQL property using TStrings' Add() method.

You may have noticed that you haven't yet opened (activated) the query--at this point, it still lacks one thing. The SQL statement contains a reference to a parameter called :Units. But so far, you haven't set the value of the parameter--and you don't have to until the query is opened.

Setting the Params property

The beauty of the TQuery is that you can set the SQL property once, then change the parameter over and over. In the previous code example's SQL, simply changing the :Units parameter will return different records from the database. Just as you can set the SQL property during design or at runtime, you also can set the value of the parameter at either time. Look at the Tquery's Parameters dialog box in Figure B; notice the Value field in which you can enter the parameter's initial value. For example, if you wanted all the temperature measurements to be presented to the user on program startup, you could set the initial parameter value to DegF.

Actually, if you wanted to check the database before running the program, you could make an entry in the dialog box's Value field and set the TQuery's Active property to true. Doing so would cause the appropriate records to be pulled from the database (according to the SQL statement) and displayed in my design-time grid. To see records with other Units values at design-time, you'd deactivate the TQuery (Active=false), change the contents of the Parameters dialog box's Value field, and reactivate the query (Active=true).

Setting Params at runtime

You can also set the value of the Params property at runtime. The code snippet shown in Listing D does just that.

Listing D: Setting the Params property at runtime

Query1->Close();
Query1->UnPrepare();
Query1->Params->ParamByName
  ( "Units" )->AsString = "DegF";
Query1->Prepare();
Query1->Open();
Again, it's a good practice to make sure the query is closed and unprepared before making changes to properties. Next, the code searches for the current parameter ("Units") using the function ParamByName(), since there can be multiple parameters. The ParamByName() function exposes a TString property that you can set to the desired value ("DegF"). After the parameter is set, the code prepares the query to allow the underlying database to do any optimizations. Finally, the last line activates (opens) the query. The records will now be pulled from the database and displayed in your grid. Whenever the user wants a different type of measurement, you simply re-execute this code with the appropriate new value in place of "DegF".

Putting it together

My application form, shown in Figure C, requires the user to select groups of records simply by clicking different buttons.

Figure C: Our sample interface lets the user select records by clicking buttons on this form.
[ Figure C ]

Depending on the groups of buttons selected, the application sets two SQL statements. If the user clicks the All button, the application uses the code from Listing E to display all records. As you can see, this SQL statement doesn't use a parameter.

Listing E: Displaying all records at runtime

Query1->Close();
Query1->UnPrepare();
Query1->SQL->Clear();
Query1->SQL->Add
  ( "SELECT * FROM Measure.db" );
Query1->Prepare();
Query1->Open();
You'll notice in Figure C that the buttons other than All are grouped together. These grouped buttons use the SQL statement from Listing C. This query depends on a parameter that must be set before the TQuery can be opened. The user must click the Select *... button first, to establish the SQL statement and create the parameter. After that, the user can click the DegF, PSIG, and Strain buttons to set the :Units parameter value. Once the parameter value is set, the application can activate (open) the query and display the records. For example, when the user clicks the DegF button, the application runs the event handler shown in Listing F.

Listing F: Setting the parameter and opening the query

// If the current query doesn't use any 
// parameters, let the user know.
if ( !Query1->ParamCount )
{
  Application->MessageBox( 
    "Set SQL statement first.", 
    "SQL Info", MB_OK | 
    MB_ICONEXCLAMATION );
  return;
}

// Setup and execute the hard-coded Param value.
Query1->Close();
Query1->UnPrepare();
Query1->Params->ParamByName
  ( "Units" )->AsString = "DegF";
Query1->Prepare();
Query1->Open();
The code first checks to be sure the correct SQL statement has been loaded, in case the All button's query is currently selected. Since that query doesn't take any parameters, trying to set a parameter would produce a runtime error. The rest of the code should look familiar from our discussion of Listing D. At this point, the grid will display only those records in which the Units field contains the value DegF.

Conclusion

Once you understand the SQL, the hardest part of using the TQuery component with a parameter is working through the closing, unpreparing, preparing, and opening sequence in the correct order, to avoid a runtime error. Whether you set up the TQuery at design-time or runtime, if you follow the simple examples we've presented in this article, your queries will be successful. Developers with even a limited knowledge of SQL will find the TQuery component and its parameters to be valuable tools in the database toolkit.