span8
span4
span8
span4
Although FME can easily send SQL queries to MS SQL Server,you may already have a query or more complex function defined in SQL Server as aStored Procedure.FME can execute stored procedures using the SQLExecutor or SQLCreator transformers and supply attributes or parameter values as arguments to the procedure.
ALTER PROCEDURE [dbo].[MyProc] -- Add the parameters for the stored procedure here @ADDRESSID int,@COUNTY int
SELECT * from dbo.ADDRESS_POINTS where ADDRESSID = @ADDRESSID and COUNTY = @COUNTY
Take note of the order of the arguments so you can supply them with FME in the correct order.FME基金将提供一个ADDRESSIDand aCOUNTY.
Note: We have only shown parts of the stored procedure above - the complete procedure is attached (MyProc.sql).
exec dbo.MyProc '@Value(ADDRESSID)','@Value(COUNTY)'
The parameters of the stored procedure are provided in order and separated by a comma.The@Value()syntax tells FME to send the value of that attribute as the parameter to query.
5.Records returned by the query will come out of theResultport and are sent to the Data Inspector (Visualizer) in the example.Don't forget to click on the "Select No-Geometry" button to see these no geometry features.
Note: The attributes of the output features will not be exposed - meaning they will be on the features but not accessible in workbench.You can expose them using theAttributeExposertransformer to explicitly name the attributes you want to see or expose them in the SQLExecutor transformer.
exec dbo.MyProc '$(ADDRESSID)','$(COUNTY)'
Again,the parameters for the stored procedure are provided in order and separated by a comma.The$()syntax tells FME to send the value of the published parameter to the procedure.
Tested in FME Workbench 2018.0
EXEC[dbo].[GetAPIModulesFromToken]@TOKEN = N'$(token)'It worked,perhaps the article needs amending or have I misread the objective?
exec dbo.MyProc '$(ADDRESSID)','$(COUNTY)'and
exec dbo.MyProc $(ADDRESSID),'$(COUNTY)'both worked.
Performing native spatial queries on database tables using the SQLExecutor
Using the SQLExecutor or SQLCreator to issue commands to a database
Using the SQLExecutor to do a SQL Join
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
Splitting SQL statements using the FME_SQL_DELIMITER directive
© 2019 亚搏在线Safe Software Inc |Legal