# \[ANN\] ODBC.jl Overhaul and 1.0 Release

**URL:** <https://discourse.julialang.org/t/ann-odbc-jl-overhaul-and-1-0-release/40493>\
**Category:** Data\
**Created:** [May 31, 2020, 12:13am UTC](https://discourse.julialang.org/t/ann-odbc-jl-overhaul-and-1-0-release/40493 "2020-05-31T00:13:32Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![quinnj](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/quinnj/32/11_2.png) [@quinnj](https://discourse.julialang.org/u/quinnj)\
**Post date:** [May 31, 2020, 12:13am UTC](https://discourse.julialang.org/t/ann-odbc-jl-overhaul-and-1-0-release/40493/1 "2020-05-31T00:13:32Z")

</div>

I’m happy to announce a package overhaul and [1.0 release](https://github.com/JuliaDatabases/ODBC.jl/releases/tag/v1.0.0) of the [ODBC.jl](http://juliadatabases.github.io/ODBC.jl/latest/) package. In addition to being one of the oldest Julia packages (over [7 years old](https://github.com/JuliaDatabases/ODBC.jl/commits/master?after=f76ec03d1253a7e0b9eb9160fbb12598b5292da8+454)!), 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`](http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/WelcomeVisitors) and [`unixODBC`](http://www.unixodbc.org/) 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:

```julia
# 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](http://juliadatabases.github.io/ODBC.jl/latest/), or just generally need some help figuring something out, feel free to [open an issue](https://github.com/JuliaDatabases/ODBC.jl/issues/new) and we can try to help out the best we can.

---

<div class="post-metadata">

**Author:** ![StefanKarpinski](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/stefankarpinski/32/24_2.png) [@StefanKarpinski](https://discourse.julialang.org/u/StefanKarpinski)\
**Post date:** [May 31, 2020, 1:15am UTC](https://discourse.julialang.org/t/ann-odbc-jl-overhaul-and-1-0-release/40493/2 "2020-05-31T01:15:09Z")

</div>

Bravo! This is huge.

---

<div class="post-metadata">

**Author:** ![tk3369](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tk3369/32/2824_2.png) [@tk3369](https://discourse.julialang.org/u/tk3369)\
**Post date:** [May 31, 2020, 5:01am UTC](https://discourse.julialang.org/t/ann-odbc-jl-overhaul-and-1-0-release/40493/3 "2020-05-31T05:01:38Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![abx](https://avatars.discourse-cdn.com/v4/letter/a/edb3f5/32.png) [@abx](https://discourse.julialang.org/u/abx)\
**Post date:** [June 4, 2020, 11:38pm UTC](https://discourse.julialang.org/t/ann-odbc-jl-overhaul-and-1-0-release/40493/4 "2020-06-04T23:38:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![akdor1154](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/akdor1154/32/15975_2.png) [@akdor1154](https://discourse.julialang.org/u/akdor1154)\
**Post date:** [June 5, 2020, 2:19am UTC](https://discourse.julialang.org/t/ann-odbc-jl-overhaul-and-1-0-release/40493/5 "2020-06-05T02:19:48Z")

</div>

Awesome 🙂 thanks for your work on this.

---

<div class="post-metadata">

**Author:** ![Palli](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/palli/32/3380_2.png) [@Palli](https://discourse.julialang.org/u/Palli)\
**Post date:** [June 8, 2020, 7:39pm UTC](https://discourse.julialang.org/t/ann-odbc-jl-overhaul-and-1-0-release/40493/6 "2020-06-08T19:39:19Z")

</div>

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

```julia
$ ~/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
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

```

---

<div class="post-metadata">

**Author:** ![julien\_goo](https://avatars.discourse-cdn.com/v4/letter/j/ce73a5/32.png) [@julien\_goo](https://discourse.julialang.org/u/julien_goo)\
**Post date:** [June 9, 2020, 9:45am UTC](https://discourse.julialang.org/t/ann-odbc-jl-overhaul-and-1-0-release/40493/7 "2020-06-09T09:45:04Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![djholiver](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/djholiver/32/50470_2.png) [@djholiver](https://discourse.julialang.org/u/djholiver)\
**Post date:** [July 14, 2021, 9:23pm UTC](https://discourse.julialang.org/t/ann-odbc-jl-overhaul-and-1-0-release/40493/8 "2021-07-14T21:23:20Z")

</div>

Hi,

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

> [@quinnj](#):
>
> `DBInterface.execute(conn, sql)`

I’ve tried the following:

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

```

but got the following error:

```julia
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,