Skip to Content
Technical Articles
Author's profile photo Sriprasad S Bhat

SAP Cloud Platform Integration- Connecting Microsoft SQL Sever

Introduction:

JDBC connectivity to On Prem Database Systems-it was long awaited feature in SAP Cloud Platform Integration space. Latest release finally have option to work on it. Lets see how to work on the same.

Scenario:

Lets consider 3 most used concepts from integration perspective.

  • Simple SELECT and UPDATE to get and update the the tables.
  • Calling  simple and parameterized Stored Procedure including function.

 

Setup your SAP Cloud Connector:

Follow below tutorial to install and configure SAP Cloud Connector in your landscape.

Install and Configure SAP Cloud Connector

One you are done with cloud connector installation and initial configuration follow below to create TCP connection to your on-premise Microsoft SQL Server.

Note down virtual server address you have specified in SAP Cloud Connector later we will be using same in SAP Cloud Platform Integration.

Configure your Microsoft SQL server:

Install any client you want to leverage for better user experience while performing SQL transactions. In my scenario I will be using Data Grip by Jet Brains.

Get into Data Grip and create new project as shown below.

Click on add new data source and select type as Microsoft SQL server.

Keep your SQL Server urls and credentials ready to configure new data source as shown below.

From above screen make a note of JDBC driver version and copy the URL which we are going to use in SAP CPI.

Lets create 3 scenarios

  1. Create a Stored Procedure
  2. Create Stored Procedure with input parameter.
  3. Create Stored Procedure which calls Function internally.
  4. Create table which can be later used to perform SELECT and INSERT operations.

I have explained steps for creating Simple Stored Procedure which can be further extended for other features as its self explanatory.

 Stored Procedure:

Open Console and click Alt + Insert to create Stored Procedure and execute it once done.

Copy Paste below

CREATE PROC What_DB_is_this5
AS
SELECT DB_NAME() AS ThisDB;
go

Click Ctrl + Shift +F10 to run the procedure and you are good to execute from SAP CPI.

 

Parameterized Stored Procedure:

CREATE PROC What_DB_is_that @ID INT
AS
SELECT DB_NAME(@ID) AS ThatDB;
go

Stored Procedure Internally calling function:

create procedure PROC_PRINT_HW
as
select dbo.helloworldfunction()
go

Function:

CREATE FUNCTION dbo.helloworldfunction()
RETURNS varchar(20)
AS
BEGIN
	 RETURN 'Hello world'
END
go

Creating artifact in SAP Cloud Platform Integration:

 

Setting up JDBC connections:

Login into SAP CPI and Navigate to “Manage JDBC Material” to maintain Connection profile and required JDBC driver.

Maintain JDBC Driver:

Click on add new and select type of database you are trying to connect. You can cross check version of JDBC driver required with screenshot provided in Test Connection option of Data Grip.

Below will give overview of JDBC driver compatibility with different versions of Microsoft SQL Server.

Microsoft JDBC Driver for SQL Server support matrix

 

Maintain JDBC Data Source:

Click on Add JDBC Data Source and follow below configuration. Make sure you have selected Cloud Connector Checkbox if you are connecting your On-premise SQL server. End Point url would be your virtual server address replaced with actual server ( check cloud connector to get the virtual server address)

 

IFlow Configuration:

Lets try to simulate all the scenarios with simple IFlow having Postman as HTTP client.

Also make sure to add header name “Action” in the allowed headers part.

Step 1:

Add  HTTP Receiver Adapter with url.

Step 2:

Add Router branch to identify different actions we are going to perform.

Choose Expression Type as Non XML and add below condition.

${header.Action} = 'QUERY'

Sample query structure:

 

SELECT * FROM <SCHEMA_NAME>.<TABLENAME>;

Step 3:

Add Router branch to identify different actions we are going to perform.

Choose Expression Type as Non XML and add below condition.

${header.Action} = 'INSERT'

Sample query structure:

INSERT INTO <SCHEMA_NAME>.<TABLE_NAME> (
    ID,
    COMPANY_NAME,
    COMPANY_CODE,
    LOCATION,
    ADDRESS_1,
    ADDRESS_2,
    STATE,
    COUNTRY,
    PINCODE,
    EMAIL,
    WEBSITE,
    PHONE_1,
    PHONE_2,
    NMBR_OF_EMPLOYEES,
    FACILITIES,
    PROCESSED
)
VALUES
    (
        'COMPANY 1S',
        'C101S',
        'LOCATION 1S',
        'ADDRESS 1S',
		'ADDRESS 2S',
		'STATE 1S',
		'COUNTRY 1S',
		560085,
		'c1S.email@email',
		'company1S.com',
		12345,
		12342,
		25,
		'FACILITIES 1S',
		'NO'	
		
    ),
    (
        'COMPANY 2S',
        'C102S',
        'LOCATION 2S',
        'ADDRESS 2S',
		'ADDRESS 2S',
		'STATE 2S',
		'COUNTRY 3S',
		560085,
		'c2S.email@email',
		'company2S.com',
		12345,
		12342,
		25,
		'FACILITIES 2S',
		'NO'
    );
	

Step 4:

Add Router branch to identify different actions we are going to perform.

Choose Expression Type as Non XML and add below condition.

${header.Action} = 'STOREDPROCEDURE'

Sample Stored Procedure structure:

<root>
	<StatementName>
		<storedProcedureName action="EXECUTE">
			<table><STORED_PROCEDURE_NAME></table>		
		</storedProcedureName >
	</StatementName>
</root>

Sample Stored Procedure With Input Parameter structure:

<root>
	<StatementName>
		<storedProcedureName action="EXECUTE">
			<table><STORED_PROCEDURE_NAME></table>
			<param1 type="INTEGER">1</param1>
		</storedProcedureName >
	</StatementName>
</root>

Step 5:

Add Content Modifier to make sure if none of the action item mentioned it will give response back as Enter valid Action.

Step 6:

Add JDBC receiver adapter and enter JDBC Data Source name you have created.

 

Go ahead and deploy your Iflow. Once deployed get runtime URL from Monitoring -> Manage Artifact-> Select endpoint url.

Now get into post man tool and import the below JSON collection. Make sure to update url /password/body as required.

 

{
	"info": {
		"_postman_id": "fe787994-b71a-4020-a8f8-25ddfcff5d55",
		"name": "MS_SQL_ONPREM",
		"schema": "https://schema.getpostman.com/json/collection/v2.1.0/collection.json"
	},
	"item": [
		{
			"name": "SELECT DATA",
			"protocolProfileBehavior": {
				"disableBodyPruning": true
			},
			"request": {
				"auth": {
					"type": "basic",
					"basic": [
						{
							"key": "password",
							"value": "PASSWORD",
							"type": "string"
						},
						{
							"key": "username",
							"value": "USER",
							"type": "string"
						},
						{
							"key": "saveHelperData",
							"type": "any"
						},
						{
							"key": "showPassword",
							"value": false,
							"type": "boolean"
						}
					]
				},
				"method": "GET",
				"header": [
					{
						"key": "Action",
						"value": "QUERY",
						"type": "text"
					}
				],
				"body": {
					"mode": "raw",
					"raw": "Select * from <SCHEMA_NAME>.<TABLENAME>;"
				},
				"url": {
					"raw": "https://<RUNTIME_URL>/http/UpdateMSSQL",
					"protocol": "https",
					"host": [
						"<RUNTIME_URL>"
					],
					"path": [
						"http",
						"UpdateMSSQL"
					]
				}
			},
			"response": []
		},
		{
			"name": "STORED PROCEDURE",
			"protocolProfileBehavior": {
				"disableBodyPruning": true
			},
			"request": {
				"auth": {
					"type": "basic",
					"basic": [
						{
							"key": "password",
							"value": "PASSWORD",
							"type": "string"
						},
						{
							"key": "username",
							"value": "USERNAME",
							"type": "string"
						},
						{
							"key": "saveHelperData",
							"type": "any"
						},
						{
							"key": "showPassword",
							"value": false,
							"type": "boolean"
						}
					]
				},
				"method": "GET",
				"header": [
					{
						"key": "Action",
						"value": "STOREDPROCEDURE",
						"type": "text"
					}
				],
				"body": {
					"mode": "raw",
					"raw": "<root>\r\n\t<StatementName>\r\n\t\t<storedProcedureName action=\"EXECUTE\">\r\n\t\t\t<table>What_DB_is_this5</table>\t\t\r\n\t\t</storedProcedureName >\r\n\t</StatementName>\r\n</root>"
				},
				"url": {
					"raw": "https://<RUNTIME_URL>/http/UpdateMSSQL",
					"protocol": "https",
					"host": [
						"<RUNTIME_URL>"
					],
					"path": [
						"http",
						"UpdateMSSQL"
					]
				}
			},
			"response": []
		},
		{
			"name": "STORED PROCEDURE PARAM",
			"protocolProfileBehavior": {
				"disableBodyPruning": true
			},
			"request": {
				"auth": {
					"type": "basic",
					"basic": [
						{
							"key": "password",
							"value": "PASSWORD",
							"type": "string"
						},
						{
							"key": "username",
							"value": "USER",
							"type": "string"
						},
						{
							"key": "saveHelperData",
							"type": "any"
						},
						{
							"key": "showPassword",
							"value": false,
							"type": "boolean"
						}
					]
				},
				"method": "GET",
				"header": [
					{
						"key": "Action",
						"value": "STOREDPROCEDURE",
						"type": "text"
					}
				],
				"body": {
					"mode": "raw",
					"raw": "<root>\r\n\t<StatementName>\r\n\t\t<storedProcedureName action=\"EXECUTE\">\r\n\t\t\t<table>What_DB_is_that</table>\r\n\t\t\t<param1 type=\"INTEGER\">1</param1>\r\n\t\t</storedProcedureName >\r\n\t</StatementName>\r\n</root>"
				},
				"url": {
					"raw": "https://<RUNTIME_URL>/http/UpdateMSSQL",
					"protocol": "https",
					"host": [
						"<RUNTIME_URL>"
					],
					"path": [
						"http",
						"UpdateMSSQL"
					]
				}
			},
			"response": []
		},
		{
			"name": "INSERT DATA",
			"protocolProfileBehavior": {
				"disableBodyPruning": true
			},
			"request": {
				"auth": {
					"type": "basic",
					"basic": [
						{
							"key": "password",
							"value": "PASSWORD",
							"type": "string"
						},
						{
							"key": "username",
							"value": "USER",
							"type": "string"
						},
						{
							"key": "saveHelperData",
							"type": "any"
						},
						{
							"key": "showPassword",
							"value": false,
							"type": "boolean"
						}
					]
				},
				"method": "GET",
				"header": [
					{
						"key": "Action",
						"value": "INSERT",
						"type": "text"
					}
				],
				"body": {
					"mode": "raw",
					"raw": "INSERT INTO <SCHEMA_NAME>.<TABLENAME> (\r\n    ID,\r\n    COMPANY_NAME,\r\n    COMPANY_CODE,\r\n    LOCATION,\r\n    ADDRESS_1,\r\n    ADDRESS_2,\r\n    STATE,\r\n    COUNTRY,\r\n    PINCODE,\r\n    EMAIL,\r\n    WEBSITE,\r\n    PHONE_1,\r\n    PHONE_2,\r\n    NMBR_OF_EMPLOYEES,\r\n    FACILITIES,\r\n    PROCESSED\r\n)\r\nVALUES\r\n    (\r\n        'COMPANY 1S',\r\n        'C101S',\r\n        'LOCATION 1S',\r\n        'ADDRESS 1S',\r\n\t\t'ADDRESS 2S',\r\n\t\t'STATE 1S',\r\n\t\t'COUNTRY 1S',\r\n\t\t560085,\r\n\t\t'c1S.email@email',\r\n\t\t'company1S.com',\r\n\t\t12345,\r\n\t\t12342,\r\n\t\t25,\r\n\t\t'FACILITIES 1S',\r\n\t\t'NO'\t\r\n\t\t\r\n    ),\r\n    (\r\n        'COMPANY 2S',\r\n        'C102S',\r\n        'LOCATION 2S',\r\n        'ADDRESS 2S',\r\n\t\t'ADDRESS 2S',\r\n\t\t'STATE 2S',\r\n\t\t'COUNTRY 3S',\r\n\t\t560085,\r\n\t\t'c2S.email@email',\r\n\t\t'company2S.com',\r\n\t\t12345,\r\n\t\t12342,\r\n\t\t25,\r\n\t\t'FACILITIES 2S',\r\n\t\t'NO'\r\n    ),\r\n    (\r\n       'COMPANY 3S',\r\n        'C103S',\r\n        'LOCATION 3S',\r\n        'ADDRESS 3S',\r\n\t\t'ADDRESS 3S',\r\n\t\t'STATE 3S',\r\n\t\t'COUNTRY 3S',\r\n\t\t560085,\r\n\t\t'c3S.email@email',\r\n\t\t'company3S.com',\r\n\t\t12345,\r\n\t\t12342,\r\n\t\t25,\r\n\t\t'FACILITIES 3S',\r\n\t\t'NO'\r\n    );\r\n\t"
				},
				"url": {
					"raw": "https://<RUNTIME_URL>/http/UpdateMSSQL",
					"protocol": "https",
					"host": [
						"<RUNTIME_URL>"
					],
					"path": [
						"http",
						"UpdateMSSQL"
					]
				}
			},
			"response": []
		}
	],
	"protocolProfileBehavior": {}
}

 

Once you update you should be able to see below

 

Then go ahead with your testing end to end. Hope it gives clear idea on overall steps required to have JDBC scenario working.

 

Cheers!

 

Regards,

Sriprasad Shivaram Bhat

 

Assigned tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Yogananda Muthaiah
      Yogananda Muthaiah

      Great Article for CPI folks... Keep sharing more interesting Sriprasad Shivaram Bhat

      Author's profile photo SREEHARI THAMMISETTI
      SREEHARI THAMMISETTI

      Awesome Article for Integration folks!! Thank you Sriprasad.

      Author's profile photo Murali Shanmugham
      Murali Shanmugham

      Thanks for sharing

      Author's profile photo anitra carol
      anitra carol

      This is really a helpful blog I am really impressed with your work, keep it up the good work

      SAP Cloud Platform Integration service helps you to connect cloud and on-premise applications with other SAP and non-SAP cloud and on-premise applications. This service has the capabilities to process messages in real-time scenarios spanning different companies, organizations, or departments within one organization.

      Author's profile photo Praveen Tirumareddy
      Praveen Tirumareddy

      Thanks sri, once again for a detailed blog !!

      regards,

      Praveen T

      Author's profile photo Sharma Ashwini
      Sharma Ashwini

      Thanks Sri.

      Author's profile photo Saurabh Kabra
      Saurabh Kabra

      Thank Sri for writing such a detailed & well-explained article.

      Author's profile photo Nishant Bhaskar
      Nishant Bhaskar

      Thanks Sriprasad Shivaram Bhat for the article. Could you please share a detailed guide as to how you created that IFlow. Thanks again.