Working with parameters for external data connections
To speed up the query to the data from externally integrated data sources, parameters can be defined that always query only the passed object in the database. This makes sense especially if there are many records in the included tables and feature classes. The parameter is defined directly in the SQL statement. Using an Oracle connection as an example, this is set up as follows:
Extend the SQL statement by the field to be passed as a parameter. This should be a unique key field.
Select FieldX, FieldY, FieldZ from Table1
where fieldX = :param
The parameter is set for the Oracle connection with :+Name is defined.
|
The parameter is defined differently depending on the database type and driver. •With an OLEDB connection to an Oracle Database, a ? is used as parameter. •For a SQL connection to an SQL database, an @ is used as a parameter. |
In addition to the SQL statement, the parameter must be defined as a field in the table. Thereby Name, Expression, Size and Type assigned. Expression defines the field from which the parameter is passed. Type and Size define the data type and the length of the field.
Define parameters
In order to ensure that the data with parameters is retrieved correctly, it is necessary to set the option ReconnectOnEachRow to True in the properties of the data source. If this option is not set to True or True, the connection to the data source will be made only once, which would mean that only the first row is returned.
Data source properties