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.
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
- Create a Stored Procedure
- Create Stored Procedure with input parameter.
- Create Stored Procedure which calls Function internally.
- 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;
goClick 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;
goStored Procedure Internally calling function:
create procedure PROC_PRINT_HW
as
select dbo.helloworldfunction()
goFunction:
CREATE FUNCTION dbo.helloworldfunction()
RETURNS varchar(20)
AS
BEGIN
RETURN 'Hello world'
END
goCreating 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
- SAP Managed Tags:
7 Comments
