Specifying the SQL statement to execute
Go Up to Working with Queries
Use the SQL property to specify the SQL query statement to execute. At design time a query is prepared and executed automatically when you set the query component’s Active property to True. At runtime, a query is prepared with a call to Prepare, and executed when the application calls the component’s Open or ExecSQL methods.
The SQL property is a TStrings object, which is an array of text strings and a set of properties, events, and methods that manipulate them. The strings in SQL are automatically concatenated to produce the SQL statement to execute. You can provide a statement in as few or as many separate strings as you desire. One advantage to using a series of strings is that you can divide the SQL statement into logical units (for example, putting the WHERE clause for a SELECT statement into its own string), so that it is easier to modify and debug a query.
The SQL statement can be a query that contains hard-coded field names and values, or it can be a parameterized query that contains replaceable parameters that represent field values that must be bound into the statement before it is executed. For example, this statement is hard-coded:
SELECT * FROM Customer WHERE CustNo = 1231
Hard-coded statements are useful when applications execute exact, known queries each time they run. At design time or runtime you can easily replace one hard-code query with another hard-coded or parameterized query as needed. Whenever the SQL property is changed the query is automatically closed and unprepared.
In queries using local SQL, when column names in a query contain spaces or special characters, the column name must be enclosed in quotes and must be preceded by a table reference and a period. For example, BIOLIFE."Species Name".
A parameterized query contains one or more placeholder parameters, application variables that stand in for comparison values such as those found in the WHERE clause of a SELECT statement. Using parameterized queries enables you to change the value without rewriting the application. Parameter values must be bound into the SQL statement before it is executed for the first time. Query components do this automatically for you even if you do not explicitly call the Prepare method before executing a query.
This statement is a parameterized query:
SELECT * FROM Customer WHERE CustNo = :Number
The variable Number, indicated by the leading colon, is a parameter that fills in for a comparison value that must be provided at runtime and that may vary each time the statement is executed. The actual value for Number is provided in the query component’s Params property.
It is a good programming practice to provide variable names for parameters that correspond to the actual name of the column with which it is associated. For example, if a column name is “Number,” then its corresponding parameter would be “:Number”. Using matching names ensures that if a query uses its
DataSource property to provide values for parameters, it can match the variable name to valid field names.