Execute CRUD Stored Procedure calls using Syncfusion Data Integration Platform.
Introduction
This article explains how to execute the stored procedure calls with crude operation statements in the SQL database using the Syncfusion Data Integration.
Objective
The ExecuteProcess processor is used in the Syncfusion Data Integration Platform for executing the stored procedure calls.
Example Dataflow for Executing Stored Procedure Call using ExecuteProcess Processor
Overview
The following table is used in this sample:
Source table name: Tips
Update the size column mentioned in the following table and store the updated columns into new table.
Destination table name: UpdatedTips
List of processors used in this sample
Processor | Comments |
ExecuteProcess | Executes the stored procedure query and its resultant data will be transferred in to flowfile. |
ReplaceText | Replaces the unwanted content in the incoming flow file. |
Split Text | Creates the stored procedure query and its status will be transferred in to flowfile. |
Extract Text | Converts the AVRO format incoming data into JSON format. |
RouteOnAttribute | Calls the stored procedure query and its resultant data will be transferred in to flowfile. |
ReplaceText | Forms the insert query from the incoming flowfile. |
PutSql | Inserts the fetched data into the SQL database. |
ExecuteProcess Processor
This processor will execute the Stored Procedure query using the SQL cmd utility and its resultant data will be transferred in to flowfile.
Command: C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE
Command Arguments: -S localhost -U labuser -P coolcomp@123 -d testSample -i E:\DIP_New\Knowledge_Base\Stored_procedure\Execute_Procedure.sql -s "&"
Refer below link for more details
https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility
ReplaceText Processor
This processor will process the generated flowfile and replace the invalid lines in the incoming flowfile content (including column header and special characters) using the regex expression \([^*]+[-]{3,}.
SplitText Processor
This processor will process the flowfile and split each line of its content into separate flowfile.
ExtractText Processor
This processor will check regular expression against the flowfile content and if it is satisfied, then it will assign the content into the flowfile attribute.
RouteOnAttribute Processor
This processor checks each flowfile attribute of the valid content.
It will transfer the success flowfiles, if the given property with following expression matches with its incoming flowfile attribute.
validrow: ${input.row.1:getDelimitedField(1):contains('rows affected'):equals(false)}
ReplaceText Processor
This processor will replace the content of the flowfile using user defined SQL command.
SQL Query:
INSERT INTO [testSample].[dbo].[updatedTips] VALUES (${input.row.1:getDelimitedField(1,'&')},'${input.row.1:trim():getDelimitedField(2,'&')}','${input.row.1:trim():getDelimitedField(3,'&')}','${input.row.1:trim():getDelimitedField(4,'&')}','${input.row.1:trim():getDelimitedField(5,'&')}',${input.row.1:trim():getDelimitedField(6,'&')})
PutSql Processor
This processor will execute the command from flowfile content and insert data into another table.
Destination table: UpdatedTips
Limitations of Executing the stored procedure call in ExecuteProcess Processor
- To perform the crude operation using stored procedure call, both the SQL Server and Syncfusion Data Integration platform should be installed in the same machine.