Syntax FACTORY_DEF QueryFactory [FACTORY_NAME ] [INPUT FEATURE_TYPE [ ]* []*]* [USE_BOUNDING_BOX [(YES|NO)]] [CONTINUE_ON_READER_ERROR (YES|NO)] [FCTQUERY_INTERACTION [(EQUALS|DISJOINT|TOUCHES|CROSSES|WITHIN|OVERLAPS| CONTAINS|INTERSECTS|NONE|ENVELOPE_INTERSECTS|)]+] [QUERY_INTERACTION [(EQUALS|DISJOINT|TOUCHES|CROSSES|WITHIN|OVERLAPS| CONTAINS|INTERSECTS|NONE|ENVELOPE_INTERSECTS|)]*] [QUERY_WHERE ] [QUERY_WHERE_CLAUSE ] [TARGET_TABLE_FIELD ] [QUERYFCT_TABLE_SEPARATOR (COLON|SPACE)] [QUERY_SQL ] [QUERY_SQL_STATEMENT ] [COMBINE_ATTRIBUTE (RESULT_ONLY|QUERY_ONLY|PREFER_QUERY|PREFER_RESULT|PREFIX_QUERY)] [IGNORE_NULLS (YES|NO)] [REJECT_POINTS (YES|NO)] [QUERYFCT_ATTRIBUTE_PREFIX ] [COMBINE_GEOMETRY (RESULT_ONLY|QUERY_ONLY|AGGREGATE)] READER_TYPE READER_DATASET [READER_IDS ] [QUERYFCT_IDS +] [QUERYFCT_PROPAGATE_IDS_ON_SQL_EXECUTE (YES|NO)] [READER_PARAMS [ ]*] [READER_PARAMS_FMEParsableText [,[,,]*]] [TREAT_READER_PARAM_AMPERSANDS_AS_LITERALS (YES|NO)] [READER_DIRECTIVES [ ]*] [READER_COORDSYS ] [UNIQUE_KEYS *] [QUERYFCT_MAX_FEATURES ] [QUERYFCT_OUTPUT (BASED_ON_CONNECTIONS|SCHEMA_ONLY|DATA_ONLY) [QUERYFCT_RESULT_TAGS *] [QUERYFCT_SET_FME_FEATURE_TYPE [(YES|NO)]] [QUERYFCT_SET_FME_META_ATTRS [(YES|NO)]] [OUTPUT (QUERY|RESULT|READER_ERROR|SCHEMA|*) FEATURE_TYPE [ ]* []*]* Overview Oracle Version: Any references to Oracle 8i are also applicable to subsequent Oracle releases. This factory performs a spatial query against any FME reader. It may also be used to perform non-spatial queries, in which case it acts as a convenient way to inject features from any source dataset into the middle of a factory pipeline. For each feature this factory receives by way of its INPUT clause, it retrieves content from the specified dataset. The type query used to retrieve content from the dataset varies depends on how the QueryFactory is configured. Content retrieved from the input dataset is output via the clause corresponding to the feature's feature type if it was specified in QUERYFCT_RESULT_TAGS (see below), otherwise it is output to the RESULT clause. Query features are output via the QUERY clause. Query features are output with an attribute called _matched_records, which indicates the number of result features which resulted from the query for that query feature. Additionally, schema features are output for each feature type through the SCHEMA output clause if present and QUERYFCT_OUTPUT is not set to DATA_ONLY. See FeatureReader doc for more details. The following rules are used to determine the type of query used: If an SQL statement is specified via the QUERY_SQL clause, then the SQL statement is used to query the input dataset. The SQL statement can only be used if the FME reader supports the custom SQL statements via the fme_execute_sql setConstraints() search type. If the QUERY_SQL clause is specified, then the QUERY_INTERACTION, QUERY_WHERE and READER_IDS clauses are ignored. If one or more spatial interactions are specified via the QUERY_INTERACTION (deprecated) or the FCTQUERY_INTERACTION clause, a spatial relationship query using the geometry of the input feature is used to query the input dataset. All features which satisfy the spatial relationships are returned. The set of spatial interactions supported by the interaction clause are the same as the SpatialRelationshipFactory plus some additional predicates available on a per-format basis. However, it is not possible to combine a format-specific spatial interaction with another that is not handled first-hand by that format. For example, you cannot combine ORACLE:TOUCH with DISJOINT, since DISJOINT is not handled first-hand by Oracle. If the interaction clause is left blank (which is the default value) or set to ENVELOPE_INTERSECTS the factory will perform a search envelope query defined by the geometry of the query feature. If the incoming feature does not contain geometry or interaction clause is set to NONE, an attribute only query is performed. (For more information on spatial relationships, see Spatial Predicate Background in the SpatialFilterFactory.) Note: QUERY_INTERACTION (deprecated) is only evaluated once at the beginning the translation whereas FCTQUERY_INTERACTION is evaluated on every incoming feature and can be set to come from an attribute. FCTQUERY_INTERACTION takes precedence if both are specified. In addition, if a WHERE clause is specified via the QUERY_WHERE clause, this clause is used to further limit the dataset. The WHERE clause can only be used in conjunction with a spatial relationship query if the FME reader supports the fme_spatial_interaction setConstraints() search type (see below). If a WHERE clause is specified via the QUERY_WHERE clause, this clause is used to filter the input dataset. A WHERE clause can only be used if the FME reader supports the fme_all_features setConstraints() search type. All parameters to the QUERY_SQL, QUERY_INTERACTION, QUERY_WHERE, READER_TYPE, READER_DATASET, READER_IDS, READER_PARAMS, and READER_PARAMS_FMEParsableText clauses are treated as dynamic expressions rather than literal strings. That is, they are evaluated at the time a query feature is accepted into the factory. This allows the values actually used for these parameters to be the results of functions executed on each query feature that enters the factory. The exception to this is when TREAT_READER_PARAM_AMPERSANDS_AS_LITERALS is set to Yes, ampersands in the READER_PARAMS and READER_PARAMS_FMEParsableText clauses will be treated as literals instead of as indicating attributes (provided there is not also a function call in the same value). For example, the following factory definition will read from the dataset specified by the format, dataset, and ids attributes from the input query feature: FACTORY_DEF * QueryFactory \ INPUT FEATURE_TYPE * \ READER_TYPE &format \ READER_DATASET &dataset \ READER_IDs &ids * \ OUTPUT RESULT FEATURE_TYPE * Unless changed on the OUTPUT clause, all features resulting from the query will have the feature type given them by the source dataset reader. The COMBINE_ATTRIBUTES clause determines which attributes are returned on features emitted from the OUTPUT clause. Acceptable values for this clause are: RESULT_ONLY: Only attributes originating from the input dataset are placed on the result features. QUERY_ONLY: Only attributes from the query feature are placed on the result features. PREFER_QUERY: Attributes from the query feature are merged with the attributes originating from the input dataset. In the event of a conflict between attribute names, attribute values are taken from the query feature. PREFER_RESULT: Attributes from the query feature are merged with the attributes originating from the input dataset. In the event of a conflict between attribute names, attribute values are taken from the input dataset. PREFIX_QUERY: Attributes from the query feature are prefixed with the value of QUERYFCT_ATTRIBUTE_PREFIX. The IGNORE_NULLS clause determines whether attributes with a value of null will partake in conflict resolution when merging. The REJECT_POINTS clause determines whether a spatial query requires geometries with nonzero areas. That is, point geometries will be rejected. The QUERYFCT_ATTRIBUTE_PREFIX contains the string to be prefixed onto query attribute names when COMBINE_ATTRIBUTES is set to PREFIX_QUERY. The COMBINE_GEOMETRY clause determines which geometry is returned with features emitted from the OUTPUT clause. Acceptable values for this clause are: RESULT_ONLY: Only geometry originating from the input dataset is placed on result features. QUERY_ONLY: The geometry from the query feature is placed on all result features. AGGREGATE: Each result feature contains an aggregate of the geometry originating from the input dataset and the geometry from the source feature. The QUERYFCT_OUTPUT clause specifies what will be output, or more precisely what will not be read. Acceptable values are BASED_ON_CONNECTIONS (default), SCHEMA_ONLY, DATA_ONLY. See FeatureReader docs for their meanings. QUERYFCT_RESULT_TAGS lists additional output tags for data features. If a feature read has a feature type that matches result tag, it will be output through the corresponding clause. QUERYFCT_RESULT_TAGS should be FMEParsableText-encoded and space-separated. The corresponding tags in the output clauses should be FMEParsableText-encoded as well. To ensure the 'fme_feature_type' attribute is set for all result features, set the QUERYFCT_SET_FME_FEATURE_TYPE clause to YES. To ensure 'fme_feature_type', 'fme_basename', and 'fme_dataset' attributes are set for all result features, set the QUERYFCT_SET_FME_META_ATTRS clause to YES. Reader Specification ~~~~~~~~~~~~~~~~~~~~ The READER_TYPE clause specifies the FME reader type from which to retrieve features. The QueryFactory may be used in conjunction with any of FME's readers. If a spatial relationship query is being used against the input dataset, and the FME reader supports the fme_spatial_interaction setConstraints() search type, the spatial relationship query is optimized by executing the query directly against the input dataset. The spatial relationship query is otherwise transparently handled via an internal instance of a SpatialRelationshipFactory. Otherwise, if a search envelope query is being used, readers that support the SEARCH_ENVELOPE directive (Oracle8i, ESRI Shape, and ESRI SDE, for example) will perform spatial envelope queries particularly efficiently, but other readers will also work. The READER_DATASET keyword is used to specify the input dataset. It corresponds to the dataset normally specified for the given reader.If the READER_COORDSYS is specified then the coordinate system of the resulting geometries will be tagged with the specified coordinate system. The optional QUERYFCT_IDS clause is used to select specific feature types to read within a dataset. If QUERYFCT_IDS is populated, features that do not have a specified feature type will be filtered out. Feature type names that contain spaces or double-quotes should be quoted with double quotes with inner double-quotes escaped by a second double quote. QUERYFCT_TABLE_SEPARATOR does not affect this clause; the delimiter is always a space. (eg "table one" "3""Table" if table name is 3"Table). If QUERYFCT_PROPAGATE_IDS_ON_SQL_EXECUTE is specified, then QUERYFCT_IDS will be propagated to the reader when executing SQL. *** READER_IDS and TARGET_TABLE_FIELD are now deprected. Use QUERYFCT_IDS instead *** The optional READER_IDS clause may be used to select specific feature types within a dataset. If READER_IDS is populated and not overridden, features that do not have a specified feature type will be filtered out. If specified, the feature types specified in TARGET_TABLE_FIELD override READER_IDS and allows each query feature to specify which tables or files should be searched by setting the named attribute to the desired tables or file names. When more than one table or file name are specified, parameters should be separated by the character specified in QUERYFCT_TABLE_SEPARATOR. If SPACE is the table separator, then feature type names that contain spaces or double-quotes should be quoted with double quotes with inner double-quotes escaped by a second double quote (eg "table one" "3""Table"). *** Additional parameters may be given to the data readers using the READER_PARAMS_FMEParsableText (or READER_PARAMS) clause. They are passed verbatim to the reader being used to perform the data retrieval as runtime macros. Additional reader directives can be given through the READER_DIRECTIVES clause. Reader directives govern how a reader is created and configured (see IFMESession::createReader documentation for details). If READER_PARAMS(_FMEParsableText) is also specified, then they will replace the value of RUNTIME_MACROS in the directives. If READER_COORDSYS is also specified, it will replace the value of COORDSYS in the directives. The following example performs spatial interaction queries against an Oracle Spatial table. The input query feature supplies the geometry which will be used during spatial interaction testing. The result features contain geometry from the input dataset which passed the spatial interaction tests. Attributes are copied from the query feature to the result feature if they did not previously exist on the result feature. FACTORY_DEF * QueryFactory \ INPUT FEATURE_TYPE * \ QUERY_INTERACTION INTERSECTS \ COMBINE_ATTRIBUTES PREFER_RESULT \ COMBINE_GEOMETRY RESULT_ONLY \ READER_TYPE ORACLE8I \ READER_DATASET kdb123a \ READER_IDS ROADS \ READER_PARAMS \ USER_NAME fme \ PASSWORD testsuite \ OUTPUT RESULT FEATURE_TYPE * \ The following example performs spatial envelope queries against an Oracle8i Spatial table. The input query feature supplies the search envelope for the query. FACTORY_DEF * QueryFactory \ INPUT FEATURE_TYPE * \ READER_TYPE ORACLE8I \ READER_DATASET kdb123a \ READER_PARAMS \ SERVER_TYPE ORACLE8I \ USER_NAME fme \ PASSWORD fmepass \ READER_DATASET kdb123a \ READER_IDs ROADS \ OUTPUT RESULT FEATURE_TYPE * Consecutive Queries ~~~~~~~~~~~~~~~~~~~ The QueryFactory performs a dataset read for each feature that enters its INPUT clause. If given more than one query feature, the factory may possibly return any feature in the dataset any number of times. This effect can be avoided. The UNIQUE_KEYS clause specifies a set of attributes that uniquely identify a feature within the source dataset. If any unique keys are specified, the corresponding attribute values are inspected on each feature returned from the reader. Only the first feature with a given combination of attribute values will be emitted from the QueryFactory. Multiple SQL Commands ~~~~~~~~~~~~~~~~~~~~~ The QueryFactory supports multiple SQL commands in the QUERY_SQL clause. Multiple SQL commands can be delimited by a character specified using the keyword FME_SQL_DELIMITER, embedded at the very beginning of the SQL statement. The single character immediately following this keyword will be used to split the SQL which will then be sent to the database for execution. (Note: Include a space before the character.) An individual statement may be preceded with a hyphen, indicating that errors should be ignored. Number of Features to Read ~~~~~~~~~~~~~~~~~~~~~~ Setting QUERYFCT_MAX_FEATURES will limit the number of features to read per input feature to the number specified. Numbers less than one will be treated as read everything. TO BE RESOLVED CONTINUE_ON_READER_ERROR, QUERY_WHERE_CLAUSE, QUERY_SQL_STATEMENT, USE_BOUNDING_BOX clauses and READER_ERROR output tag added to Syntax section above, but not documented.