Erlang ODBC

Connect to CrateDB from Erlang using ODBC.

About

Erlang includes an ODBC application out of the box that provides an interface to communicate with relational SQL-databases, see also Erlang ODBC examples.

Install

While Windows typically includes an ODBC driver manager, you can install the unixODBC driver manager on Linux and macOS systems. The PostgreSQL ODBC driver is called psqlODBC.

Please navigate to the psqlODBC download site to download and install the latest psqlODBC driver for Windows systems. Installing PostgreSQL ODBC drivers on Windows includes an illustrated walkthrough.

On Linux, install the unixODBC ODBC driver manager and the psqlODBC driver. Installing PostgreSQL ODBC drivers on Linux includes an illustrated walkthrough.

Arch Linux

pacman -Sy psqlodbc

Debian and derivatives

apt install --yes odbc-postgresql odbcinst unixodbc

Red Hat and derivatives

yum install -y postgresql-odbc

Verify installation.

odbcinst -q -d

[PostgreSQL ANSI]
[PostgreSQL Unicode]

On macOS, install the unixODBC ODBC driver manager and the psqlODBC driver, then register it.

# macOS
brew install psqlodbc unixodbc

odbcinst.ini

[PostgreSQL Unicode]
Description     = PostgreSQL ODBC driver (Unicode version)
Driver          = /usr/local/lib/psqlodbcw.so

odbcinst -i -d -f odbcinst.ini

Verify installation.

odbcinst -q -d

[PostgreSQL Unicode]

Synopsis

Before running the example, ensure the PostgreSQL ODBC driver is installed on your system.

odbc_example.erl

-module(odbc_example).

main(_) ->
    odbc:start(),
    {ok, Ref} = odbc:connect("Driver={PostgreSQL Unicode};Server=localhost;Port=5432;Uid=crate;Pwd=crate;Sslmode=disable;", []),
    io:fwrite("~p~n", [odbc:sql_query(Ref, "SELECT mountain, height FROM sys.summits ORDER BY height DESC LIMIT 3")]),
    init:stop().

SSL connection

Start the Erlang SSL application, add the Sslmode=require parameter, and replace Server, Uid, and Pwd with values matching your environment. Also use this variant to connect to CrateDB Cloud.

ssl:start(),
odbc:start(),
{ok, Ref} = odbc:connect("Driver={PostgreSQL Unicode};Server=testcluster.cratedb.net;Port=5432;Uid=admin;Pwd=password;Sslmode=require;", []),

Example

Create the file odbc_example.erl including the synopsis code shared above.

Start CrateDB using Docker or Podman, then invoke the example program.

docker run --rm --publish=5432:5432 docker.io/crate '-Cdiscovery.type=single-node'

escript odbc_example.erl