Setting the SQL property directly

From InterBase
Jump to: navigation, search

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 }
  Open;                                        { activate the query }

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.