SQLExecutor

Executes SQL queries against a database once for each initiating feature that enters the transformer. Schema, the initiating features, and the results of the query may be output.

Jump to Configuration

Typical Uses

  • 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 does it work?

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.

Schemas may also be produced if supported by the database. One schema feature will be output for every initiator feature.

Examples

Usage Notes

  • 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

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.

Defining Values

There are several ways to define a value for use in a Transformer. The simplest is to simply type in a value or string, which can include functions of various types such as attribute references, math and string functions, and workspace parameters. There are a number of tools and shortcuts that can assist in constructing values, generally available from the drop-down context menu adjacent to the value field.

Dialog Options - Tables

Transformers with table-style parameters have additional tools for populating and manipulating values.

Reference

Processing Behavior

Not applicable

Feature Holding

Not applicable

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

FME Community

The FME Community is the place for demos, how-tos, articles, FAQs, and more. Get answers to your questions, learn from other users, and suggest, vote, and comment on new features.

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

 

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