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.
Unfortunately, activation email could not send to your email. Please try again.

Execute CRUD Stored Procedure calls using Syncfusion Data Integration Platform.

Introduction

This ar­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­ticle 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.

 

 

Article ID: Published Date: Last Revised Date: Platform: Control:
8665 04/10/2018 06/01/2018 Big Data Platform General
Did you find this information helpful?
Add Comment
You must log in to leave a comment

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.