Queries with ADO - DB/7

man using laptop
Klaus Vedfelt/Taxi/Getty Images

SQL with TADOQuery

The TADOQuery component provides Delphi developers the ability to fetch data from one or multiple tables from an ADO database using SQL.

These SQL statements can either be DDL (Data Definition Language) statements such as CREATE TABLE, ALTER INDEX, and so forth, or they can be DML (Data Manipulation Language) statements, such as SELECT, UPDATE, and DELETE. The most common statement, however, is the SELECT statement, which produces a view similar to that available using a Table component.

Note: even though executing commands using the ADOQuery component is possible, the ADOCommandcomponent is more appropriate for this purpose. It is most often used to execute DDL commands or to execute a stored procedure (even though you should use theTADOStoredProc for such tasks) that does not return a result set.

The SQL used in a ADOQuery component must be acceptable to the ADO driver in use. In other words you should be familiar with the SQL writing differences between, for example, MS Access and MS SQL.

As when working with the ADOTable component, the data in a database is accessed using a data store connection established by the ADOQuery component using itsConnectionString property or through a separate ADOConnection component specified in the Connectionproperty.

To make a Delphi form capable of retrieving the data from an Access database with the ADOQuery component simply drop all the related data-access and data-aware components on it and make a link as described in the previous chapters of this course.

The data-access components: DataSource, ADOConnection along with ADOQuery (instead of the ADOTable) and one data-aware component like DBGrid is all we need. 
As already explained, by using the Object Inspector set the link between those components as follows:

DBGrid1.DataSource = DataSource1
DataSource1.DataSet = ADOQuery1
ADOQuery1.Connection = ADOConnection1
//build the ConnectionString
ADOConnection1.ConnectionString = ...


ADOConnection1.LoginPrompt = False

Doing a SQL query

The TADOQuery component doesn't have a TableNameproperty as the TADOTable does. TADOQuery has a property (TStrings) called SQL which is used to store the SQL statement. You can set the SQL property's value with the Object Inspector at design time or through code at runtime.

At design-time, invoke the property editor for the SQL property by clicking the ellipsis button in the Object Inspector. Type the following SQL statement: "SELECT * FROM Authors".

The SQL statement can be executed in one of two ways, depending on the type of the statement. The Data Definition Language statements are generally executed with the ExecSQL method. For example to delete a specific record from a specific table you could write a DELETE DDL statement and run the query with the ExecSQL method.
The (ordinary) SQL statements are executed by setting the TADOQuery.Active property to True or by calling theOpen method (essentialy the same). This approach is similar to retrieving a table data with the TADOTable component.

At run-time, the SQL statement in the SQL property can be used as any StringList object:

with ADOQuery1 do begin
  Close;
  SQL.Clear;
  SQL.Add:='SELECT * FROM Authors '
  SQL.Add:='ORDER BY authorname DESC'
  Open;
end;

The above code, at run-time, closes the dataset, empties the SQL string in the SQL property, assigns a new SQL command and activates the dataset by calling the Open method.

Note that obviously creating a persistent list of field objects for an ADOQuery component does not make sense. The next time you call the Open method the SQL can be so different that the whole set of filed names (and types) may change. Of course, this is not the case if we are using ADOQuery to fetch the rows from just one table with the constant set of fields - and the resulting set depends on the WHERE part of the SQL statement.

Dynamic queries

One of the great properties of the TADOQuery components is the Params property. A parameterized query is one that permits flexible row/column selection using a parameter in the WHERE clause of a SQL statement.

The Params property allows replacable parameters in the predefined SQL statement. A parameter is a placeholder for a value in the WHERE clause, defined just before the query is opened. To specify a parameter in a query, use a colon (:) preceding a parameter name. 

At design-time use the Object Inspector to set the SQL property as follows:

ADOQuery1.SQL := 'SELECT * FROM Applications WHERE type = :apptype'

When you close the SQL editor window open the Parameters window by clicking the ellipsis button in the Object Inspector.

The parameter in the preceding SQL statement is namedapptype. We can set the values of the parameters in the Params collection at design time via the Parameters dialog box, but most of the time we will be changing the parameters at runtime. The Parameters dialog can be used to specify the datatypes and default values of parameters used in a query.

At run-time, the parameters can be changed and the query re-executed to refresh the data. In order to execute a parameterized query, it is necessary to supply a value for each parameter prior to the execution of the query. To modify the parameter value, we use either the Params property or ParamByName method. For example, given the SQL statement as above, at run-time we could use the following code:

with ADOQuery1 do begin
  Close;
  SQL.Clear;
  SQL.Add('SELECT * FROM Applications WHERE type =:apptype');
  ParamByName('apptype').Value:='multimedia';
  Open;
end;

Navigating and editing the query

As like when working with the ADOTable component the ADOQuery returns a set or records from a table (or two or more). Navigating through a dataset is done with the same set of methods as described in the "Behind data in datasets" chapter.

In general ADOQuery component should not be used when editing takes place. The SQL based queries are mostly used for reporting purposes. If your query returns a result set, it is sometimes possible to edit the returned dataset. The result set must contain records from a single table and it must not use any SQL aggregate functions.

 Editing of a dataset returned by the ADOQuery is the same as editing the ADOTAble's dataset.

An example

To see some ADOQuery action we'll code a small example. Let's make a query that can be used to fetch the rows from various tables in a database. To show the list of all the tables in a database we can use the GetTableNamesmethod of the ADOConnection component. The GetTableNames in the OnCreate event of the form fills the ComboBox with the table names and the Button is used to close the query and to recreate it to retrieve the records from a picked table. The () event handlers should look like:

procedure TForm1.FormCreate(Sender: TObject);
begin
 ADOConnection1.GetTableNames(ComboBox1.Items);
end;

procedure TForm1.Button1Click(Sender: TObject);
var  tblname : string;
begin
if ComboBox1.ItemIndex then Exit;
tblname := ComboBox1.Items[ComboBox1.ItemIndex];
with ADOQuery1 do begin
  Close;
  SQL.Text := 'SELECT * FROM ' + tblname;
  Open;
end;
end;


Note that all this can be done by using the ADOTable and it's TableName property.