Providing a path to a driver directly?

[MarcellGranat]

Hi, ✌️

I am using Mac M1 🍎 and as for many the installation of instantclient didn't went well 🥵. For installation it creates a new folder "instantclient_23_3" in the downloads folder, but odbc::odbcListDrivers() cannot recognise that.

In the description of the function it's written that: Driver names that are not configured with the driver manager (and thus do not appear in this function's output) can still be used in dbConnect by providing a path to a driver directly. Although it requires and odbc::odbc() input, where I cannot provide the path.

I only saw in #709 that you can achieve similar with Sys.setenv(ODBCSYSINI = ""), but after the installation from the website I do not see any odbcinst.ini file in the folder.

Any suggestion how to provide the path then?

Thanks in advance! 🙏

[simonpcouch]

Hey @MarcellGranat, thanks for the issue. Oracle is indeed painful to work with.🙃 You're right—you'll need to tell odbc where to find that Instant Client.

First, note that you'll need both the "Basic Package" and the "ODBC Package" linked on that Instant Client page and to fully follow the "Installation Instructions" dropdown on that page to merge them into that one instantclient_23_3 folder.

For the more R odbc-specific slant: run odbc::odbcEditDrivers() to pull up your odbcinst.ini file and then add the Oracle driver configuration to it using something like:

[Oracle]
Description=Oracle ODBC Driver
Driver=/Users/YOUR_USERNAME/Downloads/instantclient_23_3/libsqora.dylib.23.3

At that point, you should be able to see the driver in your odbcListDrivers().

[MarcellGranat]

Thank for the quick reaction! 🙌

I modified the odbcinst.ini file as you described, but the connection still runs into an error.

odbc::dbConnect(odbc::odbc(), 
                Driver = "Oracle ODBC Driver",
                DBQ = connect_string, 
                UID = schema, 
                PWD = Sys.getenv(paste0("OCI_", schema, "_PW")),
                encoding = "")

> Error in `odbc::dbConnect()`:
! ODBC failed with error 00000 from [unixODBC][Driver Manager].
✖ Can't open lib '/Users/marci/Downloads/instantclient_23_3/libsqora.dylib.23.1'
  : file not found
ℹ From [nanodbc/nanodbc.cpp:1150](vscode-file://vscode-app/Applications/Positron.app/Contents/Resources/app/out/vs/code/electron-sandbox/workbench/workbench.html#).

But the file does exist 🥲

I also copied the whole folder into wd, to check whether the issue is that R cannot read external folders, but I still get the same error. 🤯

[simonpcouch]

Been there! If you see a "file not found" error for a driver that does exist, it's because some dependency for the driver doesn't exist; use otool -L /Users/marci/Downloads/instantclient_23_3/libsqora.dylib.23.1 to find the dependencies and then symlink them from elsewhere on your computer into where the driver looks for them.

[simonpcouch]

Note to odbc developers: instantclient.*file not found may be a good use case for contextualize_database_error():

odbc/R/utils.R

Line 212 in 629595a

contextualize_database_error <- function(cnd_body) {

Where we could append some (OS-specific) advice along the lines of that recommended in the comment above.

[MarcellGranat]

Thanks, that helped truly a lot! 🙏

Now I figured out that /usr/lib/libresolv.9.dylib and /usr/lib/libSystem.B.dylib are not in the /usr/lib/ folder instead, they are placed at "/Library/Developer/CoreSimulator/Volumes/iOS_22B81/Library/Developer/CoreSimulator/Profiles/Runtimes/iOS 18.1.simruntime/Contents/Resources/RuntimeRoot/usr/lib/libSystem.B.dylib"

I cannot even copy or symlink them even with sudo. 😳