SQLExecutor

From fmepedia

SQLExecutor is a Workbench Transformer.

Table of contents

1 Description 2 Notes: 3 Example 3.1 Workspace Screenshot

Description

Executes an arbitrary SQL statement against a database. Supported databases include ODBC, Oracle, Microsoft Access, Microsoft SQL Server, PostgreSQL/PostGIS, DB2, MySQL, and Microsoft Excel.

If the SQL statement resulted in a row (or rows) being returned, the attributes from the row are added to the feature. If multiple rows are returned, the attributes from those rows are held in the list specified by the <list name> parameter. The individual rows may be then exposed to be operated on directly, manipulated by any of the list processing transformers, or broken out into separate features by using the ListExploder transformer.

Note that in either case, if the attributes retrieved from the SQL statement are to be used in later transformers, they must be manually identified in the transformers' properties. This is because the SQLExecutor does not know what attributes it will be retrieving from the database until the SQL statement is actually executed at run time. Undefined attributes will show up when the data is sent to the Viewer, but other transformers will not be able to access those attributes.

---

Notes:

• Some databases require that quotation marks be escaped. For mixed registration table names, or Oracle tables with spaces, you must include backslashes to run a query. For example:

select * from \"MixedRegister\"

• The geometry column from a MySQL spatial database will not be selected when a select * from ... statement is used. It will only be selected if explicitly identified and when the AsText() operator is used. E.g.

SELECT AsText(geom) FROM geonames; 

However, this causes the geometry attribute to be known as 'AsText(geom)' and it should also be identified as such in the SQLExecutor.

• The SQLExecutor fires off an SQL statement to the database once for every feature passing through it.

• A few more examples of select syntax for SQLExecutor:

 select * from DEPOTS where NUMBER = '99'

Note single quotes, not double. This adds a list of records from the DEPOTS table to each FME feature that satisfy the condition NUMBER = '99' Note that NUMBER is the database field name and'99' is a literal.

To query by the value of an FME attribute use the following:

 select * from DEPOTS where NUMBER = &count

Here DEPOTS is the oracle table and NUMBER is the field I am matching on. count is the FME attribute I am using to link to the database. The '&' means get the value of this attribute for the current feature. This returns a list of records added to each feature that match NUMBER = &count, so if count = 5 for the FME feature then you'll get all the records from DEPOT where NUMBER = 5.

Example

The attachment 'SQLExecutor Example.zip|attached workspace' not found shows an example use of the SQLExecutor transformer.

In this example parcel data is queried based on a field within the source owner data to add new attributes to the owner data.

Workspace Screenshot

image 'SQLExecutor Example.bmp' not found