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
- All options for odbc.create_env
- dsn - connection string
- size - number of connections in the pool
Environment methods¶
Connection methods¶
conn:execute(query, params)¶
Executes an arbitrary SQL query
Parameters
- query - SQL query
- 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
- 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
- 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
- level - isolation level. One of the values defined in the odbc.isolation table.
Isolation levels
- odbc.isolation.READ_UNCOMMITTED
- odbc.isolation.READ_COMMITTED
- odbc.isolation.REPEATABLE_READ
- 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:cursor(query, params)¶
Creates a cursor object for the specified query.
Parameters
- query - SQL query
- 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
- query - SQL query
Cursor methods¶
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.
- query - SQL query
- 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:
- unixODBC driver
- Driver for the database of your choice. Currently this module is tested only with PostgreSQL, MySQL and MS SQL Server databases.
- 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:
- http://www.iodbc.org/dataspace/doc/iodbc/wiki/iodbcWiki/Downloads
- 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¶
- Tarantool - in-memory database and application server.
- PostgreSQL ODBC
- MS SQL Server ODBC
- MySQL ODBC