JDBC connection from SAP PI to HANA - Read from a View instead of Table

[Former Member]

Hello,

We have a scenario where we need to read data from HANA DB using JDBC connection via PI 7.31 SP12. Though we are able to read the data successfully from a table on HANA db the requirement is to read the data from a view on HANA DB. Is it feasible to  read the data from the a view and if yes could you please share the schema for it as well or direct me to the required documentation.

The schema being used to read from the Table:

<?xml version="1.0" encoding="UTF-8" ?>

<ns0:MT_KNA1_HANA_Request xmlns:ns0="http://abc.com/hana">

- <Statement>

- <dbTableName action="SELECT">

<table>ECCDATA.KNA1</table>

- <access>

<MANDT />

<KUNNR />

<LAND1 />

<PSTLZ />

</access>

- <key>

<MANDT>080</MANDT>

<KUNNR>0000000106</KUNNR>

</key>

</dbTableName>

</Statement>

</ns0:MT_KNA1_HANA_Request>

Thanks,

Ashish

[bhavesh_kantilal · Accepted solution]

The JDBC adapter converts the xml to a select query.

So, as with performing a select over a view the table name in the JDBC request xml should be the name of the view.

Did you try this?

Regards

Bhavesh

[Former Member · Accepted solution]

Thanks for the reply. Please see the error below when I use the view name instead of the table name in the schema:

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:MT_KNA1_HANA_Request xmlns:ns0="http://abc.com/hana">

- <Statement>

- <dbTableName action="SELECT">

<table>_SYS_BIC.ABC_FUSION.SD.Orderhistory/ORDHIST</table>

- <access>

<VBELN />

<VBTYP />

<ERDAT />

</access>

- <key>

<W_DATE_FROM>20150101</W_DATE_FROM>

<W_DATE_TO>20151231</W_DATE_TO>

<VKORG>0800</VKORG>

<VTWEG>84</VTWEG>

<SPART>80</SPART>

</key>

</dbTableName>

</Statement>

</ns0:MT_KNA1_HANA_Request>

Error While Sending Message: Additional error text from response: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. '_SYS_BIC.ABC_FUSION.SD.Orderhistory/ORDHIST' (structure 'Statement'): java.sql.SQLException: FATAL ERROR: Column 'W_DATE_FROM' does not exist in table '_SYS_BIC.ABC_FUSION.SD.Orderhistory/ORDHIST'

[bhavesh_kantilal · Accepted solution]

The error states that column W_DATE__FROM does not exist in this view.

Have you executed the sql query standalone?

Looks.like your query is incorrect

[Former Member · Accepted solution]

Just FYI...the issue was resolved after the following three were fixed.

1. PI was updated to pass "_SYS_BIC"."ABC_FUSION.TEST_ORDER.ORDER_HISTORY/VBAK_VBAP" instead of just _SYS_BIC.ABC_FUSION.TEST_ORDER.ORDER_HISTORY/VBAK_VBAP i.e "RuntimeSchema"."PackageName/ViewName"
2. Schema (UserID) did not have access to the view.
3. Fields 'W_DATE_FROM' and 'W_DATE_TO' were missing in the view.

Bhavesh thanks for your help.

[former_member804778 · Accepted solution]

Hi All,

Any drivers needs to be installed in SAP PO for connecting to hana Cloud DB.

Regards,

Karthiga