Hana Cloud Database Integration with SAP Cloud Integration via JDBC part -2

[amysh95]

In the continuation of series Hana Cloud Database Integration with SAP Cloud Integration via JDBC part -1 here is the second blog post where I'll provide a demo on how to send data to the Hana cloud database.

It is mostly like what we do in SAP PI/PO, if you have worked before on. To send the data from CPI to Hana cloud DB we are having two methods.

1. By using standard XML architecture
2. By using SQL query directly

By using standard XML architecture

Below is the standard format for the XML which is use to insert the data into database irrespective of backend DB type. Also, I have mentioned the SAP documentation link for reference.

https://help.sap.com/saphelp_scm70/helpdata/ru/2e/96fd3f2d14e869e10000000a155106/frameset.htm

<root>
    <StatementName1>
        <dbTableName action="UPDATE">
            <table>SCHEMA.TABLENAME</table>
            <access>
                <col1>val1</col1>
                <col2>val2new</col2>
            </access>
            <key1>
                <col2>val2old</col2>
                <col4>val4</col4>
            </key1>
        </dbTableName>
    </StatementName1>
</root>

The list of supported methods is SELECT, INSREST, UPDATE, DELETE, INSERT_UPDATE”.

XML Filed name	Comments
Action (Xml attribute)	Specify the type of query Select, UPDATE, INSERT, INSERT_UPDATE etc.
Table	Table name with SCHEMA. eg: SCHEMA.TABLENAME
Access	Here specify the names of the fields in table as per the database
Key1	Specify the fields maintained as primary keys in database table

I have created one IFlow for where I have exposed one endpoint to get the JSON data and this data, I’m sending to the Hana cloud database via JDBC using INSERT_UPDATE as action type.

Below is the structure of the table inside the Hana cloud database.

Hana Cloud Database Table

IFlow design:

https endpoint ➡️ JSon-to-XML ➡️ Message Mapping ➡️ JDBC send ➡️ XML-to-JSon ➡️ Receiver

Integration Suite IFlow Design

Here is the input payload which I am passing from postman.

{
    "OrderDetails": {
        "ORDERNUMBER": "order2",
        "ORDERQUANTITY": "10",
        "ORDERITEM": "1",
        "ADDITIONALPHOTO": ""
    }
}

XML Payload content towards JDBC adapter.

<?xml version="1.0" encoding="UTF-8"?>
<root>
    <StatementName>
        <dbTableName action="UPDATE_INSERT">
            <table>DEVELOPER.ISORDER_DEMO</table>
            <access>
                <ORDERNUMBER>order2</ORDERNUMBER>
                <ORDERQUANTITY>10</ORDERQUANTITY>
                <ORDERITEM>1</ORDERITEM>
                <ADDITIONALPHOTO></ADDITIONALPHOTO>
            </access>
            <key1>
                <ORDERNUMBER>order2</ORDERNUMBER>
            </key1>
        </dbTableName>
    </StatementName>
</root>

Response From JDBC after converting into JSon.

Postman API Call

updated entries in the database Table.

Hana Database Table

In the next series of the blog will show if any media data needs to be push to database, how it can be done.

because in general base64 encoded string we send, but here in case of Hana cloud database that will not work.

Thanks

Aman Sharma