In this blog I will show you how we can insert a record in to sql table and also retrieving the record from table.
Inserting data into database :
First we need to start with creating the service. Here we need to create Integration service.The values which we need to insert in to table will be obtained as an input to service.
Here I have only single complex object as input to service, but we can even have multiple input variables as input.Coming to the diagram , first the sql query needs to be written. For that I have used server scriptlet and I’ve binded that to sql variable. So that we can use that for bindings later.
Here I have used internal DB so there is no need of any schema for CRUD operations . But if we are connecting to external sql server we need to have db schema.
NOTE : As a good practice DB schema needs to created as an epv variable and can be referenced in the service. So that any change in the schema details needs updation just at one place.
Then we need to initialize parameters as List of SQL Parameter type as we need to pass that as input to SQL Execute Statement nested service.Then in this parameters we need to have initialization for all the values that we pass in our SQL query.
Then we need to have the SQL Execute Statement Nested service. Then we need to do the necessary bindings
Now the service is ready for the insertion of data in to sql table.
Retrieving data from database :
Coming to retrieving data from table, It is similar to insertion but we need to mention the returnType , how we are expecting the result. It may be some business object type, XMLElement, NameValuePair etc., If it is other than business object type then we need to parse that response before sending that as output. I will show both the ways
- If return type is set as business Object type then results and output variable should also be of same business object type.
Initializing the params is same.We need to initialize the input variable here
So we will have the response in results variable. Then you need to assign that to your output variable.It can done in the Post-execution Assignment.
tw.local.employeeData = tw.local.results
2.If the return type is of type XMLElement , please check here