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. Image for the cookie policy date
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

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

 

This KB article explains the steps to start and stop the data integration processors by invoking the REST API using the Triggers in the SQL server database.

Goal:

Whenever a new row is inserted or an existing record modified in a SQL server table, the trigger gets fired and the REST API will be invoked to start the Data Integration processors. Instead of scheduling the Data Integration Platform processors on a fixed time to pull records from the SQL table, here, we are going to fire the Trigger when a record is inserted or modified to invoke the Data Integration Platform REST API to start the processors to pull the new or modified record from the SQL server.

Start and Stop Data Integration Processor:

Data integration processor can be started or stopped using the REST API. Follow these steps to start and stop the processor.

Step-1:  Get the processor version.

Using the ‘GET’ request, we can get the version of the data integration processor in its JSON response.

 

http://localhost:60017/dataintegration-api/processors/<ProcessorID>

 

 

The ProcessorID of the GenerateFlowFile processor is highlighted in the following image.

GenerateFlowFile processor

 

Step-2: Construct a REST API to start the processors.

Using the ‘PUT’ request, we can start the data integration processor. Construct the REST API to be invoked for starting the processor.

Components

                          Format

                   Example

URL

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

http://localhost:60017/dataintegration-api/processors/a7f73042-0176-1000-cc85-dae5aa1bcd8f

 

Body

{

 "revision":

 {

  "version":<Version Number>

  // Version can get from the GET request.

 },

 "component":

 { 

  "id":<ProcessorID>,

  "state":"RUNNING"

 }

}

{

 "revision":

 {

  "version":18

 },

 "component":

 {

  "id":"a7f73042-0176-1000-cc85-dae5aa1bcd8f",

  "state":"RUNNING"

 }

}

 

Step-3: Construct the REST URL to stop the processors.

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

Components

                          Format

                     Example

URL

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

http://localhost:60017/dataintegration-api/processors/a7f73042-0176-1000-cc85-dae5aa1bcd8f

 

Body

{

 "revision":

 {

  "version":<Version Number>

  // Version can get from the GET request.

 },

 "component":

 {

  "id":<ProcessorID>,

  "state":"STOPPED"

 }

}

{

"revision":

{

         "version":18

},

"component":

{

         "id":"a7f73042-0176-1000-cc85-dae5aa1bcd8f",

         "state":"STOPPED"

}

}

 

Step-4: State of the processor.

The processor is in a stopped state in the Data Integration Platform before the trigger gets fired.

 Processor state

Step-5: SQL Trigger query to invoke the processor:

Create a SQL Trigger for a SQL table using the following syntax as a 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":<version number can get from step-1>

},

"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

 

Make sure you have created any SQL table before creating a trigger using the following query as a reference.

 

Table creation

 


Step-6: Invoking the REST API.

The trigger will get fired and the Data Integration processor will be started running as follows if any records are inserted or modified into the created SQL table.

Processor started

 
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