ODBC

• 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

• 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

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.

Install the unixODBC library:

apk add unixodbc
apk add unixodbc-dev

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:

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

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)

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

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

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