Skip to content

Home

Jump to bottom
Liang.Lin edited this page May 7, 2018 · 1 revision

DolphinDB ODBC plugin

With this plugin, you can easily pull your data from existing databases that support ODBC interface.

Prerequisites

使用该插件需要在操作系统中安装unixODBC以及对应数据库的ODBC驱动, 下面以ubuntu系统为例

Ubuntu

# install unixODBC library
apt-get install unixodbc unixodbc-dev

# SQL Server ODBC Drivers
apt-get install tdsodbc

# PostgreSQL ODBC ODBC Drivers
apt-get install odbc-postgresql

# MySQL ODBC Drivers
apt-get install libmyodbc

# SQLite ODBC Drivers
apt-get install libsqliteodbc

Getting started

This short guide will walk you through loading odbc plugin and connecting to a mysql server.

Loading plugin into DolphinDB

You can use dolphindb's loadPlugin function to load the plugin. This function takes a plugin description file parameter. For example, the follwing dolphindb script loads the plugin described by odbc.cfg.

loadPlugin("\home\demo\plugins\odbc\odbc.cfg");

Using the plugin

You should use odbc before you call the functions of odbc plugin

use odbc;

The ODBC plugin functions

odbc::connect(connStr)

odbc::connect takes a odbc connection string as the paramter and opens up a connection via ODBC with this string and return the connection handle to user. For more information regarding the format of connection string, see https://www.connectionstrings.com/ ;

odbc::close(connHandle)

odbc::close closes an odbc connection associated with the handle.

odbc::query(connHandle or connStr, querySql [,t])

odbc::query queries the database via connHandle or connStr and return the results as a dolphindb table.
The first argument could be connection handle or a connection string.
The second argument is the query string.
The last argument is a optional user-provided table. If provided, query results will be appended to the table. Note that, the table schema must be compatible with the results returned from ODBC or an exception will be thrown.

You can ommit odbc:: prefix by introducing odbc module namespace.

example

这是一个连接mysql server,并且从employees.departments表中将数据取到DolphinDB的例子

loadPlugin("\home\demo\plugins\odbc\odbc.cfg");
use odbc;

connStr="Driver=MySQL;Data Source =
mysql-employees;server=127.0.0.1;uid=[username];pwd=[password]database=employees";

conn=odbc::connect(connStr)

mysqlTable=odbc::query(conn,"select * from departments") //transfer data from mysql to dolphindb
select * from mysqlTable
odbc::close(conn)

在运行例子之前要先确认已经正确配置了mysql odbc.

$ cat /etc/odbc.ini
[mysql-employees]
Description           = MySQL connection to  database
Driver                = MySQL
Database              = employees
Server                = localhost
User             = root
Password              = ******
Port                  = 3306
Socket                = /var/run/mysqld/mysqld.sock

Compilation

若需要修改和编译源码，请按如下方式编译和部署:

  • 下载源码
  • 在目录下找到makefile文件，在makefile的同级目录下运行make命令
  • 编译成功之后，将生成的libODBC.so文件覆盖部署目录下同名文件

FAQ

  1. loadPlugin时报错Couldn't load the dynamic library /[path]/libODBC.so

请使用命令 ldd libODBC.so 检查是否有依赖的.so文件不存在或路径错误，较大可能是 libDolphinDB.so路径错误，或者是libodbc.so不存在(unixodbc驱动未安装)

  1. Syntax Error: [line #2] Can't find module [odbc]

使用loadPlugin和use odbc时，需要分步执行，同时执行会报上述异常

loadPlugin("/home/llin/hzy-test/DolphinDBPlugin/odbc/DolphinDBODBC1.txt");

use odbc

License

DolphinDB ODBC Plugin is licensed under the [MIT License]. The terms of the license are as follows:

The MIT License (MIT)

Copyright (c) 2016 DolphinDB, DolphinDB Inc., and a number of other contributors. 

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
Clone this wiki locally