Reading data from On Prem Database and creating file into FTP server through Integration Call in OIC


Reading data from On-Prem Database and creating file into FTP server through Integration Call in OIC

 In this article, we will learn how to reuse or call already built integration into another integration. 

Use case:  REST integration needs to be developed with the capability to read data from an on-prem database instance and generate the CSV file based on the on-premise database instance in an external FTP server 

To achieve the use case we need the following things to need to be done

1. Create REST Integration in OIC to read data from the On-Premise Database instance, Let's name it Integration1 (CreateCsvFileFromDB)

2. Create another  REST Integration in OIC to generate a CSV file based on JSON Payload Let's name it Integration2

3. Now will call Integration2 (CSV File Generation) with the name CreateCSVFileIntegration in Integration1(Select data from On-Prem DB ) to read data from the on-prem database instance and generate a CSV file in the external FTP server. 


Now let's create Integration1 with the name CreateCsvFileFromDB  to read data from the On-Premise Database instance


Create the integration, drop the REST connector, and fill in the mandatory details like REST endpoint name, Resource URI


Provide the sample JSON payload 







{
"UserData":[
        {
         "id":"",
         "firstname":"",
         "lastname":"",
"department":""
      },
      {
         "id":"",
         "firstname":"",
         "lastname":"",
"department":""
      }
  ]
}


Provide the response payload as 

{

"status":""
}


Click on Done. 






Drop the OnPremDatabase connector to the OIC pallette



Select the mode as Run a SQL statement


provide the SQL query required to fetch data from the on-prem instance





Click on Done 

The next step is to do mapping the activity 
Use the mapper activity to map the fields between source response and target response and click on done to complete the mapping activity. 



Enable identifier for tracking 



Activate the integration and test it by hitting the URL 




With this, we are done with Integration1. 



Now let's create another integration with the purpose to generate a CSV file based on the JSON Payload, Create Integration with App Driven Orchestration

 





Fill in details to create a new integration 



Drop the REST Trigger 




Provide the endpoint name 



select operation as POST, since we are going to generate CSV at the FTP server





{
"UserData":[
        {
         "id":"",
         "firstname":"",
         "lastname":"",
"department":""
      },
      {
         "id":"",
         "firstname":"",
         "lastname":"",
"department":""
      }
  ]
}








Configure response payload as JSON with below sample response 

{
"status" :""
}



Click on Done 





Once the ENDPOINT configured, now lets drop the FTP connector 



Select the operation as Write File and Provide File Name Pattern as ora%SEQ%File.csv 

The file will be created as ora1File.csv, ora2File.csv respectively 




Specify the structure for the contents of the file 




Create the CSV file with columns below and upload it as sample file 
  • ID 
  • FIRSTNAME
  • LASTNAME
  • DEPARTMENT



Click on done 



Perform the mapping activity using mapper. 




Create mapping for status as well by creating a target node 


Map the message in target node as "Successfully created "


Click on Done button 


Activate the integration and run through the JSON payload 




File will be created in External FTP server 





Now get through the integration1 which we have created with the name CreateCsvFileFromDB, deactivate to make changes to it by adding the newly created Integration2 (CreateCSVFileIntegration)






Open the Integration, Go to the action, expand the Call  to drag Integration onto the pallette 




Provide the name  InvokeCsvAPI 




Now browse the integration which you want to call , in our case it is CreateCSVFileIntegration




Select the Operation POST 



Click on Done 




Click on mapper to perform the mapping activity 
Perform the mapping activity from the Database connector response to Call Integration response 


Save and activate the integration 


Hit the URL to generate CSV based on the On-Prem Database select  query 







 

Comments

Popular posts from this blog

Sample HZ API's script to Create Contacts and Contact Points in Oracle

REST integration built-in OIC to read Large files with size more than 10MB