Azure Database for MySQL Integration made simple with SAP Integration Suite

[Sriprasadsbhat]

Its been a while I have not contributed to our SAP Community with blogs,finally got some time and its now around scenario involving connecting to Azure Database for MySQL.

Introduction:

In below blog would like to share how we can implement integration scenario for MySQL hosted on Microsoft Azure by leveraging SAP CPI & SAP Cloud Platform Open Connectors service.

Scenario:

Lets create simple scenario to Query from Database and Insert data into ( Single record ) database using MySQL connector of SAP Cloud Platform Open Connectors service.

How to Host MySQL with trial Microsoft Azure account:

You can get a free trial account on Microsoft Azure and once you are done with registration please follow steps mentioned in below blog to get MySQL up and running,

How To Enable MySQL on MS Azure

once your own MySQL is up and running copy below details from MySQL instance which should be used in your Open Connectors instance creation.

Once you are done with above configuration make sure to white list the IP Address from where you want to make a request to this server.In the below screen I have added 0.0.0.0 - 255.255.255.255 which allows all the request since this is just for demo purpose.In actual scenario you must enter correct IP Address only as this poses severe security issue.

Now you can use any client using which you can manage above database.I am using MySQL workbench and created schema : employeedata and table inside it called keydetails.

And finally you are done with MySQL configuration and your database is up and running on Microsoft Azure.

SAP Cloud Platform Open Connectors Configuration:

Login to your SAP Cloud Platform and launch the Open Connector service.

Step#1:

Search for Element: MySQL and click on authenticate.

Step#2:

Provide all the required details before clicking on Create Instance.Refer Section : "How to Host MySQL with trial Microsoft Azure account " to get all the necessary information

Step#3:

Click on API Docs and navigate to Ping to make sure you are getting successful response.

Step#4:

Navigate to API Query to test sample SQL statement and copy the request url to be used in SAP CPI Open Connector receiver channel.

Sample SQL Query:

{

  "script": "SELECT * FROM employeedata.keydetails;"

}

Now we are done with SAP Cloud Platform Open Connectors Configuration.

SAP Cloud Platform Integration Configuration:

Lets create simple integration flow which receives message from POSTMAN using HTTP adapter and post messages to  SAP Cloud Platform Open Connectors service.

Step#1:

Configure HTTP end point .For Ex in my case /ocndata/sqldata

Step#2:

Block created to handle INSERT scenario with Router condition

Expression Type: Non-XML

Condition: ${header.Mode} = 'INSERT'

Step#2.a:

Add Content Modifier with below mentioned body to form SQL query .

{

  "script": "INSERT INTO employeedata.keydetails (id,employeeFirstName,employeeLastName,phoneNumber,emailAddress,department,CompanyId) VALUES ${in.body};"

}

Step#2.b:

Add script to log the query formed .Its for debugging purpose and navigate to Reference section on how to get logging script.

Step#2.c:

Configure Open Connectors adapter with below configuration by leveraging details received from SAP Cloud Platform Open Connectors- MySQL element configuration

Step#3-

Block created to handle Query scenario with Router condition.

${header.Mode} = 'QUERY'

Step#3.a:

Add Content Modifier with below mentioned body to form SQL query .

{

  "script": "SELECT * FROM employeedata.keydetails;"

}

Step#3.b:

Add script to log the query formed .Its for debugging purpose and navigate to Reference section on how to write payload logging script.

Step#3.c:

Configure Open Connectors adapter with below configuration by leveraging details received from SAP Cloud Platform Open Connectors- MySQL element configuration.Please refer Reference section on how to create credentials of type open connectors.

Step#4:

Its default route in case from POSTMAN if you pass other than values INSERT or QUERY it will throw back below response maintained in Content Modifier.

Once you are done with all the above steps deploy your iflow and copy the end point url from Monitoring artifacts section.

Its testing time:

Open POSTMAN tool and configure url & authentication required.

INSERT Case:

Add header Mode with value INSERT and fill the body as shown below .Then send the message to see successful response showing no of records inserted.

QUERY Case:

Add header Mode with value QUERY and then send the message to see successful response having employee records from MySQL database.

References:

[1] SAP Cloud Platform - Open Connectors Blog Series

[2] SAP Cloud Platform Integration- Open Connector Adapter

[3] MySQL Workbench Configuration

Conclusion:

Hope this blogs helps to build DB scenario using SAP Cloud Platform Integration Suite.Thanks to my colleague Shwetha Walaskar for all the support!

Regards,

Sriprasad Shivaram Bhat