Top.Mail.Ru
ODBC connector for Tarantool | Enterprise
Modules reference / ODBC connector for Tarantool
Tarantool
Check out the new release 2.8
Modules reference / ODBC connector for Tarantool

ODBC connector for Tarantool

ODBC connector for Tarantool

Based on unixODBC

Breaking changes

Between 0.7.3 (and lower) and 1.0.0 (and higher)

odbc.create_env

  • opts.date_as_table deprecated
    • Use custom string date value to table marshaling
  • opts.decimal_as_luanumber introduced

Examples

Use a single connection

local odbc = require('odbc')
local yaml = require('yaml')

local env, err = odbc.create_env()
local conn, err = env:connect("DSN=odbc_test")

local result, err = conn:execute("SELECT 1 as a, 2 as b")
print(yaml.encode(result))

conn:close()

Use ODBC transactions

local odbc = require('odbc')
local yaml = require('yaml')

local env, err = odbc.create_env()
local conn, err = env:connect("DSN=odbc_test")
conn:execute("CREATE TABLE t(id INT, value TEXT)")

conn:set_autocommit(false)
conn:execute("INSERT INTO t VALUES (1, 'one')")
conn:execute("INSERT INTO t VALUES (2, 'two')")
local result, err = conn:execute("SELECT * FROM t")
print(yaml.encode(result))

conn:commit()
conn:close()

Use connection pool ad-hoc queries

Pool implements :execute(), :drivers(), :datasources() and :tables() methods.

local odbc = require('odbc')
local yaml = require('yaml')

local pool, err = odbc.create_pool({
    size = 5,
    dsn = os.getenv('DSN')
})
local _, err = pool:connect()

local res, err = pool:execute("SELECT 1 as a, 2 as b")
print(yaml.encode(res))

pool:close()

Rent pool connections

local odbc = require('odbc')
local yaml = require('yaml')

local pool, err = odbc.create_pool({
    size = 5,
    dsn = os.getenv('DSN')
})
local _, err = pool:connect()

local conn = pool:acquire()

local res, err = conn:execute("SELECT 1 as a, 2 as b")
print(yaml.encode(res))

pool:release(conn)

pool:close()

API Reference

ODBC

odbc.create_env(opts)

Creates ODBC environment.

Parameters:decimal_as_luanumber (boolean) – configures behaviour of odbc package of how to deal with decimal
Returns:environment
odbc.create_pool(opts)

Creates a connection pool. All options for odbc.create_env

Parameters:
  • dsn (string) – connection string
  • size (integer) – number of connections in the pool

Environment methods

class odbc.environment
environment.connect(dsn)

Connects to database using dsn

Parameters:dsn (string) – connection string (documentation).
Returns:connection
Returns:nil, errs
environment.drivers()
Returns:drivers Returns a list of drivers available in the system (contents of odbcinst.ini file).

Example

tarantool> env:drivers()
---
- - name: PostgreSQL ANSI
    attributes:
        Setup: libodbcpsqlS.so
        Driver: psqlodbca.so
        UsageCount: '1'
        Debug: '0'
        CommLog: '1'
        Description: PostgreSQL ODBC driver (ANSI version)
  - name: ODBC Driver 17 for SQL Server
    attributes:
        UsageCount: '1'
        Driver: /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
        Description: Microsoft ODBC Driver 17 for SQL Server
  - name: MySQL ODBC 8.0 Driver
    attributes:
        Setup: /opt/mysqlodbc/lib/libmyodbc8S.so
        Driver: /opt/mysqlodbc/lib/libmyodbc8a.so
        UsageCount: '1'
environment.datasources()

Returns a list of data sources available in the system (contents of odbc.ini file).

Example

tarantool> env:datasources()
---
- - name: tmddb
   driver: PostgreSQL ANSI
 - name: odbc_test
   driver: PostgreSQL ANSI
 - name: odbc_mssql
   driver: ODBC Driver 17 for SQL Server
 - name: odbc_mysql
   driver: MySQL ODBC 8.0 Unicode Driver
...

Connection methods

class odbc.connection
connection.execute(query, params)

Executes an arbitrary SQL query

Parameters:
  • query (string) – SQL query
  • params (table) – table with parameters binding
Returns:

resultset table with results

Returns:

nil, errors

Example

conn:execute("SELECT * FROM t WHERE id > ? and value = ?", {1, "two"})

Limitations

  • When no type marshaling case binding params have to be explicitly casted in sql expression, e.g.
conn:execute([[ insert into <table> values (cast(? as json) ) ]], { json:encode({a=1, b=2}) })
connection.set_autocommit(flag)

Sets autocommit of connection to a specified value. Used to achieve transaction behaviour. Set autocommit to false to execute multiple statements in one transactions.

Parameters:flag (boolean) – true/false
connection.set_timeout(timeout)

Sets timeout of query execution to a specified value. Timeout will be applied to each executed query, including queries with a cursor, until the connection is closed. Set timeout to 0 to disable timeout. By default, the timeout from the driver settings is used.

Parameters:timeout (integer) – timeout of query execution in seconds.
connection.commit()

Commit a transaction

connection.rollback()

Rollback a transaction

connection.set_isolation(level)

Sets isolation level of a transaction. Cannot be run in an active transaction.

Parameters:level (enum) – isolation level. One of the values defined in the odbc.isolation table.

Isolation levels

  1. odbc.isolation.READ_UNCOMMITTED
  2. odbc.isolation.READ_COMMITTED
  3. odbc.isolation.REPEATABLE_READ
  4. odbc.isolation.SERIALIZABLE
connection.is_connected()

Returns true if connection is active.

connection.state()

Returns an internal state of a connection.

connection.close()

Disconnect and close the connection.

connection.tables()

Returns a list of tables of a connected data source.

Example

tarantool> conn:tables()
---
- - table_type: TABLE
   catalog: odbc_test
   remarks:
   table_schema: public
   table_name: tt
 - table_type: TABLE
   catalog: odbc_test
   remarks:
   table_schema: public
   table_name: tt2
 - table_type: TABLE
   catalog: odbc_test
   remarks:
   table_schema: public
   table_name: tt3
...
connection.cursor(query, params)

Creates a cursor object for the specified query.

Parameters

Parameters:
  • query (string) – SQL query
  • params (table) – table with parameters binding
Returns:

cursor

Returns:

nil, err

connection.prepare(query)

Create object and prepare query. If you share a prepared query between fibers, the queries will be executed sequentially (synchronized between fibers).

Parameters:query (string) – SQL query
Returns:prepare
Returns:nil, err

Cursor methods

class odbc.cursor
cursor.fetchrow()

Fetch one row from the data frame and return as a single table value.

Example

tarantool> cusor = conn:cursor("select * from tt")
tarantool> cursor:fetchrow()
---
- id: 1
...

tarantool> cursor:fetchrow()
---
- id: 2
...
cursor.fetch(n)

Fetch multiple rows.

Parameters:n (integer) – number of rows to fetch

Example

tarantool> cursor:fetch(4)
---
- - id: 3
  - id: 4
  - id: 5
  - id: 6
...
cursor.fetchall()

Fetch all available rows in the data frame.

cursor.is_open()

Returns true if cursor is open.

cursor.close()

Close cursor discarding available data.

Prepare methods

class odbc.prepare
prepare.execute()

Execute prepared SQL query

Parameters:param (table) – table with parameters binding

Example

tarantool> p = conn:prepare('insert into tt values (?)')
---
...
tarantool> p:execute({1})
---
- 1
...
tarantool> p:execute({2})
---
- 1
...
prepare.is_open()

Returns true if prepare is open.

prepare:close()

Close prepare discarding prepared query.

Pool methods

class odbc.pool
pool.connect()

Connect to all size connections.

pool.acquire(timeout)

Acquire a connection. The connection must be either returned to pool with pool:release() method or closed.

pool.release(conn)

Release the connection.

pool.available()

Returns the number of available connections.

pool.close()

Close pool and all underlying connections.

pool.execute(query, params)

Acquires a connection, executes query on it and releases the connection.

Parameters:
  • query (string) – SQL query
  • params (table) – table with parameters binding
pool.tables()

Acquires a connection, executes tables() on it and releases.

pool.drivers()

Acquires a connection, executes drivers() on it and releases.

pool.datasources()

Acquires a connection, executes datasources() on it and releases.

Installation

Prerequisites:

  1. Driver for the database of your choice. E.g. Postgresql, MySQL, Sybase.
  2. Datasource for the database in odbc.ini file or DSN string, which describes connection.

PostgreSQL

Linux (Ubuntu)

$ sudo apt-get install odbc-postgresql

Add to file /etc/odbcinst.ini:

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

Add to file /etc/odbc.ini:

[<dsn_name>]
Description=PostgreSQL
Driver=PostgreSQL ANSI
Trace=No
TraceFile=/tmp/psqlodbc.log
Database=<Database>
Servername=localhost
username=<username>
password=<password>
port=
readonly=no
rowversioning=no
showsystemtables=no
showoidcolumn=no
fakeoidindex=no
connsettings=

MacOS

Use brew to install:

$ brew install psqlodbc

/usr/local/etc/odbcinst.ini contents:

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=/usr/local/lib/psqlodbca.so
Debug=0
CommLog=1
UsageCount=1

/usr/local/etc/odbc.ini contents:

[<dsn_nam>]
Description=PostgreSQL
Driver=PostgreSQL ANSI
Trace=No
TraceFile=/tmp/psqlodbc.log
Database=<database>
Servername=<host>
UserName=<username>
Password=<password>
ReadOnly=No
RowVersioning=No
ShowSystemTables=No
ShowOidColumn=No
FakeOidIndex=No

MSSQL

Linux

Please follow to the official installation guide.

/etc/odbcinst.ini contents:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
UsageCount=1

/etc/odbc.ini contents:

[<dsn_name>]
Driver=ODBC Driver 17 for SQL Server
Database=<Database>
Server=localhost

MacOS

For El Capitan, Sierra and High Sierra use brew to install:

$ brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
$ brew install --no-sandbox msodbcsql17 mssql-tools

For El Capitan and Sierra use brew to install:

$ brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
$ brew install --no-sandbox msodbcsql@13.1.9.2 mssql-tools@14.0.6.0

Examples below are fair to msodbcsql 13

/usr/local/etc/odbcinst.ini contents:

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.13.dylib
UsageCount=1

/usr/local/etc/odbc.ini contents:

[<dsn_name>]
Description=SQL Server
Driver=ODBC Driver 13 for SQL Server
Server=<host>,<port>

FYI:

Uid, Pwd etc are placed into connstring

Example

tarantool> conn, err = require('odbc').create_env():connect('DSN=odbc_mssql;Uid=SA;Pwd=<YourStrong!Passw0rd>')

MySQL

Linux

Please follow to the official installation guide.

MacOS

Download and install:

  1. http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads
  2. https://dev.mysql.com/downloads/connector/odbc/

Add to file /usr/local/etc/odbcinst.ini:

[MySQL ODBC 8.0 Driver]
Driver=/usr/local/mysql-connector-odbc-8.0.12-macos10.13-x86-64bit/lib/libmyodbc8a.so
UsageCount=1

Add to file /usr/local/etc/odbc.ini:

[<dsn>]
Driver = MySQL ODBC 8.0 Driver
Server = <host>
PORT = <port>

FYI:

USER, DATABASE etc are placed into connstring

Example

tarantool> conn, err = require('odbc').create_env():connect('DSN=odbc_mysql; USER=root; DATABASE=odbc_test')
---
...

Sybase ASE

MacOS

Run brew install freetds

Add to file /usr/local/etc/freetds.conf

[sybase]
host = localhost
port = 8000
tds version = auto

Add to file /usr/local/etc/odbcinst.ini:

[Sybase Driver]
Driver=/usr/local/lib/libtdsodbc.so
UsageCount=1

Add to file /usr/local/etc/odbc.ini:

[default]
Driver=/usr/local/lib/libtdsodbc.so
Port=8000

[sybase]
Driver=Sybase Driver
Description=Sybase ASE
DataSource=<datasource>
ServerName=sybase
Database=<database>

Example

tarantool> conn, err = require('odbc').create_env():connect('DSN=sybase;Uid=sa;Pwd=myPassword')
---
...

References

  1. Tarantool - in-memory database and application server.
  2. PostgreSQL ODBC
  3. MS SQL Server ODBC
  4. MySQL ODBC