SQL
Returns or sets a string that contains an SQL statement SELECT upon which the query is built. If the query has been built constructively (using methods and properties of setting up joins, search, sort, and calculations), the property returns an empty string. In this case, it is impossible to set the property. You should create a query by the ExternalDatabase::CreateQuery method with the argument equal to Null.
The string being set must contain a single SQL statement SELECT. If the SQL statement SELECT has errors, it is not set (the query is based on a previously set SQL statement SELECT).
After an SQL statement has been set successfully, the Query::SQLParameters collection of parameters becomes empty. If the SQL statement SELECT contains parameter markers, it is necessary to specify its parameters by creating SQLParameter objects, setting their values, and adding them to the Query::SQLParameters collection.
Identifiers in SQL statement SELECT (names of tables, fields, etc.) containing special characters must be enclosed in identifier quote characters. Every identifier in the SQL statement should be passed to the ExternalDatabase::AsIdentifier method as a parameter because a set of special characters in identifiers and quote characters depend on an ODBC data source. The method (using information from the ODBC data source) returns a string corresponding to an identifier and enclosed in quote characters if the identifier has special characters.
To get query records connected with a feature, the GIS kernel must embed a fragment in an SQL statement that take into account the field value of direct link and field values of indirect link. Embedding the fragment is not a problem for a query built constructively because the GIS kernel generates the whole SQL statement itself. For queries based on a directly specified SQL statement SELECT, the GIS kernel has no possibility to embed a required fragment into the SQL statement. The reason is that an SQL statement specified by a user can be very complex and as a result, it makes it impossible to get query records connected with the feature. To solve this problem, the GIS kernel provides the mechanism of link escape sequence. If a link escape sequence is absent in the SQL statement, it is impossible to get query records connected with a feature. However, it is possible to get features connected with a record even though a link escape sequence is absent.
A link escape sequence is a string of characters that a user embeds into the SQL statement SELECT upon which the query is built. The GIS kernel eliminates the link escape sequence from the SQL statement SELECT when it is necessary to get query records. When it is necessary to get records connected with a feature, the GIS kernel finds the link escape sequence and replaces it with a fragment that takes into account the field value of direct link or values of fields for the indirect link. Thus, a link escape sequence is a place indicator for inserting a condition of getting records connected with a feature into the SQL statement SELECT. The syntax of a link escape sequence is given below:
{link <bundle> <link fields>}
<bundle> - a string of WHERE or AND. If <bundle> is absent, the AND bundle is used by default.
<link fields> - a field name of direct link or a sequence of field names for the indirect link separated by commas. The order of field names must correspond to the order of field names for indirect link specified when setting a link between the query and a feature type.
The field names in a link escape sequence must coincide with names of fields used in the SQL statement SELECT (including used name qualifiers and/or aliases of field names).
The field names may be absent in a link escape sequence. In this case, names of corresponding query fields are used. The list of field names may be incomplete in a link escape sequence. In this case missing fields will be taken from the query fields. It is possible to specify none or some of fields in a link escape sequence if the field names used in the SQL statement SELECT do not contain qualifiers and do not have aliases. Otherwise, a syntax error may occur in the SQL statement SELECT.
You should correctly set the position of a link escape sequence in the SQL statement SELECT.
If the SQL statement SELECT does not contain the WHERE clause, you must set the link escape sequence in the position used for the WHERE clause. The bundle must contain the value WHERE in the link escape sequence.
If the SQL statement SELECT contains the WHERE clause, you must set the link escape sequence in the position of conjunction between a search condition specified by the WHERE clause and a link condition that will replace the link escape sequence while getting records connected with a feature. The bundle must contain the value AND (or absent) in the link escape sequence.
Samples of a link escape sequence:
SELECT FIELD1,FIELD2
FROM TABLE1 {link WHERE}
In the sample above the <bundle> contains the value WHERE in the link escape sequence because the WHERE clause is absent in the SQL statement SELECT. The field names are absent in the link escape sequence because the field names in the SQL statement SELECT were specified without qualifiers and aliases.
SELECT FIELD1 AS F1, FIELD2 AS F2
FROM TABLE1 {link WHERE F1,F2}
In the sample above the <bundle> contains the value WHERE in the link escape sequence because the WHERE clause is absent in the SQL statement SELECT. The field names are present in the link escape sequence because the field names in the SQL statement SELECT were specified with aliases.
SELECT FIELD1,FIELD2
FROM TABLE1
WHERE FIELD1='Smith' {link}
In the sample above the <bundle> is absent in the link escape sequence (the default bundle AND is used) because the WHERE clause is present in the SQL statement SELECT. The field names are absent in the link escape sequence because the field names in the SQL statement SELECT were specified without qualifiers and aliases.
The property supports read/write access.
 |  |
