ODBC connector for Tarantool¶

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

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()

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.

PostgreSQL¶

$ sudo apt-get install odbc-postgresql

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

[<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=

$ brew install psqlodbc

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

[<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¶

[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

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

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

$ 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

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

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

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

MySQL¶

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

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

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

Sybase ASE¶

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

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

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

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

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