Connecting to ODBC with %LOAD

[lucharo]

Hello guys, I've been watching this project for a while and my only option to use this project is to connect to my DB endpoints via ODBC. Unfortunately I have seen you have no example nor documentation for that use case.

I am trying to use xeus-sql's %LOAD to connect to my ODBC driver and I am certainly doing something wrong, I would love if you can provide some help:

In [1] %LOAD odbc 'HiveANAHDSN'
Out [1] Error: Failed to find shared library for backend odbc

Note: HiveANAHDSN is the name of my ODBC driver Data Source Name (DSN)

[lucharo]

Hi @marimeireles, any advice? :)

[lucharo]

Hi again,

I've managed to connect to ODBC on Windows. My previous attempt was through the WSL (version 1) and it did not seem to find the backend odbc library (libsoci_odbc.dll ?).

Regarding windows, the way the ODBC connection works is different to the other ones (Postgres, MySQL...), When I run %LOAD odbc with no further arguments a 'Select Data Source' GUI appears and then I can select the correct DSN. I am wondering though if there is anyway to specify the DSN to use in the Jupyter cell, as with the other SQL backends

Could be relevant: ODBC Connection String Keyword list

[marimeireles]

Hey @lc5415, sorry for being non responsive here. I was in vacation. That's great you figured out a solution! :)
Unfortunately I don't own a windows machine and haven't own one for years, I'm clueless with windows, sorry I can't be much help here.
But if you want to share your founds with other folks, I'm pretty sure it will be very appreciated. If you can, feel free to edit this file https://github.com/jupyter-xeus/xeus-sql/blob/master/docs/source/ODBC.rst with your step by step.

I am wondering though if there is anyway to specify the DSN to use in the Jupyter cell, as with the other SQL backends

Regarding this... It's weird because the SOCI docs don't mention any special treatment for windows. It should work as the other stuff is working. maybe they're also lacking in docs.
Anyways, I'll keep the issue open!

[mrshu]

Would be indeed very cool to get some more docs for ODBC -- there are a couple databases where I could see it being immediately useful :)

[gwerbin]

ODBC support would be much appreciated for Snowflake users!

I set up a connection using odbcinst on my Mac, I will try and report back if it works.

[gwerbin]

I ended up with the same error on MacOS with Xeus-SQL installed in a Conda/Mamba environment:

Error: Failed to find shared library for backend odbc

What library is required here? I tried installing unixodbc from Conda Forge in the environment, but that didn't fix the problem.

I am able to connect with iodbctest dsn=..., so maybe I need to somehow instruct Xeus or SOCI where to find the required library?