How You Can Use Parameterized Queries
To allow greater flexibility, the text of the SQLSelect's Query attribute can contain parameter substitutions. There are two types of query parameters:
Parameters evaluated before the query is sent to the DBMS server
These parameters involve straight textual substitution and can appear anywhere in the text, even inside quoted strings and substituting for SQL keywords and operators. They cannot, however, be used with repeat queries. These parameters begin with a leading ampersand (&).
The following example contains parameterized column names and a parameterized table name:
ss.Query.Value = 'select &(col1), &(col2),
&(col3) ' +'from &tablename';
The variables representing the column names and table name must be visible in the scope specified in the SetCols or Open method. When the ampersand is followed by an expression, the expression must be enclosed within parentheses as shown in the following example:
ss.Query.Value = 'select &(col1 + col2/2) ' +
'from &tablename';
Parameters interpreted by the Ingres server
These parameters can appear only where the Ingres DBMS accepts parameters (that is, in the same places as constants). They begin with a leading colon (:).
The following example evaluates an expression and uses the result as the value for the where clause:
select number from infotbl
where number = :(var1 + var2);
The following example shows how you can set the Value attribute of an SQLSelect with a parameterized where clause:
initialize () =
declare
ss = SQLSelect;
wclause = varchar(60) not null;
...
enddeclare
begin
ss.Query.Value = 'select * from customer
&(wclause)' +' order by cname';
You can set the where clause later. The value of the where clause is assigned after the SQLSelect is opened and evaluated and before the query is sent to the DBMS server.
For a discussion of setting the Query's Value attribute, see
How You Can Specify the Query (see
How You Can Specify the Query).