ODBC

ODBC provides a unified interface that enables applications to access heterogeneous databases using SQL standards. With DolphinDB odbc plugin, you can import data from databases that support ODBC interface or export DolphinDB in-memory tables to other databases. This plugin is developed based on the nanodbc library.

Prerequisites

The odbc plugin supports the following databases stably in CentOS 7: MySQL, PostgreSQL, SQLServer, Clickhouse, SQLite, and Oracle. When connecting, you need to specify the database name, such as "MySQL".

Install the ODBC driver that corresponds to your operating system and database.

Ubuntu

  • Install unixODBC library
apt-get install unixodbc unixodbc-dev
  • Install SQL Server ODBC Drivers
apt-get install tdsodbc
  • Install PostgreSQL ODBC Drivers
apt-get install odbc-postgresql
  • Install MySQL ODBC Drivers
apt-get install libmyodbc
  • Install SQLite ODBC Drivers
apt-get install libsqliteodbc

CentOS

  • Update the MySQL version
$> su root
$> yum update mysql-community-release
  • Install unixODBC library
yum install unixODBC  unixODBC-devel
  • Install MySQL ODBC Drivers
yum install mysql-connector

Windows

  1. Download and install odbc driver for mysql or other databases from their websites. For examples:
    • MySQL: https://dev.mysql.com/downloads/connector/odbc/
    • MS SQL Server: https://www.microsoft.com/en-us/download/details.aspx?id=53339
    • PostgreSQL: https://www.postgresql.org/ftp/odbc/versions/msi/
  2. Configure an ODBC data source. For an example of configuring MySQL's ODBC data source, please refer to MySQL manual.

Docker Environment (Alpine Linux)

Install the unixODBC library:

apk add unixodbc
apk add unixodbc-dev

Installation (with installPlugin)

Required server version: DolphinDB 2.00.10 or higher

OS: Windows, Windows JIT, Linux x86-64, Linux ABI, and Linux JIT.

Installation Steps:

(1) Use listRemotePlugins to check plugin information in the plugin repository.

Note: For plugins not included in the provided list, you can install through precompiled binaries or compile from source. These files can be accessed from our GitHub repository by switching to the appropriate version branch.

login("admin", "123456")
listRemotePlugins()

(2) Invoke installPlugin for plugin installation

installPlugin("odbc")

(3) Use loadPlugin to load the plugin before using the plugin methods.

loadPlugin("odbc")

Method References

connect

Syntax

connect(connStr, [dataBaseType])

Details

Create a connection to the database server. Return a database connection handle, which will be used to access the database server later.

Parameters

  • connStr: An ODBC connection string. For more information regarding the format of the connection string, refer to The Connection Strings Reference. ODBC DSN must be created by the system administrator. Its connection strings can be referenced DSN connection strings. We can also create DSN-Less connections to the database. Rather than relying on information stored in a file or the system registry, DSN-less connections specify the driver name and all driver-specific information in the connection string, such as SQL server's DSN-less connection string and MySQL's DSN-less connection string.
  • dataBaseType (optional): The type of the database (case-insensitive), e.g., "MYSQL", "SQLServer", "PostgreSQL", "ClickHouse", "SQLite", and "Oracle". It is recommended to specify this parameter to avoid errors when writing data.
Note:
  • Starting from 3.00.2.1, a lock is only added when databaseType is unspecified or specified as "ClickHouse". Previously, the plugin always added a lock to the connection.
  • The driver name could be different depending on the installed ODBC version.
  • If the database server port is not specified correctly, a server crash will occur.
  • You must connect to the Oracle database using Data Source Name (DSN), otherwise, the user name and password validation may fail. If you change the database and password configured by DSN in /etc/odbc.ini, you need to commit the new configuration at the Oracle command prompt before you can connect via the new configuration (command line tool isql can also be used to verify whether the new configuration takes effect).
  • When accessing the database via freeTDS, ensure that the DSN configuration in freetds.conf is correct, otherwise a freeTDS crash may occur.

Example

conn1 = odbc::connect("Dsn=mysqlOdbcDsn")  //mysqlOdbcDsn is the name of data source name
conn2 = odbc::connect("Driver={MySQL ODBC 8.0 UNICODE Driver};Server=127.0.0.1;Database=ecimp_ver3;User=newuser;Password=dolphindb123;Option=3;") 
conn3 = odbc::connect("Driver=SQL Server;Server=localhost;Database=zyb_test;User =sa;Password=DolphinDB123;")

close

Syntax

close(conn)

Details

Close an ODBC connection.

Parameters

  • conn: A connection handle created with method connect.

Example

conn1 = odbc::connect("Dsn=mysqlOdbcDsn") 
odbc::close(conn1)

query

Syntax

query(connHandle|connStr, querySql, [t], [batchSize], [transform])

Details

Query the database via connHandle or connStr and return a DolphinDB table.

Parameters

  • connHandle: The connection handle.
  • connStr: The connection string.
  • querySql: An SQL query.
  • t (optional): A table object. If specified, query results will be appended to the table. Note that the table schema must be compatible with the results returned from ODBC (refer to the "Data Type Mappings" section) or an exception will be thrown.
  • batchSize (optional): When the number of rows queried from ODBC reaches batchSize, the currently loaded data will be appended to table t. The default value is 262,144.
  • transform (optional): A unary function whose input parameter must be a table. If it is specified, a partitioned table must be created before loading the file. The method will first apply the transform function to the data, and then save the result to the partitioned table.

Example

t = odbc::query(conn1,"SELECT max(time),min(time) FROM ecimp_ver3.tbl_monitor;")

execute

Syntax

execute(connHandle or connStr, SQLstatements)

Details

Execute the SQL statements.

Parameters

  • connHandle: The connection handle.
  • connStr: The connection string.
  • SQLstatements: The SQL statements.

Example

odbc::execute(conn1,"delete from ecimp_ver3.tbl_monitor where `timestamp` BETWEEN '2013-03-26 00:00:01' AND '2013-03-26 23:59:59'")

append

Syntax

append(connHandle, tableData, tablename, [createTableIfNotExist], [insertIgnore])

Details

Append a DolphinDB table to the connected database.

Parameters

  • connHandle: The connection handle.
  • tableData: A table in DolphinDB.
  • tablename: The name of the table in the connected database.
  • createTableIfNotExist (optional): A BOOLEAN value indicating whether to create a new table. The default value is true.
  • insertIgnore (optional): A BOOLEAN value indicating whether to ignore duplicate data when inserting. The default value is false.

Example

t = table(1..10 as id,take(now(),10) as time,rand(1..100,10) as value)
odbc::append(conn1, t,"ddbtale" ,true)
odbc::query(conn1,"SELECT * FROM ecimp_ver3.ddbtale")

setLogLevel

Syntax

setLogLevel(logLevel)

Details

Set the odbc plugin log level. After setting the log level, only logs at and above the specified logLevel are printed. Note that this method does not affect the DolphinDB server log level.

Parameters

  • logLevel: The odbc plugin log level. The optional values from low to high are: DEBUG, INFO, WARNING, and ERROR.

Example

With the odbc log level set to WARNING, only WARNING and ERROR level logs are printed:

odbc::setLogLevel(WARNING)

getLogLevel

Syntax

getLogLevel()

Details

Return a string indicating the log level for the odbc plugin.

Data Type Mappings

For Queries

Type in ODBCType in DolphinDB
SQL_BITBOOL
SQL_TINYINT / SQL_SMALLINTSHORT
SQL_INTEGERINT
SQL_BIGINTLONG
SQL_REALFLOAT
SQL_FLOAT/SQL_DOUBLE/SQL_DECIMAL/SQL_NUMERICDOUBLE
SQL_DATE/SQL_TYPE_DATEDATE
SQL_TIME/SQL_TYPE_TIMESECOND
SQL_TIMESTAMP/SQL_TYPE_TIMESTAMPNANOTIMESTAMP
SQL_CHAR(len == 1)CHAR
other typesSTRING

For Data Writing

DolphinDBPostgreSQLClickHouseOracleSQL ServerSQLiteMySQL
BOOLbooleanBoolchar(1)bitbitbit
CHARchar(1)char(1)char(1)char(1)char(1)char(1)
SHORTsmallintsmallintsmallintsmallintsmallintsmallint
INTintintintintintint
LONGbigintbigintnumberbigintbigintbigint
DATEdatedatedatedatedatedate
MONTHdatedatedatedatedatedate
TIMEtimetimetimetimetimetime
MINUTEtimetimetimetimetimetime
SECONDtimetimetimetimetimetime
DATETIMEtimestampdatetime64datedatetimedatetimedatetime
TIMESTAMPtimestampdatetime64timestampdatetimedatetimedatetime
NANOTIMEtimetimetimetimetimetime
NANOTIMESTAMPtimestampdatetime64timestampdatetimedatetimedatetime
FLOATfloatfloatfloatfloat(24)floatfloat
DOUBLEdouble precisiondoublebinary_doublefloat(53)doubledouble
SYMBOLvarchar(255)varchar(255)varchar(255)varchar(255)varchar(255)varchar(255)
STRINGvarchar(255)varchar(255)varchar(255)varchar(255)varchar(255)varchar(255)

FAQ

  1. When connecting to ClickHouse on Windows, the query results contain Chinese garbled code.

    • Solution: Choose the ClickHouse ODBC Driver (ANSI).

  2. When reading data from ClickHouse, data of DATETIME type returns null or wrong values.

    • Cause: The ClickHouse ODBC driver prior to version 1.1.10 returns DATETIME data as a STRING type with shorter length.
    • Solution: Update the driver to version 1.1.10 or higher.

  3. When connecting to the MySQL data source after installing the MySQL ODBC Driver with yum install mysql-connector-odbc, an error is reported due to the inconsistency between the driver and the MySQL version.

    • Cause: The Yum Repository is not updated in time. yum install mysql-connector-odbc will download the corresponding version of MySQL ODBC Driver according to the configuration in the Yum Repository. When the version of MySQL data source is higher, e.g., version 8.0, make sure your local Yum Repository configuration is up-to-date. To avoid errors such as “connection timeout” or “can’t find file libodbc.so.1”, the latest version of MySQL ODBC Driver is required. You can use the following solutions:

    • Solution 1: Run the following commands before you run yum install mysql-connector-odbc.

      $> su root
$> yum update mysql-community-release

      For more instructions of MySQL Yum Repository, refer to Installing Additional MySQL Products and Components with Yum.

    • Solution 2: Download the specified version of MySQL ODBC Driver, modify the file /etc/odbc.ini, and the conn statement. For example, MySQL version 8.0 is required.

      • Download the MySQL version 8.0 with the following commands.

        wget https://dev.mysql.com/get/Downloads/Connector-ODBC/8.0/mysql-connector-odbc-8.0.32-1.el7.x86_64.rpm
rpm -ivh mysql-connector-odbc-8.0.32-1.el7.x86_64.rpm
rpm -ql mysql-connector-odbc-8.0.32-1.el7.x86_64

      • If the error libodbc.so.1: cannot open shared object file: No such file or directory is reported, you need to create a soft link between libodbc.so.2 and libodbc.so.1, and reload the plugin.

        cd /usr/lib64
ln -s libodbc.so.2 libodbc.so.1

      • Copy the specified path of Driver under [MySQL ODBC 8.0 Unicode Driver] in /etc/odbcinst.ini.

        [MySQL ODBC 8.0 Unicode Driver]
Driver = /usr/lib64/libmyodbc8w.so
UsageCount = 1

      • Modify /etc/odbc.ini with the login information and information copied in the previous step.

        [root@username/]# cat /etc/odbc.ini
[mysql8]
Description = ODBC for MySQL
Driver = /usr/lib64/libmyodbc8w.so
Server = 172.17.0.10
Port = 3306
Database = test1db
User = root
Password = 123456

      • Modify the conn statement.

        conn = odbc::connect("Driver=MySQL ODBC 8.0 Unicode Driver;Server=172.17.0.10;Port=3306;Database=testdb;User=root;Password=123456;", "MySQL");

  4. When connecting to SQLServer data sources using the FreeTDS driver, the query results contain garbled code.

    • Cause: This issue occurs with lower versions of the FreeTDS driver when connecting to databases with non-UTF-8 character set.

    • Solution: Upgrade to FreeTDS with version 1.4.22 or higher. The compilation script is as follows:

    wget -c https://www.freetds.org/files/stable/freetds-1.4.23.tar.gz
tar -zxvf freetds-1.4.23.tar.gz
cd freetds-1.4.23
./configure --prefix=/usr/local/freetds --enable-msdblib
make -j
make install