Query Attribute
Data Type: StringObject
4GL Access: RW
The Query attribute contains the SQL query text.
The query text can contain parameter substitutions. The parameters are OpenROAD expressions evaluated with reference to a given scope. Parameters are evaluated in the DataStream class methods
SetCols (see
SetCols Method) and
Open (see
Open Method).
There are two forms of query parameters:
• Parameters that involve straight textual substitution are evaluated by the client before the query is sent to the DBMS server.
These text strings can appear anywhere in the text, including inside single- or double-quoted strings. They can be substituted for any portion of the SQL query, including keywords and operators. They cannot be used with repeat queries.
These parameters begin with a leading ampersand (&) followed by a parenthesized expression. For example:
select number from infotbl where number
= &(var + 1);
This example substitutes the value of "var + 1" into the query text prior to being sent to the DBMS.
In the following example:
select txtcol from infotbl where txtcol =
'&(var)sfx &&';
the parameter substitution occurs within a quoted string. The value of the text variable, var, is concatenated with the letters sfx inside the string. Other similar mechanisms can be used to concatenate substitutions together with text outside of text strings.
A double ampersand within quoted strings represents a literal single ampersand. In the preceding example, the DBMS receives a literal string ending in an ampersand.
Since parameters evaluated by the client are strictly textual, the only restrictions are:
– Do not pair a quotation mark within a substitution with one outside it.
– Do not place a parameter within another parameter.
– You can substitute these parameters for table and column names in the query. However, you should use correlation names and as names that are not parameterized to facilitate mappings between entries in the Columns array with columns in the query. For example:
select &(var1) as colname from &(var2) t
where ...
• Parameters that are sent to and interpreted by the DBMS can appear only in places where the DBMS accepts parameters (where constants can be used).
These parameters begin with a leading colon (:), followed by the appropriate variable or by a parenthesized expression. For example:
select empname from emptbl where
empname = :arr[i].attr;
select number from infotbl where
number = :(var1 + var2);
In the first example, the attr attribute of the i'th element of the arr array is evaluated and replaced into the query. In the second, the result of the entire expression, var1 + var2, is evaluated and replaced. Without the parentheses, the parameter terminates at anything other than an identifier, a dot, an empty space, or an expression enclosed in square braces.
Colons are not interpreted inside strings that are enclosed within single or double quotes.
Parameters used by the SetCols method are substituted textually into the query text to obtain column information.
Parameters used by the Open method are sent to the DBMS as parameters, providing two advantages:
– On repeat queries, the parameters represent the variable parts of the query.
– The DBMS rather than the client handles data type conversion, allowing it to process floating point values with regard to roundoff and precision issues.
For an example of using a parameterized query, see the Programming Guide.