Seems incompatible with system-wide drivers/DSNs on Linux/Mac

[akdor1154]

Hey,
This is probably more discussion than issue, so feel free to let me know if this should be raised some other way :)
First, thanks for the huge amount of work you've put in to the re-write!

I have a question/issue - I am using Ubuntu 20.04, and have drivers set up in /etc/odbcinst.ini as AFAIK is standard for unixODBC. However, I can't seem to get ODBC.jl to recognize them since v1.0. This seems to be by design - my understanding behind your design is that on Windows, ODBC.jl will use the system configuration (as it is predictable and documented), and on Linux/Mac, ODBC.jl uses its own internal configuration (as system config is neither predictable nor documented! :) )

This lack of system-wide config is problematic.. especially when you consider the use of Julia environments.
If ODBC.jl was to be used in a global Julia environment, then I could probably whinge a bit but then put up with redeclaring my odbcinst.ini in the ODBC.jl directory once, or setting up a symlink, yadda yadda yadda.

However, if ODBC.jl is installed in independent Julia environments, then it means I need to manually configure all of my drivers independently for each project. This is a) a huge pain, and b) additionally painful if I'm sharing a Julia project (say an IJulia notebook with a project.toml describing its dependencies including ODBC.jl) with other people, as driver configuration on my system has nothing to do with other users of my code.

Is there a possible resolution to this? For my purposes, it would make sense if ODBC would attempt to use my system unixODBC by default, or at least its own unixODBC but look in my /etc/unixodbc.ini, without putting code to this effect in my notebooks. However I do not have your outlook on the issues others have had getting ODBC.jl to work on their unices, so I am not blessed with an understanding of the sorts of issues this would cause other people. :)

[akdor1154]

Sorry, that got to be a bit rambly and undirected. Maybe a more clear request would be:

• I'm writing a Jupyter/IJulia notebook, with an attached Project.toml that declares its dependencies.
• ODBC.jl is one of its dependencies.
• I want to share this project/notebook within my organization. Users of the notebook are all going to use the same physical database, but will be using a mix of Linux, Mac, and Windows on their PCs they are running the notebook on.

How can I set up the project/notebook so that they can run the notebook directly after doing git clone and pkg install?

Previously to ODBC.jl 1.0, I could tell them either "make sure your system has an ODBC driver installed and called ORG_DRIVER", or "make sure your system has a DSN configured and called ORG_DSN". However this no longer seems to be possible.

[quinnj]

Thanks for opening an issue!

Yes, as you've noticed, the 1.0 release of ODBC.jl has taken a bit opinionated approach to odbc configuration by attempting to manage it "in house". This was definitely a conscious, deliberate decision aimed at helping the majority of users. The ODBC.jl repo has had some ~200 issues, in addition to personal pings on slack/discourse and the over-arching theme was just "how the heck do I get odbc configured correctly?".

In the 1.0 release, we did 3 specific things aimed at improving the situation. First, we now ship both unixODBC and iODBC binaries with the ODBC.jl installation; this avoids the need for users to need to install on their own, getting stuck on old or incompatible versions, compiled w/ unexpected flags, etc. Second, we started setting our own internal ODBCINI and ODBCSYSINI environment variables to a fixed, ODBC.jl-managed location; this ensures we always can point at a single, known, managed location (so many headaches w/ people not knowing where to put odbcinst.ini files). And last, we added the ODBC.adddriver and ODBC.adddsn installation functions. This allows programmatic installation of drivers/dsns on the system.

With that context, and applied to your comments, I'd say it should be helpful? In the scenario you describe, you're putting more onus on users to "setup" their system; and I'm saying in my experience that can be a huge pain, lead to weird issues, and is generally a hard problem for casual users not familiar w/ how odbc configuration works. Alternatively, in ODBC.jl 1.0, we can move at least some of the configuration burden into ODBC.jl itself. i.e. the user will still need to download/install the odbc driver on their system, but once they've done that, you can just have:

ODBC.adddriver("odbc_driver", "path/to/driver/library")

# and optionally
ODBC.adddsn("odbc_dsn", "odbc_driver"; configurations...)

And indeed, I recommend putting that as the first "cell" in your notebook as a way to help users w/ installation. You could also put the calls in os-specific blocks if that might be easier; for example, on windows, when you install a driver, it will typically do the ODBC.adddriver call for you. So you could put in your notebook cell:

if !Sys.iswindows()
    ODBC.adddriver(...)
end

To wrap up, I do want to support the use-case of "let me use my already configured system odbc configuration". As you've found in #300 , we have some bugs to iron out, but I'd like to get that resolved to enable this. But I'd also encourage you to consider the "1.0" way of ODBC.jl and maybe it will make life easier for people you're working with out there.

[quinnj]

Fixed via #302

[akdor1154]

nice!! this looks perfect, I'll have a play. Maintainer ranking 11/10.

wrt the 1.0 way.. next time I need to help someone set up their odbc stuff I will certainly try that. Just for the sake of giving my thoughts as a user: the main turnoffs for this atm are 1) platform-specific boilerplate code in every notebook, and 2) my notebooks will need to know where the .dll/.dylib/.so ends up on every platform. this is gonna be different even maybe between versions of the same driver, and it might be a pain for linux consumers - - their workflow now is normally "download a .tar.xz bundle from vendor", "extract it somewhere arbitrary", "run an install script to register it with sys odbc". This would need to turn "somewhere arbitrary" into "where some akdor1154 guy who wrote this notebook I want to run wants it to go".

Another question: is it even possible, from a technical perspective, for the same ODBC.jl release to dynamically link against either the system unixODBC, or the bundled ODBC, based on some runtime condition?