rodbchanarjdbc

R SAP Hana Integration with ODBC for Windows


I would like to connect to SAP Hana Database from RStudio. I've tested several libraries such as:

And finally after some readings and research, I've determined that ODBC is the newer library instead of RODBC, and it has better performance compared to RJDBC.

I've started with the RStudio documentations:

1- SAP HANA Driver Installation link

2- RStudio introduction to ODBC package link

3- ODBC Package GitHub Page link

After following the driver installation for Windows 64-bit, I can see the driver HDBODBC from RStudio:

> library(odbc)
> sort(unique(odbcListDrivers()[[1]]))
[1] "Amazon Redshift (x64)"  "HDBODBC"  "ODBC Driver 17 for SQL Server"  "PostgreSQL ANSI(x64)"  "PostgreSQL Unicode(x64)"      
[6] "SQL Server"                   
> 

The below image is a capture when I was configuring the DSN where I provide the IP and Port:

enter image description here

enter image description here

Errors:

1- This is what I get when I attempt connecting to DSN:

> library(odbc)
> con <- dbConnect(odbc(), "HAN")
Error: nanodbc/nanodbc.cpp:983: HY000: [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;1033 error while parsing protocol 
> 

2- This is what I get while I attempt to connect without DSN:

library(DBI)
con <- dbConnect(odbc::odbc(),
  driver = "HDBODBC",
  uid = "<user>",
  pwd = "<pass>",
  host = "<ip>",
  port = <port>)  

Error: nanodbc/nanodbc.cpp:983: HY000: [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;-10719 Connect failed (invalid SERVERNODE '')

Miscellaneous troubleshooting information:

I've tested a connection and it is successful.

I used this with Tableau and it worked.

I've also used RJDBC and the java driver and it is working as well, but the performance is really bad:

jdbcDriver <- JDBC(driverClass="com.sap.db.jdbc.Driver",  
                   classPath="C:\\Program Files\\SAP\\hdbclient\\ngdbc.jar")

jdbcConnection <- dbConnect(jdbcDriver,
                            "jdbc:sap://<ip>:<port>/?autocommit=false",
                            "<username>",
                            key_get("<service>", keyring = "<keyring>"))

Versions:

Windows:

OS Name:                   Microsoft Windows 10 Enterprise
OS Version:                10.0.18363 N/A Build 18363
System Type:               x64-based PC

R:

> version
               _                           
platform       x86_64-w64-mingw32          
arch           x86_64                      
os             mingw32                     
system         x86_64, mingw32             
status                                     
major          3                           
minor          6.3                         
year           2020                        
month          02                          
day            29                          
svn rev        77875                       
language       R                           
version.string R version 3.6.3 (2020-02-29)
nickname       Holding the Windsock        
> 

Solution

  • In your attempt without DSN the parameter ServerNode is missing; you used HOST instead, which is not the same parameter.

    See the reference documentation here for details.