Connecting To An On-Premise Database Using the Oracle Integration Cloud Agent Framework

Over the past few months, we have been working with Oracle Integration Cloud (OIC). In this post, we’ll be looking at Integration Cloud Services (ICS) agent framework, which allows us to connect to our on-premise applications.

As not all applications run on the cloud, the agent framework can help us build hybrid cloud solutions, allowing on-premise and cloud applications to communicate with each other.

There are 2 types of agents:

Connectivity Agent:-

A gateway which provides a connection between on-premise apps and ICS domain to interchange messages by eliminating the firewall.

Execution Agent:-

A self-managed on-premise instance of ICS that enables better integration for systems within your organization and caters for security concerns on sensitive data residing in the cloud.

In this blog, we’ll focus on the Connectivity Agent.

For our use case, we will create an Integration that connects to an on-premise MySQL database where we’ll be storing the latest weather forecast.

Components and Artifacts Used:-

 1) Integration Cloud Services.
 2) MySQL DataBase Installed on On-Premise.
 3) SoapUI.
 4) Necessary DB tables created in MySQL DataBase.
Installation:

Step 1: Create Agent Group

  • We can segregate our agents based on a project or use-case to a specific group.
  • Navigate to agent page and click on "create agent-group".
  • Provide a relevant name to be used in the installation of the agent.

Step 2: Download The Agent

In the download drop-down menu, select connectivity agent and download it.

Prerequisites for installing the agent:

 1) Linux based Operating System(Windows not supported at this stage).
 2) Create an agent group.
 3) JDK version 1.7 or 1.8.
 4) Minimum 8GB memory.

Note: I ran into swap memory issues and used the following commands to resolve it.

      sudo dd if=/dev/zero of=/var/myswap bs=1M count=2048
      sudo mkswap /var/myswap
      sudo swapon /var/myswap

Step 3: Running the Installer

  • Unzip the agent archive.
  • A *.bsx installer and a Patcher zip file are extracted into the directory. *.bsx is for installation of the agent and Patchers are for future upgrades.
  • Provide necessary permissions to make the file executable. "chmod 755 cloud-connectivity-agent-installer.bsx" would do the trick
  • As Weblogic gets installed as part of the agent Installation, Create or Use a Oracle User to run the .bsx file to start the installation.

    ./cloud-connectivity-agent-installer.bsx -h={$Ics_HostUrl} -u=username -p=password -ad=Vivek_sample_agent -au=agent_vivek -ap=Welcome1
    

    Where -h denotes our ICS Domain URL along with the port number. -u denotes our ICS username. -p denotes password. -ad denotes agent group which we have created. -a denotes username for the Weblogic domain created as part of the installation. created while installation. -ap denotes Weblogic user’s password.

Step 5: Verify the Count

Once the agent has been installed

  • Navigate to Agent Page.
  • We can see that the agent count has been increased on the agent group which we have created.
Configuring Connections:

Step 1: Create Source Connection

  • We will use the Soap Adapter as source and configure it with our service. For our case, I have created a WSDL which we can use to update weather details.

  • ICS allows us to define a WSDL URL or upload a WSDL file.

  • Upload the WSDL and click "OK".

  • As we are not using any security constraints, select "No Security Policy" in configure security tab. Step 2: Create Target Connection

  • Navigate to the connections page.

  • Click on create, which prompts us to select the adapter we want to use.

  • Select the MySQL adapter to configure the connection

  • I am going to name it Vivek_MySQL and select the role as Invoke, as we are going to use it as a target system as part of our integration.

  • Provide the necessary connection and authentication details to configure connectivity.

  • In agent group section click on "Configure Agents".

    • Click on "Use" to configure agent for that connection.
  • Click test connection to make it ready for usage.

Step 3: Testing the connections

  • Click on "Test" Button, which prompts us with two options "Validate and Test" and "Test".
  • Click on "Validate and Test" to both validate the artifacts and test the connection.

Once the connections have been tested we can proceed with the creation of integration.

Building an Integration:
  • Navigate to Integrations Page and click on create

Step 1: Selecting Integration Pattern

  • Select an Integration Pattern, in our case Orchestration.

  • Provide basic details
    • Name.
    • Version.
    • Identifier.
    • Optional description and package name.

  • This generates a basic integration flow with no source and target configured

Step 2: Configuring Trigger and Invoke

  • Let's configure the source, drop the connection we have created from the resource palette.

•Provide a name that defines your operation and an optional description.

  • Next page shows the available operations and port types, as our WSDL has only one operation we can continue to next page by clicking "Next".

  • As we have defined our operation to be one way in our WSDL it provides us with this page where we can choose to send a delayed response with custom headers or no response at all, we’ll select no response.

  • We won’t be adding any Headers, so skip to the summary page.

  • With our source configured, let’s now configure the target.

  • Drop Vivek_MySQL on to the flow and provide Basic information like name and optional description and the steps for configuring the adapter are:

    a) We would be running pure SQL so select that option from the drop-down.

b) Insert your SQL query and validate it.

c) We have to validate the query before proceeding to the next step and it prompts the below error if we don't validate.

d)Any errors in the validation are shown below.

Step 4: Map Data

After configuring the target, ICS automatically creates a mapper file where we can map data from Source to Target.

  • Click on "Map" Action to open the mapper window.
  • Map the required data elements by just drag and drop wizard or we can even apply functions on them by clicking on the necessary target elements.
  • Click validate for checking errors and then close the wizard.

Now our flow should look like below with a trigger, invoke and mapper file.

Step 4: Adding Tracking variable

  • Tracking Variable is mandatory to activate the integration.

  • Click on menu icon and select tracking.
  • A unique identifier must be provided which helps us when tracking the integration flows.
  • We’ll select CityId.

Step 5: Activate

  • The agent must be running to activate the integration.
  • Save and activate the integration.
  • Activation provides us with an endpoint which we can use to test the integration.
Testing Our Integration:
  • We can use SoapUI to test our integration.
  • Create a new Soap project and provide our integration’s endpoint.

  • Enter the data along with WSS Username Token and Timestamp. Username and password can be specified in request properties bar.

  • Right click on the request payload and click on "Add" to add username tokens and timestamp values.

  • Run the test case to successfully trigger our instance.

  • Now we can see that the data is being populated to the DB Table.

  • We can track any specific instance by providing the tracking variable used while creation of an integration

Usages of agent FrameWork
  • The On-premise agent can be monitored through the User Interface in the ICS Console
  • No need to have a pre-built container to deploy the agent
  • It is not required to open the inbound ports to access applications
  • It is not required to expose private Soap-based web services
Monitoring and Control:-
  • The installation provides start/stop scripts which take username and password as inputs:

    ./startAgent -u=Username -p=Password
    
  • Status and Start/Stop actions can be monitored from the ICS Console.

With this, we have seen how to Create, Test and Monitor an Integration that connects to an on-premise application using the agent framework.

Thank you for reading!

Vivekananda Reddy

Oracle Certified Implementation Specialist and technology enthusiast with hands-on professional experience in Oracle Fusion Middleware and Oracle Cloud integration projects.

Hyderabad