Using parameters

SQLDB implements parameterized queries, simulating them if the native SQL client does not support parameterized queries. A parameterized query means that the SQL statement contains placeholders for actual values. The following is a typical example:

SELECT * FROM MyTable WHERE (id=:id)

The :id is a parameter with the name id. It does not contain a value yet. The value of the parameter will be specified separately. In SQLDB this happens through the TParams collection, where each element of the collection is a named parameter, specified in the SQL statement. The value can be specified as follows:

Params.ParamByname('id').AsInteger:=123;

This will tell SQLDB that the parameter id is of type integer, and has value 123.

SQLDB uses parameters for 3 purposes:

1. When executing a query multiple times, simply with different values, this helps increase the speed if the server supports parameterized queries: the query must be prepared only once.
2. Master-Detail relationships between datasets can be established based on a parameterized detail query: the value of the parameters in the detail query is automatically obtained from fields with the same names in the master dataset. As the user scrolls through the master dataset, the detail dataset is refreshed with the new values of the params.
3. Updating of data in the database happens through parameterized update/delete/insert statements: the TSQLQuery.UpdateSQL, TSQLQuery.DeleteSQL, TSQLQuery.InsertSQL properties of TSQLQuery must contain parameterized queries.

An additional advantage of using parameters is that they help to avoid SQL injection: by specifying a parameter type and value, SQLDB will automatically check whether the value is of the correct type, and will apply proper quoting when the native engine does not support parameters directly.

See also