Syntax FACTORY_DEF SDE30QueryFactory [FACTORY_NAME ] [INPUT FEATURE_TYPE [ ]* []*]* SDE_SERVER_FIELD SDE_INSTANCE_FIELD SDE_DATASET_FIELD SDE_USERID_FIELD SDE_PASSWORD_FIELD SDE_VERSION_NAME_FIELD SDE_TARGET_TABLE_FIELD [SDE_SEARCH_METHOD_FIELD ] [SPATIAL_FILTER_LAYER_FIELD SPATIAL_FILTER_ID_FIELD SPATIAL_FILTER_METHOD_FIELD ] [SPATIAL_FILTER_TRUTH_FIELD ] [FEATURE_TYPE_FIELD ] [WHERE_CLAUSE_FIELD ] [CORRIDOR_DISTANCE_FIELD ] [MAX_NUM_FIELD ] [QUERY_MODE (QUERY|DELETE|UPDATE)] [OUTPUT_DUPLICATES [(yes|no)]] [SEARCH_ORDER (OPTIMIZE|SPATIAL_FIRST|ATTRIBUTE_FIRST)] [GET_SPATIAL_RELATIONS [(yes|no)]] [COMBINE_ATTRIBUTES (ADD|MERGE|NONE|RESULT_ONLY|QUERY_ONLY|PREFER_RESULT)] [COMBINE_GEOMETRY (ADD|MERGE|NONE|RESULT_ONLY|PREFER_RESULT|PREFER_QUERY|AGGREGATE)] [SILENT_MODE ] [REJECTED_PIPELINE_DIRECTORY ] [REMOVE_TABLE_QUALIFIER [(yes|no)]] [CLEAR_MISSING_DATA [(yes|no)]] [OUTPUT (QUERY|RESULT) FEATURE_TYPE [ ]* []*]* Overview Note: This factory is not supported by FME Base Edition. This factory can only be used in conjunction with ESRI's Spatial Database Engine Version (SDE) 3.x/8.x/9.x. It is capable of performing queries with spatial and/or attribute components. Queries are defined by the features received on the input clause, allowing the FME to perform sophisticated SDE queries and deletions. The SDE30QueryFactory is driven by input features that contain the specifics of the query to be performed. For example, a simple user interface could create such query features. These query features could then be run through the FME either immediately or as a batch job when SDE activity from connected users is low. A query is defined by each input feature received and has the following properties: Tip: When debugging an SDE30QueryFactory specification, use the MAX_NUM_FIELD value to avoid consuming large amounts of system resources with erroneous searches. Each feature defines the SDE upon which the query is to be performed enabling the FME to dynamically connect to any SDE on the network. Internally, the FME uses SDE connections sparingly to ensure there are no extraneous connections to any SDE being used by the FME. As with the SDE30 reader, a query may specify a multi-table join operation. See the ESRI SDE 3.x/8.x Reader/Writer chapter in the FME Readers and Writers manual for a description of this type of operation. The underlying mechanism is identical for both the SDE 30 reader and the SDE30QueryFactory. In addition to simply retrieving features, the SDE30QueryFactory can also be used to delete features from the database as they are retrieved. This allows the FME to perform deletions from SDE 3.X based on attribute and spatial constraints. When operated in delete mode, the SDE30QueryFactory is still able to return the features that were deleted from the SDE for further processing. Tip: Features that leave this factory are treated by FME as if they originated from the reader being used to drive the query. SDE30QueryFactory Processing ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ A feature flows into the SDE30QueryFactory either from the reader module or from an upstream factory. The features the SDE30QueryFactory accepts are called query features as they contain the parameters for the query to be performed. The SDE30QueryFactory uses these parameters to issue a query against the SDE. As features are returned from the SDE, they are passed out of the SDE30QueryFactory without delay for immediate processing by the rest of the system. After the last feature for a query has been returned by the SDE, the SDE30QueryFactory then passes the query feature to the rest of the FME system via the OUTPUT QUERY clause. The next query is started when the next query feature arrives. An attribute named _matched_records will be added to each query feature, specifying how many database rows the query matched. Using Versioning with the SDE30 Reader, Writer, and QueryFactory ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Database states will be created by the FME only when updating/inserting/deleting from a versioned table or layer. Therefore, only the SDE30 writer or an SDE30QueryFactory in DELETE or UPDATE mode is able to create a database state. The SDE reader and the SDE30QueryFactory in QUERY mode will never create a database state. All changes made during a single translation to a specific version (on the same SDE), even if they were made by different SDE30QueryFactories or different SDE writers, are placed into one (and the same) child state. Versioning must be used when updating/inserting/deleting from a versioned table or layer. If versioning is not used in these cases, then an "Insufficient permissions" error will be generated and the translation stopped. Assumptions ESRI SDE 3.x/8.x/9.x is installed on the database server you wish to retrieve data from. Clauses The following clauses are used to configure the SDE30QueryFactory for a retrieval operation: SDE_SERVER_FIELD The attribute containing the name of the server machine used to perform the query operation. SDE_INSTANCE_FIELD The attribute containing the name of the sde instance used to perform the query operation. Typically the value is port:5151 (or for older SDEs the typical value is esri_sde). SDE_DATASET_FIELD The attribute containing the name of the dataset upon which the query is performed. This is a required field. For SDE clients based on Oracle, any value will suffice. SDE_USERID_FIELD The attribute containing the userid used to perform the query. SDE_PASSWORD_FIELD The attribute containing the user's password. SDE_VERSION_NAME_FIELD The attribute containing the version to which the FME is to connect. The version must already exist and the current user must have privileges set so that it can access the version. If the SDE_VERSION_NAME_FIELD directive is not used, then the factory connects to the version SDE.DEFAULT. This clause is only applicable when dealing with versioned tables and layers. SDE_TARGET_TABLE_FIELD If more than one table is to be specified in the field, then the table names must be separated by colons, ":". If there are joined tables, then the compound tables are described by including the secondary tables in parentheses. See the example outlined in Multi-table Join Example, in the ESRI SDE 3.x/ArcSDE 8.x Reader/Writer chapter of the FME Readers and Writers. SDE_SEARCH_METHOD_FIELD The attribute identifying the type of search method used to perform the query. If this is not specified then no spatial aspect of the query will be posed. The following search methods are permitted: SDE_ENVELOPE The envelope of the returned feature overlaps or touches the envelope of the search feature. SDE_COMMON_POINT The search feature shares at least one common point. SDE_LINE_CROSS The search feature and the returned feature have intersecting line segments. SDE_COMMON_LINE The search feature and the returned feature share one or more common line segments. SDE_CP_OR_LC The search feature and the returned feature have line segments that intersect or have a common point. SDE_AI_OR_ET The search feature and the returned area feature edge touch (ET) or their areas intersect (AI). SDE_AREA_INTERSECT The search feature and the returned feature's area intersect. SDE_AI_NO_ET The returned feature and search feature have intersecting areas with no edge touching. One feature is therefore contained in the other. SDE_CONTAINED_IN The search feature is contained in the returned feature. For area features, this is clear. If search feature is a line, then a linear feature will be returned when the search feature path is included in returned feature. If search feature is a point, then the search feature will be one of the returned features vertices. SDE_CONTAINS The returned feature is contained by the search feature. If both features are linear features, then the returned feature must lie on the search feature's path. Point features that lie on a search feature vertex are also returned. SDE_CONTAINED_IN_NO_ET The returned feature must be an area feature that does not touch or share a vertex with the search feature. The returned feature contains the search feature. SDE_CONTAINS_NO_ET The returned feature is contained within the search feature. The returned features cannot touch the edge of, or share a vertex with, the search feature. SDE_POINT_IN_POLY The returned feature contains the first point of the search feature. SDE_IDENTICAL The returned feature has the same feature type and geometry. This is used to find duplicate data. SPATIAL_FILTER_TRUTH_FIELD This clause specifies the query feature attribute that indicates if features returned during the query will or will not satisfy the spatial constraint. This allows you to select features that are not contained by another, for example. The value contained in the query feature field must be either TRUE or FALSE. Default: TRUE WHERE_CLAUSE_FIELD The name of the feature's attribute that contains the WHERE clause used to perform attribute filtering. Note: Double quotation marks are required around the WHERE clause, especially if you are querying across multiple fields: e.g. "OID = &OID and PARK = 'STANLEY'" CORRIDOR_DISTANCE_FIELD This clause is specified if a buffer is to be constructed around the search feature. The clause specifies the attribute containing the size of the buffer to be used. This buffered feature is then used as the search feature. MAX_NUM_FIELD This clause identifies the attribute which specifies the maximum number of features that can be returned by the query. This is used to ensure that an unexpectedly large number of result features are not accidentally returned. QUERY_MODE Indicates if the features that satisfy the query are to be deleted from the SDE database, updated to the SDE database, or just retrieved from the SDE database. QUERY - features are retrieved from the database. DELETE - features are deleted as they are retrieved from the database. Currently this can only be used on tables that have a spatial column. To delete rows from tables that are not spatially enabled the user must use another method such as @SDEsql() or @SQL(). UPDATE - features are retrieved from the database. Then the attributes of the query feature are copied onto the result feature. If the same attribute exists on the query feature and the result feature, then the value on the result feature is overwritten with the value on the query feature. If the query feature contains some geometry, the geometry is copied over onto the result feature, overwriting any existing geometry originally found on the result feature. For the UPDATE mode to work, the tables being updated must either contain a spatial column or must be registered as multiversioned. If the clause OUTPUT_DUPLICATES is not specified, then if the same feature is retrieved from SDE database (by different query features), only the first update will be made. Subsequent updates will only be made if the OUTPUT_DUPLICATES clause is specified. WARNING! When copying features from one SDE to another SDE (i.e., when the query feature is a feature retrieved from a different SDE database), and when the result features come from a multiversioned table, the object ID field must be removed first from the query feature (if it exists), so that it doesn't overwrite the object ID field on the result feature. Range: QUERY | DELETE | UPDATE Default: QUERY OUTPUT_DUPLICATES If specified (with no argument or with an argument of yes), the factory will not ensure that each spatial feature is updated only once and/or output only once (depending on whether the QUERY_MODE is set to QUERY or UPDATE and on whether the factory is outputting result features). The default behaviour for the factory is to keep track of the spatial features. In the event that a single factory performs multiple queries, it ensures that each spatial feature is only updated and/or output once. Specifying OUTPUT_DUPLICATES removes this check and results in duplicates being updated and/or output. SEARCH_ORDER Specifies the manner in which the search is performed. If it is not specified, or OPTIMIZE is specified, then the SDE engine decides how to perform the search. If ATTRIBUTE_FIRST is specified, then the attribute portion of the search is performed first and then the spatial component is performed on the set resulting from the attribute set. If SPATIAL_FIRST is specified, then the spatial search is performed first and then the attribute search is performed on the resulting set. This is useful if the OPTIMIZE setting makes the wrong choice and you want to force the search to be performed in a different order. Range: OPTIMIZE | SPATIAL_FIRST | ATTRIBUTE_FIRST Default Value: OPTIMIZE GET_SPATIAL_RELATIONS When specified (either with no argument or with an argument of yes), this clause results in each output feature having its relationship with the query feature returned in an attribute. Using this clause slows the performance of the factory, since it must do more computation. One common use of the relationship that will be returned is to reduce the number of features sent to the ClippingFactory if SDE data is then to be clipped. In such a case, those features completely inside of the query do not need to be clipped. The attributes that are added to the features and their meanings are given below. Each attribute has the value TRUE or FALSE assigned to it. It is important to note that there may be more than one relation set to TRUE. sde30_embedded_pt: the returned feature has a vertex embedded in the of the query feature. sde30_line_cross: the returned feature crosses the query feature. sde30_common_pt: the returned feature has a common point with the query feature. sde30_embedded_pt: the returned feature has a vertex embedded in the of the query feature. sde30_cbound_diff: the returned feature has a common edge with the query feature which is in the opposite direction. sde30_parallel_overlapping: the returned feature has a common edge with the query feature. sde30_identical: the returned feature is identical to the query feature. sde30_area_intersects: the returned feature intersects the query feature. sde30_interior_intersect: the interior of the returned feature intersects the interior of the query feature. sde30_boundary_intersect: the boundary of the returned feature intersects the boundary of the query feature. sde30_prim_lep_interior: the end point of the query feature touches the interior of the returned feature. sde30_sec_lep_interior: the end point of the returned feature touches the interior of the query feature. sde30_prim_contained: the query feature is contained in the returned feature. sde30_sec_contained: the returned feature is contained in the query feature. COMBINE_ATTRIBUTES When the value specified is RESULT_ONLY, result feature attributes are based solely on the query results. When the value specified is PREFER_RESULT, result feature attributes are a combination of both the query results and query feature's attributes. If there is a conflict, attribute values taken from the query results. When the value specified is PREFER_QUERY, result feature attributes are a combination of both the query results and the query feature's attributes. If there is a conflict, attribute values are taken from the query feature. Default Value: NONE COMBINE_GEOMETRY When the value specified is RESULT_ONLY, result feature geometry is taken from the query results. When the value specified is QUERY_ONLY, result feature geometry is taken from the query feature. When the value specified is PREFER_RESULT, result feature geometry is taken from the query results, if the query results include geometry. Otherwise, result feature geometry is taken from the query feature. When the value specified is AGGREGATE, result feature geometry is an aggregate of the geometry from the query results and the geometry from the query feature. Note: If QUERY_MODE is UPDATE, specifying COMBINE_GEOMETRY has no effect. Default Value: NONE SILENT_MODE Specifies the number of table queries to log. (A table query is a query made to a single table. If the target table field on a query feature contains 3 tables, then 3 table queries will be made for that query feature. Each table query generates 2 log messages.) If the value is 0, only one summary message explaining how many table queries have been suppressed will be logged. If the value is -1, all table queries will be logged. Any positive integer value can be used to specify a certain number of queries to log. Default Value: 20 REJECTED_PIPELINE_DIRECTORY The absolute or relative path of a directory containing pipeline files. If a relative path and the command-line FME are used, then the path is relative to the location where FME is called from. If a relative path and the Universal Translator are used, then the path is relative to the location of the mapping file. If the path contains spaces, it should be double-quoted. The pipeline in the designated directory will be used when QUERY_MODE is UPDATE to process any feature that fails to convert successfully to an SDE shape, or any feature that is rejected by the database. The first feature returned from the pipeline is then used to retry the failed operation. REMOVE_TABLE_QUALIFIER Specifies whether to keep or remove the table name prefix. The default value is NO. Possible values are YES and NO. If the ArcSDE resides on a database (that is, MS SQL Server) where a specific value for database is set, then the full name for a table is ... If the ArcSDE is located on a database (that is, Oracle) that does not require the database field, then the full name of a table is .. Setting this keyword to YES indicates that the reader should return the table name without any prefixes. This is useful when: creating a workspace that will be passed on to another organization using the same table names, performing a translation to another database format but with a different user name, and when writing to a file-based format but not wanting the prefix in the name of the feature type. When this keyword is set to YES during the generation of a mapping file or workspace, the source feature types will be the table names without any prefix; otherwise, they will contain the owner name as a prefix. It is recommended that this keyword not be changed in value after generating the mapping file/workspace as it is possible for no features to be successfully passed onto the writer (since the writer is expecting feature types with different names). Note: Even when REMOVE_TABLE_QUALIFIER is set to YES, if the table is owned by a user other than the current user, the prefix will not be dropped so that the reader will find the correct table; however, the prefix will still be dropped. CLEAR_MISSING_DATA Valid values for this clause are YES and NO. The default value is NO. When set to YES, attributes not found on the query feature will be set to NULL on the feature(s) to be updated in ArcSDE. Likewise, if the query feature has no geometry, then the corresponding feature(s) to be updated will be given a nil geometry. When this clause is set to YES, make sure that, where appropriate, your columns allow NULL values. Connecting to SDE There are two possible ways to connect to SDE: connecting to the SDE service, which in turn communicates with the underlying database (a three-tier architecture), and connecting to the underlying database directly (a two-tier architecture). With a direct connection (the two-tier architecture), SDE does not need to be installed and an ArcSdeServer license is only needed if writing to the database; reading does not require a license. Regular Connection ~~~~~~~~~~~~~~~~~~ The clauses needed for a regular connection are as follows: SDE_DATASET_FIELD The attribute containing the name of the dataset upon which the query is performed. For SDE clients based on Oracle, any value will suffice. SDE_SERVER_FIELD The attribute containing the name of the server machine used to perform the query operation. SDE_INSTANCE_FIELD The attribute containing the name of the sde instance used to perform the query operation. Typically the value is port:5151 (or for older SDE's the typical value is esri_sde). SDE_USERID_FIELD The attribute containing the userid used to perform the query. SDE_PASSWORD_FIELD The attribute containing the user's password. SDE_VERSION_NAME_FIELD The attribute containing the version to which the FME is to connect. The version must already exist and the current user must have privileges set so that it can access the version. If the SDE_VERSION_NAME_FIELD directive is not used, then the factory connects to the version SDE.DEFAULT. This clause is only applicable when dealing with versioned tables and layers. Direct Connection ~~~~~~~~~~~~~~~~~ The clauses needed to make a direct connection to SDE depend on the underlying database. In order to make a direct connection, the SDE must be of the same major version as the client libraries with which the SDE30QueryFactory was built. For example, a direct connection to an ArcSDE 9.1 instance could be made with an SDE30QueryFactory built using 9.0 libraries, but a connection could not be made to an ArcSDE 8.3 instance using the same SDE30QueryFactory. Oracle (option 1) SDE_INSTANCE_FIELD The name of the attribute containing the value: sde:oracle or sde:oracle9i (for 9i connections to use the right driver) SDE_USERID_FIELD The name of the attribute containing the value: SDE_PASSWORD_FIELD The name of the attribute containing the value: @ Oracle (option 2) SDE_INSTANCE_FIELD The name of the attribute containing the value: sde:oracle:/;local= SDE_USERID_FIELD The name of the attribute containing the value: SDE_PASSWORD_FIELD The name of the attribute containing the value: MS SQL Server SDE_DATASET_FIELD The name of the attribute containing the value: SDE_INSTANCE_FIELD The name of the attribute containing the value: sde:sqlserver: or sde:sqlserver:\ (for connecting to a named instance) SDE_USERID_FIELD The name of the attribute containing the value: SDE_PASSWORD_FIELD The name of the attribute containing the value: DB2 (option 1) SDE_DATASET_FIELD The name of the attribute containing the value: SDE_SERVER_FIELD The name of the attribute containing the value: remote (if client application is remote, otherwise do not specify) SDE_INSTANCE_FIELD The name of the attribute containing the value: sde:db2 SDE_USERID_FIELD The name of the attribute containing the value: SDE_PASSWORD_FIELD The name of the attribute containing the value: DB2 (option 2) SDE_SERVER_FIELD The name of the attribute containing the value: remote (if client application is remote, otherwise do not specify) SDE_INSTANCE_FIELD The name of the attribute containing the value: sde:db2: SDE_USERID_FIELD The name of the attribute containing the value: SDE_PASSWORD_FIELD The name of the attribute containing the value: Informix SDE_SERVER_FIELD The name of the attribute containing the value: remote (if client application is remote, otherwise do not specify) SDE_INSTANCE_FIELD The name of the attribute containing the value: sde:informix: SDE_USERID_FIELD The name of the attribute containing the value: SDE_PASSWORD_FIELD The name of the attribute containing the value: The clause SDE_VERSION_NAME_FIELD can also be used to specify the attribute containing the version to use when making a direct connection. If not specified the version SDE.DEFAULT will be used. Output Tags The SDE30QueryFactory supports the following output tags. QUERY The query feature that entered the factory. RESULT Each of the features that satisfied the query. TO BE RESOLVED FEATURE_TYPE_FIELD, SPATIAL_FILTER_LAYER_FIELD, SPATIAL_FILTER_ID_FIELD, SPATIAL_FILTER_METHOD_FIELD clauses added to Syntax section above, but not documented. They appear to be ignored in the code. ADD|MERGE|NONE added as parameters to COMBINE_ATTRIBUTES and COMBINE_GEOMETRY clauses, but not documented.