Top.Mail.Ru
ODBC connector for Tarantool | Enterprise
Tarantool
Узнайте содержание релиза 2.8
Справочник по модулям 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.

Параметры:decimal_as_luanumber (boolean) – configures behaviour of odbc package of how to deal with decimal
Результат:environment
odbc.create_pool(opts)

Creates a connection pool. All options for odbc.create_env

Параметры:
  • 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

Параметры:dsn (string) – connection string (documentation).
Результат:connection
Результат:nil, errs
environment.drivers()
Результат: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

Параметры:
  • query (string) – SQL query
  • params (table) – table with parameters binding
Результат:

resultset table with results

Результат:

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.

Параметры: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.

Параметры: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.

Параметры: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

Параметры:
  • query (string) – SQL query
  • params (table) – table with parameters binding
Результат:

cursor

Результат:

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

Параметры:query (string) – SQL query
Результат:prepare
Результат: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.

Параметры: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

Параметры: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.

Параметры:
  • 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