SQL Anywhere linux odbc SQL_HANDLE_HENV error

Question

I've got a linux server running RStudio, and I'm trying to connect to an SQL Anywhere database.

I have the drivers installed and configured, and I can connect using iSQL. When trying through RStudio, I continually get this error:

Error: nanodbc/nanodbc.cpp:983: 00000: [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed 

However, if I launch an R script straight from /opt/bin/r/rscript, it connects.

The same thing happens when trying to connect with Python through a conda environment in my home directory. However, if I launch by typing "python test.py" into the terminal, the connection succeeds.

I'm on Ubuntu 18.04 with the SQL Anywhere 17 drivers. Any ideas would be appriciated.

Thanks.

Answer 1

I just solved this issue with a very similar setup: Connecting to a SQL Anywhere database, where the connection works from R when launched from the command line, but not from RStudio, and gives the error:

SQLAllocHandle on SQL_HANDLE_HENV failed

The key was to set the environment variables in RStudio to match those in my regular shell. In my case, these were $ODBCINI and $LD_LIBRARY_PATH. I reset them as follows:

1. In the shell, ran the following to get the values being used by console R.

   echo $ODBCINI
   echo $LD_LIBRARY_PATH
   

2. In RStudio, ran Sys.getenv() to confirm these values were different.

3. Reset the variables to match with

   Sys.setenv(ODBCINI = "[path from shell]/odbc.ini")
   Sys.setenv("LD_LIBRARY_PATH" = paste0(Sys.getenv("LD_LIBRARY_PATH"),":[user path from shell]"))
   

With this setup, I was able to connect from RStudio.