)
We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to invoke a REST Call action in Data Integration Platform using SQL Server Trigger statements.

Platform: Big Data Platform |
Control: General |
Published Date: March 30, 2017 |
Last Revised Date: February 27, 2020

How to invoke a REST Call action in Data Integration Platform using SQL Server Trigger statements.

 

This document illustrates the steps to start / stop the data integration processors through Restful invocation based on Triggers in SQL Server database.

Goal:

Whenever a new row inserted or existing record modified in a SQL Server table, we need the process the new/modified records in Data Integration Platform (DIP) by fetching them from SQL table. Instead of scheduling the DIP processors on a fixed time to pull records from SQL table, here we are going to use Trigger fired when a record is inserted / modified to invoke DIP REST API to start the processors to pull the new/modified record from SQL Server.

Start / Stop Data Integration Processor:

 Data integration processor can be start or stop using REST full operation. Below are the steps followed to start and to stop the processor.

Step 1 – Construct REST URL to start the processors.

Using ‘Put’ request we can start the data integration processor. Construct the REST URL to be invoked for Starting the processor.

Components

Format

Example

URL

http://<IPAddress>:<PortNo>/dataintegration-api/processors/<ProcessorID>

http://localhost:60017/dataintegration-api/processors/69b51b50-015a-1000-7656-9bf20f2973c7

 

Body

{

 "revision":

 {

  "version":<Version Number>

  // Version can be get from GET request

 },

 "component":

 { 

  "id":<ProcessorID>,

  "state":"RUNNING"

 }

}

{

 "revision":

 {

  "version":18

 },

 "component":

 {

  "id":"69b51b50-015a-1000-7656-9bf20f2973c7",

  "state":"RUNNING"

 }

}

 

Step 2 – Construct REST URL to stop the processor:

Using ‘PUT’ request we can stop the data integration processor. Construct the REST URL to be invoked for stopping the processor.

Components

Format

Example

URL

http://<Machine IpAddress>:<PortNo>/dataintegration-api/processors/<ProcessorID>

http://localhost:60017/dataintegration-api/processors/69b51b50-015a-1000-7656-9bf20f2973c7

 

Body

{

 "revision":

 {

  "version":<Version Number>

  // Version can be get from GET request

 },

 "component":

 {

  "id":<ProcessorID>,

  "state":"STOPPED"

 }

}

{"revision":

{

"version":18},

"component":

{"id":"69b51b50-015a-1000-7656-9bf20f2973c7",

"state":"STOPPED"

}

}

 

Step 3 - SQL Trigger query to invoke the processor:

Create SQL Trigger using the following syntax as reference

Query:

Syntax:

CREATE TRIGGER <trigger Name> ON <Table name >

FOR INSERT

AS

declare @vPointer INT;

declare @vResponseText VARCHAR(8000);

declare @vStatus INT;

declare @vStatusText VARCHAR(200);

declare @Parametres nvarchar(3000);

declare @Body as varchar(8000) =

'{

"revision":

{

"version":18

},

"component":

{

"id":"<processorID>",

"state":"RUNNING"

}

} '

SET @Parametres = ''

EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @vPointer OUTPUT

EXEC sp_OAMethod @vPointer, 'open',NULL, 'PUT', “http://<hostname>:60017/dataintegration-api/processors/<processorID>”, 0

EXEC sp_OAMethod @vPointer, 'setRequestHeader', null, 'Content-Type', 'application/json'

Exec sp_OAMethod @vPointer, 'send', null, @Body

EXEC sp_OAMethod @vPointer, 'Status', @vStatus OUTPUT

EXEC sp_OAMethod @vPointer, 'StatusText', @vStatusText OUTPUT

EXEC sp_OAMethod @vPointer, 'responseText', @vResponseText OUTPUT              

EXEC sp_OADestroy @vPointer 

GO

ADD COMMENT
You must log in to leave a comment
Comments
Mohammad Shojaeinia
Feb 26, 2020

Hello.

Thanks for your helpful article. I have the same problem, I want to send PUT request after insert into a table. So, I want to use a trigger for this purpose. But it seems there is a problem with this trigger definition. My transaction never executes! Because when it comes to "EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @vPointer" does not continue anymore. What should I do? My platform is windows 10 and SQL Server 2014.

Regards.

Reply
Abinaya Karuppasamy [Syncfusion]
Feb 27, 2020

Hi Mohammad Shojaeinia,

Please specify "OUTPUT" type for @vPointer parameter while creating OLE object instance. EXEC sp_OACreate 'WinHttp.WinHttpRequest.5.1', @vPointer OUTPUT

We have also updated the documentation.

Regards, Abinaya Karuppasamy

Mohammad Shojaeinia
Apr 04, 2020

Hello again,

thanks for your great article and your fast reply to my last comment. Another problem I have is that I want to dynamically create the URL. For instance, I want to have: http://someurl.com/api/v1/{id}/feed and I should get the id from the query: (select ID from inserted).

How can I do this?

Regards

Reply
Abinaya Karuppasamy [Syncfusion]
Apr 06, 2020

Hi Mohammad,

Please follow the below steps to dynamically generate the URL based id from the SQL select query.

  1. ExecuteSQL – Use this processor to execute your select query (select ID from inserted) for getting the id from your database.
  2. ConvertAvroToJSON – The result of the ExecuteSQL processor will be in AVRO format. Use this processor to convert Avro formatted data to JSON array.
  3. SplitJson – Use this processor to split the Json array into individual json element.
  4. EvaluateJsonPath – Use this processor to parse the value from json and store the id as flow file attribute.
  5. InvokeHTTP – Once the id is stored as flow file attribute in step 4. We can directly use that id dynamically in URL using this expression http://someurl.com/api/v1/${id}/feed

Regards, Abinaya.

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon