I was asked to look into how a FME script can be run from FME Server through trigger mechanism. Well there obviously help available on safe.com – Article. But I wanted to make it even simpler for the user to understand. It is assumed the reader has the adequate knowledge of the FME Desktop and the FME Server. I hope it helps someone!
In my workflow when a row is inserted, updated or deleted on a SQL Server table, log a row in a CSV file accessible to FME server.
Follow the instructions below:
- Login to SQL Server Server Management Studio and login to your SQL server.
- Create a test database if you don’t have one. In my case I created it as TRIGGERTEST.
- Login with the user and create a table called Employee
USE "TRIGGERTEST"
CREATE TABLE [dbo].[EMPLOYEE](
[EMPLOYEE_NUM] [INT] NULL,
[EMPLOYEENUM_NAME] [char](50) NULL
)
GO
- Run the following command to activate the automation procedures
USE "TRIGGERTEST"
sp_configure 'show advanced options', 1;
RECONFIGURE
GO
sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE
GO
- There are some stored procedures in the SYS to which the user used to insert/update or delete, need to be assigned the grants to execute. But on my SQL Server, the user (called sa) already has the EXECUTE grants on the procedures (sp_OASetProperty, sp_OAMethod, sp_OAGetErrorInfo, sp_OADestroy, sp_OAStop, sp_OACreate and sp_OAGetProperty) from SYS.
- Create the following procedure, run the procedure as it is:
create procedure [dbo].[HTTP_DB_MESSAGE]( @sUrl varchar(2000), @message varchar(max))
As
Declare
@obj int
,@hr int
,@status int
,@msg varchar(256)
exec @hr = sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT
if @hr <> 0 begin Raiserror('sp_OACreate MSXML2.ServerXMLHttp.3.0
failed', 16,1) return end
exec @hr = sp_OAMethod @obj, 'open', NULL, 'POST', @sUrl, false
if @hr <>0 begin set @msg = 'sp_OAMethod Open failed' goto eh end
exec @hr = sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'application/x-www-form-urlencoded'
if @hr <>0 begin set @msg = 'sp_OAMethod setRequestHeader failed' goto
eh end
exec @hr = sp_OAMethod @obj, send, NULL, @message
if @hr <>0 begin set @msg = 'sp_OAMethod Send failed' goto eh end
exec @hr = sp_OADestroy @obj
return
eh:
exec @hr = sp_OADestroy @obj
return
GO
- Create the following triggers on the table Employee which would run the FME SERVER script when insert , update and a delete operation is performed.
In the procedure below -see the line -
SELECT 'operation=INSERT&num=' + CAST(EMPLOYEE_NUM as CHAR) + '&name=' + CAST(EMPLOYEE_NAME as CHAR) as mes
Here words operation, name and num are used in the FME script which would get triggered, further. Through this line we are going to pass the parameters to the FME Server workspace url
The trigger script forms the parameter list in key value pairs and appends to the FME Server url, see the lines –
@sUrl = N'http://193.120.94.130:8080/fmejobsubmitter/trigger/sqlserver_employee_logger.fmw',
@message = @mes
CREATE TRIGGER [dbo].[TRG_EMPLOYEE_INSERT]
ON [dbo].[EMPLOYEE]
AFTER INSERT
AS
BEGIN
DECLARE @mes VARCHAR(max)
DECLARE @hr INT
DECLARE @obj INT
-- SQL Server does not support 'for each rows' Trigger.
-- Therefore, it is necessary to make a loop and create a manual 'for each row' method
DECLARE curs CURSOR FOR
SELECT 'operation=INSERT&num=' + CAST(EMPLOYEE_NUM as CHAR) + '&name=' + CAST(EMPLOYEE_NAME as CHAR) as mes
FROM inserted
OPEN curs
FETCH NEXT FROM curs INTO @mes
WHILE @@FETCH_STATUS = 0
BEGIN
-- Run HTTP request procedure
EXEC [dbo].[HTTP_DB_MESSAGE]
@sUrl = N'http://193.120.94.130:8080/fmejobsubmitter/trigger/sqlserver_employee_logger.fmw',
@message = @mes
FETCH NEXT FROM curs INTO @mes
END
CLOSE curs
DEALLOCATE curs
END
GO
-- Step 6. Create Trigger UPDATE on EMPLOYEE table
CREATE TRIGGER [dbo].[TRG_EMPLOYEE_UPDATE]
ON [dbo].[EMPLOYEE]
AFTER UPDATE
AS
BEGIN
DECLARE @mes VARCHAR(max)
DECLARE @hr INT
DECLARE @obj INT
-- SQL Server does not support 'for each rows' Trigger.
-- Therefore, it is necessary to make a loop and create a manual 'for each row' method
DECLARE curs CURSOR FOR
SELECT 'operation=UPDATE&num=' + CAST(EMPLOYEE_NUM as CHAR) + '&name=' + CAST(EMPLOYEE_NAME as CHAR) as mes
FROM inserted
OPEN curs
FETCH NEXT FROM curs INTO @mes
WHILE @@FETCH_STATUS = 0
BEGIN
-- Run HTTP request procedure
EXEC [dbo].[HTTP_DB_MESSAGE]
@sUrl = N'http://193.120.94.130:8080/fmejobsubmitter/trigger/sqlserver_employee_logger.fmw',
@message = @mes
FETCH NEXT FROM curs INTO @mes
END
CLOSE curs
DEALLOCATE curs
END
GO
-- Step 7. Create Trigger DELETE on EMPLOYEE table
CREATE TRIGGER [dbo].[TRG_EMPLOYEE_DELETE]
ON [dbo].[EMPLOYEE]
AFTER DELETE
AS
BEGIN
DECLARE @mes VARCHAR(max)
DECLARE @hr INT
DECLARE @obj INT
-- SQL Server does not support 'for each rows' Trigger.
-- Therefore, it is necessary to make a loop and create a manual 'for each row' method
DECLARE curs CURSOR FOR
SELECT 'operation=DELETE&num=' + CAST(EMPLOYEE_NUM as CHAR) + '&name=' + CAST(EMPLOYEE_NAME as CHAR) as mes
FROM deleted
OPEN curs
FETCH NEXT FROM curs INTO @mes
WHILE @@FETCH_STATUS = 0
BEGIN
-- Run HTTP request procedure
EXEC [dbo].[HTTP_DB_MESSAGE]
@sUrl = N'http://193.120.94.130:8080/fmejobsubmitter/trigger/sqlserver_employee_logger.fmw',
@message = @mes
FETCH NEXT FROM curs INTO @mes
END
CLOSE curs
DEALLOCATE curs
END
- On the FME Server, create a repository called “trigger” and assign grant to fmeguest to execute scripts in it. See the snapshots below:
- Create a FME Script (fmw’), make sure there are 3 published parameters called num, name and operation as these 3 parameters are passed from the trigger scripts. In the script, fetch those parameters and create attributes from them and use them to populate a CSV file.
- Publish this script to trigger repository on the FME Server using the Job Submitter service.
- Create a CSV file on FME Server and keep it in c:\temp\EmployeeTableLog folder.
- Done! Run the following SQL commands in SQL Server.
INSERT into [triggertest].[dbo].[employee] values(100,'Kirk'); UPDATE [triggertest].[dbo].[employee] set employee_name ='Ciaran'); DELETE from [triggertest].[dbo].[employee] where employee_num=100;
This would trigger through SQL Server and execute FME script on FME Server and populate a CSV file as designed in the script.
vijaysambhe 