The Query object is used to access and set properties of a source query. It can be used only in scripts attached to queries.
Syntax
Query.[property
| method]
Parameters
None
Example
The following example is
the Before Query script. It adds additional selection criteria to a
query used for a vendor integration.
Query.AdditionalCriteria = “State = 'ND'”
The AdditionalCriteria property allows you to add additional selection criteria to a text query or simple ODBC query.
Syntax
Query
Parameters
object—The query object to which additional
selection criteria is applied.
criteria—A string containing the additional criteria to apply.
Comments
You must set the
AdditionalCriteria property in the Before Query script.
If you refer to a column whose name contains spaces or other special characters, be sure to enclose the column name in square brackets [].
You can use parentheses, logical AND, logical OR, Like and wildcard (%) operators in the criteria string, similar to how you use them in the Query Properties window.
Example
object.AdditionalCriteria
[= criteria]
object—The query object to which additional selection criteria is applied.
Query.AdditionalCriteria = “[Customer Name] Like 'A%'”
The OverrideCriteria property allows you to replace the selection criteria used for a text query or simple ODBC query.
Syntax
object.OverrideCriteria
[= criteria]
Parameters
object—The query object for which the
selection criteria will be replaced.
criteria—A string containing the criteria.
Comments
You must set the
OverrideCriteria property in the Before Query script.
If you refer to a column whose name contains spaces or other special characters, be sure to enclose the column name in square brackets [].
You can use parentheses, logical AND, logical OR, Like and wildcard (%) operators in the criteria string, similar to how you use them in the Query Properties window.
Example
The following example
replaces the selection criteria for a query to include only
customers from North Dakota.
Query.OverrideCriteria = “State = 'ND'”
The OverrideFileLocation property allows you to specify a different file location for the source file used for a text query.
Syntax
object.OverrideFileLocation
[= path]
Parameters
object—A query
object for text query.
path—A string containing the new location of the source file. Note that this is the location of the source file. The name of the source file must remain the same.
Comments
You must set the
OverrideFileLocation property in the Before Query
script.
Example
The following example is
a Before Query script. It reads the value of the Path variable,
which was set in the Before Integration script. Then it uses the
path information to override the location of the source file for
the query.
Query.OverrideFileLocation = GetVariable(“Path”)
The QueryName property returns a string containing the name associated with a query object.
Syntax
object.QueryName
Parameters
object—A query
object.
Example
The following example is
the Before Query script for a query. It retrieves the name of the
query and sets a variable to be used later in the integration.
SetVariable “QueryName”, CStr(Query.QueryName)
The DeleteSourceFile method allows you to delete the source file used for a text query.
Syntax
object.DeleteSourceFile({suppress})
Parameters
object—The
query object for which the source file will be deleted.
suppress—An optional boolean indicating whether the default confirmation warning will be suppressed. True indicates the warning will be suppressed.
Comments
You can use the
DeleteSourceFile method in the After Query script.
Example
The following example is
the After Query script for a query. It deletes the source file from
the query.
query.DeleteSourceFile()
The DeleteSourceRecords method allows you to delete the source records from a simple ODBC query that was used for an integration.
Syntax
object.DeleteSourceRecords({suppress})
Parameters
object—The
query object for which the source records used will be deleted.
suppress—An optional boolean indicating whether the default confirmation warning will be suppressed. True indicates the warning will be suppressed.
Comments
You can use the
DeleteSourceRecords method in the After Query script.
If the integration was completed successfully, you may want to delete the records from the source query if they are no longer required.
Example
The following example is
the After Query script for a query. It deletes the source records
from the query, without prompting the user.
Query.DeleteSourceRecords(True)
The ExecuteSQL method allows you to execute a SQL statement for an ODBC query that was used for an integration.
Syntax
object.ExecuteSQL(SQL_statement)
Parameters
object—The
query object for which a SQL statement will be executed.
SQL_statement—A string containing the SQL statement to execute.
Comments
The ExecuteSQL
method is typically used in the After Query script to complete any
post processing, such as updating a status column, necessary for
the data source.
The SQL statement you execute should not produce a result set.
Example
The following example is
the After Query script for an integration. It executes a SQL
statement that updates the IntStatus column of the Vendors table to
indicate which items were imported by Integration Manager.
query.ExecuteSQL(“Update Vendors Set IntStatus = 'Yes' where IntStatus = '' “)