Performs SQL queries against a database.
One query is issued to the database for each feature that enters the transformer. The results of the query are then output through the Result port.
Input Ports
Initiator
Features that trigger a SQL query to be executed.
Output Ports
Result
Features that result from the SQL queries.
Initiator
Input Initiator features with an additional attribute (_matched_records) that contains the number of features generated as the result of SQL query initiated by that feature.
<Rejected>
When SQL execution fails, the original Initiator feature is output through this port with an additional attribute called _reader_error containing the last error message.
Parameters
Database Format and Dataset
Select the database format and dataset, including any format-specific parameters.
Coord. System
You can leave the default, or use a selection from the Coordinate System Gallery.
SQL Statement
Specify the SQL query using the text editor.
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.
The following example contains two SQL commands where errors from the first command will be ignored:
FME_SQL_DELIMITER ;
-SELECT * FROM TABLEA;
SELECT * FROM TABLEB;
Attributes to Expose
Enter the names of attributes to expose on the features created by the query. The attributes will be output in the same sequence as specified in the list.
Note: By default, the attributes of the resulting features are hidden. Specify which attributes to expose by entering the attribute names. Click the browse button next to the Attributes to Expose parameter. You can also use a SQL statement to populate the list by pressing “Populate from SQL Query…” and entering a SQL query. The columns from the first matching feature will be used to populate the attributes list.
Combine Attributes
Result Attributes Only: The result feature attributes consist solely of the query results.
Keep Initiator Attributes if Conflict: The result feature attributes are a combination of both the query results and the initiator feature's attributes. If there is a conflict, attribute values are taken from the initiator feature.
Keep Result Attributes if Conflict: The result feature attributes are a combination of both the query results and initiator feature's attributes. If there is a conflict, attribute values are taken from the query results.
Combine Geometry
Result Geometry Only: The geometries of result features consist only of those produced from the SQL query.
Initiator Geometry Only: The geometries of result features consist only of those present in the initiator features.
Aggregate Initiator and Result Geometry: Result features are populated with geometries from both the initiator features and the SQL query.
Usage Notes
Features that enter this transformer are not outputted until the SQL query completes. This behavior ensures that additional SQLExecutor transformers, if present, do not execute their queries until the previous query completes.
Relationship to FeatureMerger
The FeatureMerger joins two datasets and uses a simple, single attribute key to match features. The FeatureMerger is also able to perform certain geometric operations on incoming features using its Merge Type parameter.
Relationship to InlineQuerier
If all the data to be queried already exists in a SQL-capable data source, it is always more efficient to use the SQLCreator or SQLExecutor, because this allows the queries and filtering of the data to be executed directly by the database before it enters the FME environment.
Relationship to DatabaseJoiner
The DatabaseJoiner is very useful and efficient when there exists a one-to-one or one-to-many relationship between data flowing through FME and data held within a database. If it can be used, the DatabaseJoiner can be more efficient than using either the InlineQuerier or SQLCreator/SQLExecutor, provided that the DatabaseJoiner key fields have indexes in the source database. The DatabaseJoiner is simple to use and does not require any knowledge of SQL.
Example
Editing Transformer Parameters
Using a set of menu options, transformer parameters can be assigned by referencing other elements in the workspace. More advanced functions, such as an advanced editor and an arithmetic editor, are also available in some transformers. To access a menu of these options, click 
beside the applicable parameter. For more information, see Transformer Parameter Menu Options.
Transformer Categories
Search FME Knowledge Center
Search for samples and information about this transformer on the FME Knowledge Center.