FME Transformers: 2025.2 BETA

Categories
Database
Workflows
Related Transformers
DatabaseDeleter
DatabaseJoiner
DatabaseQuerier
DatabaseUpdater
FeatureJoiner
FeatureMerger
InlineQuerier
ListBasedFeatureMerger
Matcher
SchemaScanner
SQLCreator

SQLExecutor

Executes SQL queries against a database.

Jump to Configuration

Typical Uses

  • Extracting database records as FME features according to a SQL statement
  • Executing a database join
  • Performing SQL database table operations (create, drop, modify, truncate)
  • Creating or dropping indexes or constraints before or after data loading

How does it work?

The SQLExecutor executes a SQL query against an external database.

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.

Schemas may also be produced if supported by the database.

Optional Input Port

This transformer has two modes, depending on whether a connector is attached to the Input port or not:

  • Input-driven: When input features are connected, the transformer runs once for each feature it receives in the Input port.
  • Run Once: When no input features are connected, the transformer runs one time and outputs a single new feature.

Examples

Usage Notes

  • Consider using the newer DatabaseQuerier instead.
  • If the database supports spatial predicates, spatial queries may be used.
  • The SchemaScanner may also be useful as an alternative for producing schemas.
  • 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.

Choosing a Feature Joining Method

Many transformers can perform data joining based on matching attributes, expressions and/or geometry. When choosing one for a specific joining task, considerations include the complexity of the join, data format, indexing, conflict handling, and desired results. Some transformers use SQL syntax, and some access external databases directly. They may or may not support list attribute reading and creation.

Generally, choosing the one that is most specific to the task you need to accomplish will provide the optimal performance results. If there is more than one way to do it (which is frequently the case), time spent on performance testing alternate methods may be worthwhile. Performance may vary greatly depending on the existence of key indexes when reading external tables (as opposed to features already in the workspace).

Configuration

Input Ports

Output Ports

Parameters

Editing Transformer Parameters

Transformer parameters can be set by directly entering values, using expressions, or referencing other elements in the workspace such as attribute values or user parameters. Various editors and context menus are available to assist. To see what is available, click beside the applicable parameter.

For more information, see Transformer Parameter Menu Options.

Reference

Processing Behavior

Not applicable

Feature Holding

Not applicable

Dependencies Format-dependent - may require third-party drivers for some formats
Aliases  
History  

FME Online Resources

The FME Community and Support Center Knowledge Base have a wealth of information, including active forums with 35,000+ members and thousands of articles.

Search for all results about the SQLExecutor on the FME Community.

 

Examples may contain information licensed under the Open Government Licence – Vancouver, Open Government Licence - British Columbia, and/or Open Government Licence – Canada.