rjdbchanar-dbirjdbc

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.