Bullseye can't find the MS SQL Server drivers

[julien-boudry]

I want to use Julia ODBC with Sql server. I wrote the following Dockerfile (instructions from https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16#debian18):

# Julia 1.8.5 on armv8 (64)
FROM julia:1.8-bullseye 

ENV ACCEPT_EULA=Y

# Install prerequisites required for tools and extensions installed later on.
RUN apt-get update \
    && apt-get install -y apt-transport-https gnupg2 libpng-dev libzip-dev nano unzip git sudo

RUN echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc

# Install prerequisites for the sqlsrv and pdo_sqlsrv PHP extensions.
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/11/prod.list > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get update \
    && apt-get install -y msodbcsql18 mssql-tools18 unixodbc-dev \
    && rm -rf /var/lib/apt/lists/*

The driver is installed:

odbcinst -j 
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

cat /etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1
UsageCount=1

Then, Julia can't find any driver:

julia> import ODBC
 │ Package ODBC not found, but a package named ODBC is available from a registry. 
 │ Install package?
 │   (@v1.8) pkg> add ODBC 
 └ (y/n/o) [y]: 
    Updating registry at `~/.julia/registries/General.toml`
   [...]

julia> ODBC.drivers()
Dict{String, String}()

julia> ODBC.adddriver("mssql","/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1")
ERROR: Unable to find component name
Stacktrace:
 [1] error(s::String)
   @ Base ./error.jl:35
 [2] adddriver(name::String, path::String; kw::Base.Pairs{Symbol, Union{}, Tuple{}, NamedTuple{(), Tuple{}}})
   @ ODBC.API ~/.julia/packages/ODBC/9VZTC/src/API.jl:559
 [3] adddriver
   @ ~/.julia/packages/ODBC/9VZTC/src/API.jl:594 [inlined]
 [4] #adddriver#51
   @ ~/.julia/packages/ODBC/9VZTC/src/ODBC.jl:154 [inlined]
 [5] adddriver(name::String, libpath::String)
   @ ODBC ~/.julia/packages/ODBC/9VZTC/src/ODBC.jl:154
 [6] top-level scope
   @ REPL[7]:1

I have also test with linux/amd64 platform (I'm on armv8), with the same result. Also try to install Snowflake ODBC driver from the official instructions, with the same result in Julia.

These drivers work well directly on PHP ODBC, but can't be configured with Julia.

[julien-boudry]

Very similar to #325

[julien-boudry]

Also, the test fails:

(@v1.8) pkg> test ODBC
[...]

     Testing Running tests...
[ Info: Enabled tracing of odbc library calls to /root/.julia/packages/ODBC/9VZTC/test/odbc.log
ODBC.drivers() = Dict{String, String}()
ODBC.dsns() = Dict{String, String}()
[ Info: Disabled tracing of odbc library calls
libpath = "/home/runner/mariadb64/mariadb-connector-odbc-3.1.11-ubuntu-focal-amd64/lib64/mariadb/libmaodbc.so"
isfile(libpath) = false
ERROR: LoadError: Unable to find component name
Stacktrace:
 [1] error(s::String)
   @ Base ./error.jl:35
 [2] adddriver(name::String, path::String; kw::Base.Pairs{Symbol, Union{}, Tuple{}, NamedTuple{(), Tuple{}}})
   @ ODBC.API ~/.julia/packages/ODBC/9VZTC/src/API.jl:559
 [3] adddriver
   @ ~/.julia/packages/ODBC/9VZTC/src/API.jl:594 [inlined]
 [4] #adddriver#51
   @ ~/.julia/packages/ODBC/9VZTC/src/ODBC.jl:154 [inlined]
 [5] adddriver(name::String, libpath::String)
   @ ODBC ~/.julia/packages/ODBC/9VZTC/src/ODBC.jl:154
 [6] top-level scope
   @ ~/.julia/packages/ODBC/9VZTC/test/runtests.jl:30
 [7] include(fname::String)
   @ Base.MainInclude ./client.jl:476
 [8] top-level scope
   @ none:6
in expression starting at /root/.julia/packages/ODBC/9VZTC/test/runtests.jl:30
ERROR: Package ODBC errored during testing

[quinnj]

ODBC doesn't use the system odbc setup by default, but manages it's own list of installed drivers; see the docs here for more information and the need to explicitly add drivers within the Julia session.

[kafisatz]

I am running into this too.¨ I have a python snippet which works perfectly fine using pyodbc.

but julia yields

Julia> ODBC.adddriver("adriver","/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so")
ERROR: Unable to find component name
Stacktrace:
 [1] error(s::String)
   @ Base ./error.jl:35
 [2] adddriver(name::String, path::String; kw::Base.Pairs{Symbol, Union{}, Tuple{}, NamedTuple{(), Tuple{}}})
   @ ODBC.API ~/.julia/packages/ODBC/9VZTC/src/API.jl:559
 [3] adddriver
   @ ~/.julia/packages/ODBC/9VZTC/src/API.jl:594 [inlined]
 [4] #adddriver#51
   @ ~/.julia/packages/ODBC/9VZTC/src/ODBC.jl:154 [inlined]
 [5] adddriver(name::String, libpath::String)
   @ ODBC ~/.julia/packages/ODBC/9VZTC/src/ODBC.jl:154
 [6] top-level scope
   @ REPL[120]:1

julia>  

[ahjulstad]

Works for me:

import Pkg
Pkg.activate(".")
# Pkg.add(["ODBC", "DBInterface", "DataFrames"])
using Revise
import ODBC
using DBInterface
using DataFrames

ODBC.adddriver("ODBC Driver 18 for SQL Server", "/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.2.1")

mssql = ODBC.Connection("Driver={ODBC Driver 18 for SQL Server};Server=127.0.0.1;Encrypt=no", "sa", "xxxxxx")

Tested using bullseye in devcontainer :

version: '3.8'
services:
  devcontainer:
    image: mcr.microsoft.com/devcontainers/base:bullseye
    # image: mcr.microsoft.com/devcontainers/base:ubuntu
    volumes:
      - ../..:/workspaces:cached
      - dotjulia:/home/vscode/.julia
    network_mode: service:db
    command: sleep infinity

  db:
    image: mcr.microsoft.com/mssql/server:2022-preview-ubuntu-22.04
    restart: unless-stopped
    volumes:
      - db-data:/var/lib/postgresql/data
    environment:
      ACCEPT_EULA: 'Y'
      MSSQL_SA_PASSWORD: xxx
      MSSQL_PID: Evaluation
    ports:
      - 127.0.0.1:1433:1433

volumes:
  db-data:

  dotjulia:
#    external: true 
#    name: dotjulia

Using the dev container feature to install the library

// For format details, see https://aka.ms/devcontainer.json. For config options, see the
// README at: https://github.com/devcontainers/templates/tree/main/src/debian
{
	"dockerComposeFile": "docker-compose.yml",
	"service": "devcontainer",
	"workspaceFolder": "/workspaces/${localWorkspaceFolderBasename}",
	"name": "Debian",
	// Or use a Dockerfile or Docker Compose file. More info: https://containers.dev/guide/dockerfile
	"features": {
		"ghcr.io/jlaundry/devcontainer-features/mssql-odbc-driver:1": {
			"version": "18"
		},
		"ghcr.io/julialang/devcontainer-features/julia:1": {
			"channel": "release"
		}
	},

	"customizations": {
		"vscode": {
			"extensions": [
				"ms-azuretools.vscode-docker", "julialang.language-julia"
			]
		}
	}


	// Features to add to the dev container. More info: https://containers.dev/features.
	// "features": {},

	// Use 'forwardPorts' to make a list of ports inside the container available locally.
	// "forwardPorts": [],

	// Configure tool-specific properties.
	// "customizations": {},

	// Uncomment to connect as root instead. More info: https://aka.ms/dev-containers-non-root.
	// "remoteUser": "root"
}