[SOLVED] Read a View created from a procedure in SAP HANA from R

Read a View created from a procedure in SAP HANA from R

I have schema in SAP HANA by the name "HYZ_ProcurementToSales" and View "V_HYZ_P25_Market_Market_Orders" which is created from a procedure, I am trying to extract the view in the R server version 1.0.153. The code I am using is:

library(RJDBC)
conn_server <- dbConnect(jdbcDriver,
"jdbc:sap:rdkom12.dhcp.pal.sap.corp:30015", "system",
"manager")
res <- dbGetQuery(conn,"select * from 
HYZ_ProcurementToSales.V_HYZ_P25_Market_Market_Orders")

The error that I get is this:

"Unable to retrieve JDBC result set for 
select * from HYZ_ProcurementToSales.V_HYZ_P25_Market_Market_Orders".

My belief is that something else instead of dbGetQuery will do the trick here. It works fine if I simply do

res <- dbGetQuery(conn,"select * from Tables")

Solution

The following works for me on HANA 1 SPS12 with a procedure that exposes a view called V_CURRENTUSERS:

library(RJDBC)
drv <- JDBC("com.sap.db.jdbc.Driver",
            "C:\\Program Files\\SAP\\hdbclient\\ngdbc.jar",
            identifier.quote='"')
conn <- dbConnect(drv, "jdbc:sap://<hanaserver>:3<instance>15/?", "**username**", "*pw*")

jusers <- dbFetch(dbSendQuery(conn = conn, statement = 'select * from v_currentusers;'))

At this point, the whole result set is bound to jusers. Once finished you should release the result set again:

dbClearResult(jusers)

and finally close the connection

dbDisconnect(conn)

Be aware that procedures with result views are deprecated and should not be used/developed anymore. Instead, use table functions as these can also be reused in information views and allow for dynamic parameter assignment.