Specifying the Query

For true query-type datasets, you use the SQL property to specify the SQL statement for the dataset to execute. Some datasets, such as TADODataSet, TSQLDataSet, and client datasets, use a CommandText property to accomplish the same thing.

Most queries that return records are SELECT commands. Typically, they define the fields to include, the tables from which to select those fields, conditions that limit what records to include, and the order of the resulting dataset. For example:

Copy Code

SELECT CustNo, OrderNo, SaleDate
FROM Orders
WHERE CustNo = 1225
ORDER BY SaleDate

Queries that do not return records include statements that use Data Definition Language (DDL) or Data Manipulation Language (DML) statements other than SELECT statements (For example, INSERT, DELETE, UPDATE, CREATE INDEX, and ALTER TABLE commands do not return any records). The language used in commands is server-specific, but usually compliant with the SQL-92 standard for the SQL language.

The SQL command you execute must be acceptable to the server you are using. Datasets neither evaluate the SQL nor execute it. They merely pass the command to the server for execution. In most cases, the SQL command must be only one complete SQL statement, although that statement can be as complex as necessary (for example, a SELECT statement with a WHERE clause that uses several nested logical operators such as AND and OR). Some servers also support "batch" syntax that permits multiple statements; if your server supports such syntax, you can enter multiple statements when you specify the query.

The SQL statements used by queries can be verbatim, or they can contain replaceable parameters. Queries that use parameters are called parameterized queries. When you use parameterized queries, the actual values assigned to the parameters are inserted into the query before you execute, or run, the query. Using parameterized queries is very flexible, because you can change a user's view of and access to data on the fly at runtime without having to alter the SQL statement. For more information about parameterized queries, see Using parameters in queries.

Specifying a query using the SQL property

When using a true query-type dataset (TQuery, TADOQuery, TSQLQuery, or TIBQuery), assign the query to the SQL property. The SQL property is a TStrings object. Each separate string in this TStrings object is a separate line of the query. Using multiple lines does not affect the way the query executes on the server, but can make it easier to modify and debug the query if you divide the statement into logical units:

Copy Code

MyQuery.Close;
MyQuery.SQL.Clear;
MyQuery.SQL.Add('SELECT CustNo, OrderNO, SaleDate');
MyQuery.SQL.Add(' FROM Orders');
MyQuery.SQL.Add('ORDER BY SaleDate');
MyQuery.Open;

Copy Code

MyQuery->Close();
MyQuery->SQL->Clear();
MyQuery->SQL->Add("SELECT CustNo, OrderNO, SaleDate");
MyQuery->SQL->Add("FROM Orders");
MyQuery->SQL->Add("ORDER BY SaleDate");
MyQuery->Open();

The code below demonstrates modifying only a single line in an existing SQL statement. In this case, the ORDER BY clause already exists on the third line of the statement. It is referenced via the SQL property using an index of 2.

Copy Code

MyQuery.SQL[2] := 'ORDER BY OrderNo';

Copy Code

MyQuery->SQL->Strings[2] = "ORDER BY OrderNO";

Note: The dataset must be closed when you specify or modify the SQL property.

At design time, use the String List editor to specify the query. Click the ellipsis button by the SQL property in the Object Inspector to display the String List editor.

Note: With some versions of Delphi, if you are using TQuery, you can also use the SQL Builder to construct a query based on a visible representation of tables and fields in a database. To use the SQL Builder, select the query component, right-click it to invoke the context menu, and choose Graphical Query Editor. To learn how to use SQL Builder, open it and use its online help.

Because the SQL property is a TStrings object, you can load the text of the query from a file by calling the TStrings.LoadFromFile method:

Copy Code

MyQuery.SQL.LoadFromFile('custquery.sql');

Copy Code

MyQuery->SQL->LoadFromFile("custquery.sql");

You can also use the Assign method of the SQL property to copy the contents of a string list object into the SQL property. The Assign method automatically clears the current contents of the SQL property before copying the new statement:

Copy Code

MyQuery.SQL.Assign(Memo1.Lines);

Copy Code

MyQuery->SQL->Assign(Memo1->Lines);

Specifying a query using the CommandText property

When using TADODataSet, TSQLDataSet, or a client dataset, assign the text of the query statement to the CommandText property:

Copy Code

MyQuery.CommandText := 'SELECT CustName, Address FROM Customer';

Copy Code

MyQuery->CommandText = "SELECT CustName, Address FROM Customer";

At design time, you can type the query directly into the Object Inspector, or, if the dataset already has an active connection to the database, you can click the ellipsis button by the CommandText property to display the Command Text editor. The Command Text editor lists the available tables, and the fields in those tables, to make it easier to compose your queries.