span8
span4
span8
span4
The SQLCreator and SQLExecutor Transformers can be used to perform queries on databases from within a workspace.
Follow the steps to set up:
- a SQLCreator with a SQL statement that makes use of a published parameter
- a SQLExecutor with a SQL statement to makes use of an existing attribute value
1) Open the attached Starting Workspace.The canvas is blank,but it contains a published parameter named Status.
2) Add a SQLCreator to the canvas.Enter the following PostGIS connection details:
2) Open the SQL Editor from the SQL Statement dropdown selection or click on.Here you can select which tables you want to work with,examine the attributes on the tables,choose parameter values etc.
For example,the SQLCreator can be used to select a subset of features from a database where the subset is defined by the value of a published parameter,such as the SQL below.
Enter the following SQL in the SQL Editor:
SELECT * FROM "fmedata2015"."PostalAddress" where "STATUS" = '$(Status)' and "POSTALCODE" like 'V5L%';
**It is important to quote the SQL Statement correctly,i.e.table names,string values etc,as expected by the underlying database.For example the same SQL statement executed by a SQL Server database would look like:
SELECT ٭ FROM dbo.PostalAddress where PSTLADDRESS = '@Value(Address)'
3) Expose Attributes.
There is also an option to populate the attributes which will be exposed on the outgoing features by using the SQL statement.This will not work if published parameters or attributes are being substituted into the statement,as we are doing here.In this case the attribute names need to be manually entered into the "Attributes to Expose" list.
Enter OWNERNM1,PSTLADDRESS and INTPSTLCD for attributes to expose.
Or use Populate from SQL Query...:
SELECT * FROM "fmedata2015"."PostalAddress" ;
and then edit the list of exposed attributes.
Now you'll update the fmedata2015.PostalAddress similar data in the "public" schema.
4) Add a SQLExecutor to the canvas.Connect it to the SQLCreator.We will make use of the PSTADDRESS attribute output from the SQL Creator.
5) Connect to the PostGIS database using the same connection parameters listed in step 2).
6) Open the SQL Editor from the SQL Statement dropdown selection or click on.Enter the following SQL:
SELECT * FROM "public"."PostalAddress" where"PostalAddress" = '@Value(PSTLADDRESS)';
The @Value() function allows for attributes to be used within queries.The statement above will replace @Value(Address) with the value of the Address attribute from the Initiator feature.
7) Finally,select Keep Initiator Attributes if Conflict for the Combine Attributes parameter.
Performing native spatial queries on database tables using the SQLExecutor
Using the SQLExecutor to do a SQL Join
Splitting SQL statements using the FME_SQL_DELIMITER directive
The SQLCreator and SQLExecutor Transformers
Writing custom data types to an Oracle table
Read Oracle Sequence Nextval and use in Counter
Performing spatial queries on database tables using the FeatureReader
Executing a Stored Procedure on Microsoft SQL Server with FME
Executing SQL commands within a workspace using the SQLExecutor
© 2019 亚搏在线Safe Software Inc |Legal