ODBC connector for Tarantool | Enterprise
Sign in Sign up
Download

Version:

latest
Modules reference odbc ODBC connector for Tarantool

ODBC connector for Tarantool

Based on unixODBC

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

local odbc = require 'odbc'
local yaml = require 'yaml'

local pool, err = odbc.create_pool({
    size = 5
})
local _, err = pool:connect()

local conn = pool:get()

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

pool:put(conn)

pool:close()

Use connection pool for ad-hoc requests

Pool implements :execute(), :drivers(), :datasources() and :tables() methods that acquire and release a connection object for you.

local odbc = require 'odbc'
local yaml = require 'yaml'

local pool, err = odbc.create_pool({
    size = 5
})
local _, err = pool:connect()

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

pool:close()

API Reference

Creates ODBC environment.

Options

date_as_table - configures behaviour of odbc package of how to deal with dates. If date_as_table is false then dates are represented in the default way for the driver (e.g. represents as strings for PostgreSQL). If date_as_table is true then dates are represented as tables compatible with os.date(‘*t’).

Creates a connection pool.

Options

  1. All options for odbc.create_env
  2. dsn - connection string
  3. size - number of connections in the pool

Environment methods

env:connect(dsn)

Parameters

  1. dsn - Connection string (documentation).

Connection methods

conn:execute(query, params)

Executes an arbitrary SQL query

Parameters

  1. query - SQL query
  2. param - table with parameters binding

Example

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

conn: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

  1. flag - true/false.

conn: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

  1. timeout - timeout of query execution in seconds.

conn:commit()

Commit a transaction

conn:rollback()

Rollback a transaction

conn:set_isolation(level)

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

Parameters

  1. level - 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

conn:is_connected()

Returns true if connection is active.

conn:state()

Returns an internal state of a connection.

conn:close()

Disconnect and close the connection.

conn:drivers()

Returns a list of drivers available in the system (contents of odbcinst.ini file).

Example

conn:datasources()

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

Example

conn:tables()

Returns a list of tables of a connected data source.

Example

conn:cursor(query, params)

Creates a cursor object for the specified query.

Parameters

  1. query - SQL query
  2. param - table with parameters binding

conn: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

  1. query - SQL query

Cursor methods

cursor:fetchrow()

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

Example

cursor:fetch(n)

Fetch multiple rows.

Parameters 1. n - number of rows to fetch

Example

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

prepare:execute()

Execute prepared SQL query

Parameters 1. param - table with parameters binding

Example

prepare:is_open()

Returns true if prepare is open.

prepare:close()

Close prepare discarding prepared query.

Pool methods

pool:connect()

Connect to all size connections.

pool:acquire()

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.

  1. query - SQL query
  2. param - 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. unixODBC driver
  2. Driver for the database of your choice. Currently this module is tested only with PostgreSQL, MySQL and MS SQL Server databases.
  3. Datasource for the database in odbc.ini file

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

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

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

References

  1. Tarantool - in-memory database and application server.
  2. PostgreSQL ODBC
  3. MS SQL Server ODBC
  4. MySQL ODBC
Found what you were looking for?
Feedback
odbc
oracle
Single-page version