ODBC plugin

# 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

# install unixODBC library
yum install unixODBC  unixODBC-devel

# MySQL ODBC Drivers
yum install mysql-connector

• 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/

• Configure an ODBC data source. For an example of configuring MySQL's ODBC data source, please refer to MySQL manual.

wget https://src.fedoraproject.org/repo/pkgs/unixODBC/unixODBC-2.3.11.tar.gz/sha512/dddc32f90a7962e6988e1130a8093c6fb8b9ff532cad270d572250324aecbc739f45f9d8021d217313910bab25b08e69009b4f87456575535e93be1f46f5f13d/unixODBC-2.3.11.tar.gz
tar -zxvf unixODBC-2.3.11.tar.gz
LDFLAGS="-lrt" CFLAGS="-fPIC"  ./configure --prefix=/hdd1/gitlab/DolphinDBPlugin/unixodbc2.3.11Lib --enable-static=yes --enable-shared=no --sysconfdir=/etc/ --with-included-ltdl=yes
make -j
make install

cd <plugin_odbc_dir>
mkdir build
cd build
cmake ..  -DUNIXODBCDIR=/hdd1/gitlab/DolphinDBPlugin/unixodbc2.3.11Lib
make -j

The plugin is compiled to a shared library named "libPluginODBC.so".

To connect to a SQL server database, you need to compile freetds odbc.

wget -c http://ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
tar -zxvf freetds-stable.tgz
cd freetds
./configure --prefix=/usr/local/freetds --with-tdsver=8.0 --enable-msdblib
make -j
make install

If the plugin is not running on the machine where it is compiled , copy the compiled freetds to the target machine:

• Copy "freetds.conf", "locales.conf", "pool.conf" under /usr/local/freetds/lib to the /usr/local/freetds/lib directory of the target machine;
• Copy /usr/local/freetds/lib/ibtdsodbc.so.0.0.0 to the /usr/local/freetds/lib directory of the target machine.

Syntax

• odbc::connect(connStr, [dataBaseType])

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 in the system registry, DSN-less connections specify the driver name and all driver-specific information in the connection string. For examples: SQL server's DSN-less connection string and MySQL's DSN-less connection string. Please note that the driver name could be different depending on the version of ODBC installed.
• dataBaseType: the type of the database, e.g., "MYSQL", "SQLServer", "PostgreSQL". It is recommended to specify this parameter to avoid errors when writing data.

Note:

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

Details

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

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;")  

Syntax

• odbc::close(conn)

Parameters

• conn: a connection handle created with odbc::connect.

Details

Close an ODBC connection.

Example

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

Syntax

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

Parameters

• connHandle|connStr: the connection handle or the connection string.
• querySql: a string indicating the query.
• t: a optional user-provided table. If specified, 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.
• batchSize: When the number of rows queried from ODBC reaches batchSize, the currently loaded data is appended to table t. The default value is 262,144.
• transform: a unary function and the input argument must be a table. If it is specified, a partitioned table must be created before loading the file. The method will first apply the specified function to the data, and then save the result to the partitioned table.

Details

odbc::query queries the database via connHandle or connStr and return a DolphinDB table.

Example

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

Syntax

• odbc::execute(connHandle|connStr, SQLstatements)

Parameters

• connHandle|connStr: the connection handle or the connection string.
• SQLstatements: the SQL statements.

Details

odbc::execute executes the SQL statements. It returns nothing.

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'")

Syntax

• odbc::append(connHandle, tableData, tablename, [createTableIfNotExist], [insertIgnore])

Parameters

• connHandle: connection handle.
• tableData: a table in DolphinDB.
• tablename: the name of the table in the connected database.
• createTableIfNotExist: a Boolean value. True means a new table is to be created. The default value is true.
• insertIgnore: a Boolean value. True means to use insert ignore. The default value is false.

Details

Append a DolphinDB table to the connected database.

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