Installing odbc R package in conda env in WSL

Question

I am trying to

install.packages("odbc")

inside an R 4.2.2 kernel running in anaconda, in WSL (Ubuntu-22.04 distro). The reason I am running anaconda in WSL rather than in Windows is because I need R=4.2.2, and the Windows R base on anaconda is a bit behind at 4.1.3 (see https://anaconda.org/conda-forge/r-base)

I can install.packages("odbc") on a Windows install of anaconda, just not on an anaconda installation on Ubuntu in WSL.

When running install.packages("odbc")

I get this error:

trying URL 'https://cran.stat.auckland.ac.nz/src/contrib/odbc_1.3.4.tar.gz'
Content type 'application/x-gzip' length 338480 bytes (330 KB)
==================================================
downloaded 330 KB

* installing *source* package ‘odbc’ ...
** package ‘odbc’ successfully unpacked and MD5 sums checked
** using staged installation
PKG_CFLAGS=
PKG_LIBS=-lodbc
<stdin>:1:10: fatal error: sql.h: No such file or directory
compilation terminated.
------------------------- ANTICONF ERROR ---------------------------
Configuration failed because odbc was not found. Try installing:
 * deb: unixodbc-dev (Debian, Ubuntu, etc)
 * rpm: unixODBC-devel (Fedora, CentOS, RHEL)
 * csw: unixodbc_dev (Solaris)
 * pacman: unixodbc (Archlinux, Manjaro, etc)
 * brew: unixodbc (Mac OSX)
To use a custom odbc set INCLUDE_DIR and LIB_DIR and PKG_LIBS manually via:
R CMD INSTALL --configure-vars='INCLUDE_DIR=... LIB_DIR=... PKG_LIBS=...'
--------------------------------------------------------------------
ERROR: configuration failed for package ‘odbc’
* removing ‘/home/toma/anaconda3/envs/r_422/lib/R/library/odbc’

The downloaded source packages are in
        ‘/tmp/RtmpzR29mc/downloaded_packages’
Updating HTML index of packages in '.Library'
Making 'packages.html' ... done
Warning message:
In install.packages("odbc") :
  installation of package ‘odbc’ had non-zero exit status

I have done the suggested sudo apt-get install unixodbc unixodbc-dev, and have these versions:

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
unixodbc-dev is already the newest version (2.3.9-5).
unixodbc is already the newest version (2.3.9-5).
0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded.

This didn't work, so I followed these instructions from https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server

if ! [[ "18.04 20.04 22.04" == *"$(lsb_release -rs)"* ]];
then
    echo "Ubuntu $(lsb_release -rs) is not currently supported.";
    exit;
fi

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev

I still get the earlier error about missing sql.h, however this file does exist in

~anaconda3/pkgs/unixodbc-2.3.11-h5eee18b_0/include/sql.h
~anaconda3/include/sql.h

Any ideas?

Answer 1

as the conf error states: Configuration failed because odbc was not found (!) . So you have the needed dependencies installed (twice); but your R session can't access them, because the PATH vars are not provided. This could be due to the way your conda envs are set up. It seems you want to use r_422 at ‘/home/toma/anaconda3/envs/r_422'

Point R to the unixODBC installation by providing the path during or prior of the install.

As the message suggests, you could call R from bash in following fashion :

R CMD INSTALL --configure-vars='INCLUDE_DIR=... LIB_DIR=... PKG_LIBS=...

...or from inside of R:

install.packages("odbc", configure.vars=c("LIB_DIR='r_422/lib/R/library/' INCLUDE_DIR='anaconda3/pkgs/unixodbc-2.3.11-h5eee18b_0/include/sql.h''"))

... or add the path to .Renviron file, and restart R

echo "INCLUDE_DIR='anaconda3/pkgs/unixodbc-2.3.11-h5eee18b_0/include/sql.h'" > .Renviron

... with the right paths to unixODBC (either the one that you manually installed into conda pkgs or the system wide version you installed through apt) To find the location of the apt version of odbc you could use dpkg list:

dpkg -L unixodbc

This will give you a list of the source files of unixodbc.