Specifying a SQL statement at runtime
Go Up to Specifying the SQL statement to execute
Contents
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,
- Call
Close
to deactivate the query. Even though an attempt to modify theSQL
property automatically deactivates the query, it is a good safety measure to do so explicitly. - If you are replacing the whole SQL statement, call the
Clear
method for theSQL
property to delete its current SQL statement. - If you are building the whole SQL statement from nothing or adding a line to an existing statement, call the
Add
method for theSQL
property to insert and append one or more strings to theSQL
property to create a new SQL statement. If you are modifying an existing line use theSQL
property with an index to indicate the line affected, and assign the new value. - Call
Open
orExecSQL
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“’;
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;
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;
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.