Specifying a SQL statement at runtime

Go Up to Specifying the SQL statement to execute

There are three ways to set the SQL property at runtime. An application can set the SQL property directly, it can call the SQL property’s LoadFromFile method to read a SQL statement from a file, or a SQL statement in a string list object can be assigned to the SQL property.

Setting the SQL property directly

To directly set the SQL property at runtime,

1. Call Close to deactivate the query. Even though an attempt to modify the SQL property automatically deactivates the query, it is a good safety measure to do so explicitly.
2. If you are replacing the whole SQL statement, call the Clear method for the SQL property to delete its current SQL statement.
3. If you are building the whole SQL statement from nothing or adding a line to an existing statement, call the Add method for the SQL property to insert and append one or more strings to the SQL property to create a new SQL statement. If you are modifying an existing line use the SQL property with an index to indicate the line affected, and assign the new value.
4. Call Open or ExecSQL to execute the query.

The following code illustrates building an entire SQL statement from nothing.

with CustomerQuery do begin
  Close;                     { close the query if it’s active }
  with SQL do begin
    Clear;                { delete the current SQL statement, if any }
    Add(‘SELECT * FROM Customer’);        { add first line of SQL... }
    Add(‘WHERE Company = “Sight Diver”’);      { ... and second line }
  end;
  Open;                                        { activate the query }
end;

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

CustomerQuery.SQL[1] := ‘WHERE Company = “Kauai Dive Shoppe“’;

Note:
If a query uses parameters, you should also set their initial values and call the Prepare method before opening or executing a query. Explicitly calling Prepare is most useful if the same SQL statement is used repeatedly; otherwise it is called automatically by the query component.

Loading the SQL property from a file

You can also use the LoadFromFile method to assign a SQL statement in a text file to the SQL property. The LoadFromFile method automatically clears the current contents of the SQL property before loading the new statement from file. For example:

CustomerQuery.Close;
CustomerQuery.SQL.LoadFromFile(‘c:\orders.txt’);
CustomerQuery.Open;

Note:
If the SQL statement contained in the file is a parameterized query, set the initial values for the parameters and call Prepare before opening or executing the query. Explicitly calling Prepare is most useful if the same SQL statement is used repeatedly; otherwise it is called automatically by the query component.

Loading the SQL property from string list object

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. For example, copying a SQL statement from a TMemo component:

CustomerQuery.Close;
CustomerQuery.SQL.Assign(Memo1.Lines);
CustomerQuery.Open;

Note:
If the SQL statement is a parameterized query, set the initial values for the parameters and call Prepare before opening or executing the query. Explicitly calling Prepare is most useful if the same SQL statement is used repeatedly; otherwise it is called automatically by the query component.

Advance To:

• Setting parameters