Executes SQL queries against a database. One query is issued to the database for each initiating feature that enters the transformer. Both the initiating features and the results of the query may be output as features.
- Extract database records as FME features according to a SQL statement
- Execute a database join
- Perform SQL database table operations (create, drop, modify, truncate)
- Create or drop indexes or constraints before or after data loading
How It Works
The SQLExecutor receives features via the Initiator port, and for every feature it receives, it executes a SQL query against an external database. The query results, as well as the initiating features, are output.
SQL statements are constructed within the transformer, using the appropriate syntax for the database in use. Spatial queries are supported if the database supports spatial predicates.
- If the database supports spatial predicates, spatial queries may be used.
- For simple joins of one database table to features, the DatabaseJoiner may be more efficient (if the key field(s) are indexed). The DatabaseJoiner does not require knowledge of SQL.
- The SQLExecutor can only be used on SQL-enabled data sources (in addition to the Initiator features). To use SQL with non-SQL data, consider the InlineQuerier.
- If all the data to be queried already exists in a SQL-capable data source, it is generally most efficient to use the SQLCreator or SQLExecutor, as the queries and filtering of the data is executed directly by the database before it enters the FME environment.
- Features that enter this transformer are not output until the SQL query completes. This behavior ensures that additional SQLExecutor transformers, if present, do not execute their queries until the previous query completes.