Writing a DML command in OIC instance to insert data into OnPrem Database Instance

 In this case study, we will build a REST integration through OIC with the purpose to insert data into On-Prem DB through the DML command

First step would be to create integration through service console , as shown in the below screenshot click on Create button 

Choose App Driven Orchestration

Fill the mandatory fields with relevant information

Since we are building REST API to insert data into On-Prem DB , we should drop REST connector onto the Pallette 

Provide the name for the endpoint URL

Fill the URI name and select the Operation as POST

Click on Next and load sample JSON format 

{

"UserData":[

        {

         "id":"",

         "firstname":"",

         "lastname":"",

"email":"",

         "qualification":""

      },

      {

         "id":"",

         "firstname":"",

         "lastname":"",

"email":"",

         "qualification":""

      }

  ]

}

Click on OK

Click on Next  and provide Sample JSON for the response payload

{

"status":""

}

Click on Done 

Now Database connector with Configured On-Prem Credentials needs to be dropped on the workspace 

Provide Endpoint and select the operation as " Run a SQL Statement" 

Paste the below insert command 

INSERT INTO XX_OIC_EMP( ID,FIRST_NAME,LAST_NAME,EMAIL_ID,QUALIFICATION) VALUES (#ID,#FIRSTNAME,#LASTNAME,#EMAILID, #QUALIFICATION)  

Validate the SQL query 

                          

Perform the mapping between the columns 

Perform the mapping between the Response column

Create a Target Node to save static message which will be displayed upon the successful call of API 

Enable the tracker with an identifier 

Activate the integration and test it 

Copy the sample payload in the body and hit the Test button the see whether data is being populated in the On-Premise database table or not 

Same thing you can do in Advance Rest Client , by copying the URL and entering the OIC credentials 

Here is the response from REST API

Congratulations data has been successfully inserted.