How to GET values from an external system with API REST, then insert values into a HANA DB

[MohamedAharchi]

Hello Guys,

I'm new in SAP Cloud Platform Process Integration. I explain my issue. I'm trying to GET values from an external system by using an API REST as json format and then insert values into a HANA DB with SCP-PI.

So I used the feature Request-reply, then I have a Content-Modifier in order to make an SQL Statement to the HANA DB by using JDBC connection. Currently the SQL Statement via JDBC connection is working fine. But I don't now how to catch response of Request-Reply and how to implement JSON response inside an SQLSCRIPT.

Help please !

Regards,

[MortenWittrock · Accepted solution]

Hi Mohamed

You are close to having a working integration!

After the Request-Reply step, the message payload is the JSON response from the external system. From this response, you need to extract the values you need (I'll get back to how to do that), and then insert them into the SQL statement.

If you store a value in a property, you can insert it directly into the SQL in your Content Modifier step using this notation:

${property.SomeProperty}

Where SomeProperty is the name of the property.

So, how do you extract the values and store them in a property? Depending on the complexity of the reply, you might use the built-in JSON to XML converter step, and then extract the value in a Content Modifier using an XPath expression.

Alternatively, you can extract the values in a Groovy script step using the JsonSlurper class. You can see an example of JSON parsing here, written by sriprasadshivaramabhat.

Have fun,

Morten

[MohamedAharchi · Accepted solution]

Hi Morten,

Thank you for your quick reply. Ok I see it's more clear for me, I'm coming to try this solution and I'll come back to you.

Mohamed.

[MohamedAharchi · Accepted solution]

Hello Morten,

It's now working! I'm able to execute an SQL Statement.

My need is to import several data to the HANA DB via JDBC connection.

So when I fullfit my body with only one SQL INSERT statement it's working but when I have several INSERT I have an issue.

My question is how to push several SQL INSERT statement via JDBC connector ?

Mohamed.

[MortenWittrock · Accepted solution]

Hi mohamedaharchi

I don't know if multiple native SQL statements works. If not, you can use the XML SQL format, which definitely supports multiple inserts. Also, that turns your task into one of mapping, rather than text processing. The former has much better support in CPI.

Regards,

Morten

[MohamedAharchi]

Hello,

Yep! Thanks again for your support.

Regards,

Mohamed.

[MohamedAharchi]

Hi Morten,

Thank you very much for your solution, I'm able now to make a multiple insert or update by using XML SQL format and a Splitter.

But I have another issue. On XML SQL Format I want to make an action UPDATE_INSERT and that's not woking. I'm facing to the following error:

com.sap.it.rt.adapter.jdbc.exceptions.JDBCException: Action attribute found was empty or unsupported. XML cannot be processed.

And here my XML SQL Format. When I use UPDATE or INSERT it's working but action UPDATE_INSERT it's not working

<root>
	<STATEMENTNAME>
		<dbTableName action="UPDATE_INSERT">
			<table>S0018528459.FROMCPI</table>
			<access>
				<PRODUCTID>ASM0023680123</PRODUCTID>
				<PRODUCTNAME>BLABLABLA</PRODUCTNAME>
			</access>
			<key>
				<PRODUCTID>ASM0023680123</PRODUCTID>
			</key>
		</dbTableName>
	</STATEMENTNAME>
</root>

Regards,

Mohamed.

[MortenWittrock]

Hello again

UPSERT is not supported for the XML SQL format at the moment, it seems. This is documented (scroll down a bit) on help.sap.com.

I believe I've answered your questions, so I'd be happy if you would go ahead and accept the answer.

Regards,

Morten