Unable to install odbc package on RStudio Pro - Posit Workbench

Posit Professional & Hosted Products
1

Hi,

We have configured and installed RStudio Pro 2023.12.1 (Posit Workbench) software on our Azure Linux VM (Ubuntu) and now after logging into the software over chrome.

I wanted to connect to our Azure Datawarehouse service where I have our dataset and table present using the code below.

library(odbc)
con <- dbConnect(odbc(),
                 Driver = "ODBC Driver 17 for SQL Server",
                 Server = "sqldwserver.database.windows.net",
                 Database = "sqldwdb",
                 UID = "USER",
                 PWD = rstudioapi::askForPassword("Database password"),
                 Port = 1433)

But post running the code I am getting error as below,

Error in dbConnect(odbc(), Driver = "ODBC Driver 17 for SQL Server", Server = "ucbsqldwserver.database.windows.net", :
could not find function "dbConnect"

When I tried to install the odbc package, I encountered the following error. Based on this error, I understand that the odbc package is not getting installed on the Linux vm, which is preventing me from making a connection.

I was able to run the above code on my Windows Azure VM where I have RStudio desktop free version installed, but I am encountering this error on my Post Workbench Web version which is installed on Linux VM.

Could you please help me fix this error as I have limited knowledge of both R and Linux OS?

Error for odbc package installation --

install.packages("odbc")
Installing package into ‘/home/SurajP/R/x86_64-pc-linux-gnu-library/3.6’
(as ‘lib’ is unspecified)
trying URL 'https://cloud.r-project.org/src/contrib/odbc_1.5.0.tar.gz'
Content type 'application/x-gzip' length 299106 bytes (292 KB)

downloaded 292 KB

  • installing source package ‘odbc’ ...
    ** package ‘odbc’ successfully unpacked and MD5 sums checked
    ** using staged installation
    Found pkg-config cflags and libs!
    PKG_CFLAGS=
    PKG_LIBS=-lodbc
    :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/SurajP/R/x86_64-pc-linux-gnu-library/3.6/odbc’
    Warning in install.packages :
    installation of package ‘odbc’ had non-zero exit status

The downloaded source packages are in
‘/tmp/RtmpMHrTw7/downloaded_packages’

2

I think you need to do precisely what the error message says. Admittedly it is a bit crpytic but let me expand on it a bit. The error message indicates that you are missing some software provided by the Linux operating system.

Depending if you are on a RedHat/CentOS or Ubuntu system for Workbench, you need to run (using admin privileges, you may need to get someone else to run this) in a terminal/remote session on the Workbench VM

  • RedHat/CentOS: yum -y install unixODBC-devel
  • Ubuntu: apt-get update && apt-get install -y unixodbc-dev

after which the installation of the odbc package should succeed. The above command(s) will precisely install the needed OS package.

On a related note:

  • It seems you are using R version 3.6 which is slightly outdated. The latest available version is 4.4.1 - since you are on Workbench 2023.12.1 the latest R version compatible with this Workbench version is 4.3. With Workbench you can install as many R versions as you want (cf. Install R – Posit Docs) and then dynamically select/change them in the RStudio IDE as needed.
  • If you wanted to install the odbc package and make it available to all users on your workbench VM, you could (again using admin privileges, but now running within an R session started in the terminal/remote session) run install.packages("pak");pak::pak("odbc"). pak (cf. https://pak.r-lib.org) will not only install the R package odbc but also automatically detect and install the needed operating system dependency.