SQLCreator

Generates FME features and/or schemas from the results of a SQL query executed against a database. One FME feature is created for each row of the results of the SQL query.

Jump to Configuration

Typical Uses

  • Extract database records as FME features according to a SQL statement
  • Add features from a database mid-translation
  • Execute a database join

How does it work?

The SQLCreator executes a SQL statement against a database. It has no input ports, and so (unlike the SQLExecutor) is not reliant on incoming features to trigger the statement - rather, the query is run one time only.

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.

One feature is output for each row of the query results, via the Result port.

A single schema feature may also be produced if supported by the database.

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.
  • The SQLCreator can only be used on SQL-enabled data sources. 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.
  • 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.

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

The SQLCreator has no 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 SQLCreator on the FME Community.

 

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