[ANN] ODBC.jl Overhaul and 1.0 Release

Specific Domains Data
1

I’m happy to announce a package overhaul and 1.0 release of the ODBC.jl package. In addition to being one of the oldest Julia packages (over 7 years old!), it was also my first Julia package that lured me into Julia development, so it’s been a bit nostalgic to revisit the depths of the codebase.

Over the last few years, it has accumulated quite a bit of technical debt and open issues, so it was due for an overhaul, modernization, and general cleaning up.

One of the biggest aims for the 1.0 release was an aim to make the notoriously complex process of ODBC setup as simple as possible; as such, ODBC.jl now ships with both the iODBC and unixODBC ODBC drivers as BinaryBuilder artifacts, and can be switched and used at run-time as may be desired/needed when working with an individual database driver. The standard Windows system-provided ODBC driver manager is still used on that platform.

ODBC.jl now also provides ODBC administrative functionality via ODBC.adddriver/ODBC.removedriver, ODBC.adddsn/ODBC.removedsn, and ODBC.setdebug, which hopefully help avoid the need to edit configuration files manually (note that on Windows, you need to run the Julia or terminal with administrative privileges to make these work). ODBC.jl writes, manages, and utilizes its own set of configuration files to further simplify and streamline the process for setup. The typical setup, workflow can now be as simple as:

# install the ODBC.jl package, along with system odbc driver managers for non-windows systems
Pkg.add("ODBC")
using ODBC

# locate existing ODBC driver shared libraries or download new, then configure
ODBC.adddriver("MariaDB", "full/path/to/mariadb/odbc/shared/library.so")

conn = ODBC.Connection("Driver={MariaDB};SERVER=127.0.0.1;USER=root;PWD=")
# ODBC.jl now also supports the DBInterface
conn = DBInterface.connect(ODBC.Connection, "Driver={MariaDB};SERVER=127.0.0.1;USER=root;PWD=")

# prepare statements, then execute with parameters
stmt = DBInterface.prepare(conn, sql)
DBInterface.execute(stmt, params)

# execute sql statement directly, then materialize results in a DataFrame
using DataFrames
df = DBInterface.execute(conn, sql) |> DataFrame

# load data into database table
ODBC.load(df, conn, "odbc_table_from_dataframe")

For the 1.0 release, the package has been rewritten from the ground up to help cleanup functionality, fix a lot of open, existing bugs, and generally try to modernize the codebase with now-standard ecosystem interfaces (Tables.jl, DBInterface.jl). The package surface area is complex due to the existence of 3 possible ODBC driver managers, 3 officially supported OS platforms, and an unknown number of potential database ODBC drivers to interact with, so please be patient if there are hiccups in the release and as you update codebases. If you run into bugs, need clarification in the new docs, or just generally need some help figuring something out, feel free to open an issue and we can try to help out the best we can.

71 Likes
2

Bravo! This is huge.

6 Likes
3

ODBC installation and configuration in Unix is something that I struggled in the past. What you have done in this package seems to be a great step forward in making it easy to use. Congratulations about the 1.0 release!

6 Likes
4

Some of the bugs you fixed were on the top of my wish list… Amazing!

5

Awesome :slight_smile: thanks for your work on this.

6

Works, and the startup not slow (tiny bit faster startup with this):

$ ~/julia-1.4.0/bin/julia --startup-file=no -O0 -q

julia> @time using ODBC
  1.097914 seconds (1.09 M allocations: 59.530 MiB, 0.60% gc time)

Should this now be your go to package? E.g. not JDBC.jl or native Julia driver, e.g. for PostgreSQL? Any idea what would be fastest, or how much overhead (e.g. for PostgreSQL)?

vs. for PostgreSQL native startup:

julia> @time using LibPQ   #uses Decimals.jl, not DecFP.jl, so likely slower:
  3.863968 seconds (9.32 M allocations: 477.957 MiB, 6.75% gc time)

on default opt: 4.825697 seconds
7

I confirm ODBC.jl works well. Great job done. The installation of the DB specific driver is easy (postgres in my case), but could be further facilitated by some explicit install methods in the docs for the most common DB (Maria, Postgres, …).

Congrats to the package author!

1 Like
8

Hi,

Is it possible to iterate over the cursor returned from execute?

I’ve tried the following:

cur =  DBInterface.execute(conn, "query",iterate_rows=true)  
 
for c in cur
     println(c)
end

but got the following error:

LoadError: ReadOnlyMemoryError()
Stacktrace:
 [1] SQLFetchScroll(stmt::Ptr{Nothing}, fetch_orientation::Int16, fetch_offset::Int64)
   @ ODBC.API ~\.julia\packages\ODBC\qhwMX\src\API.jl:87
 [2] iterate(x::ODBC.Cursor{false, false}, st::Int64)
   @ ODBC ~\.julia\packages\ODBC\qhwMX\src\dbinterface.jl:370

am I doing something wrong? I am hoping to push the results into a Channel eventually (direct piping also failed). The result set of my dummy query is 100 rows and I’m unclear on how to proceed from the docs.

Regards,