OB-ODBC

This topic describes how to configure and use the OB-ODBC driver of OceanBase Database and the MySQL-ODBC driver of MySQL.

ODBC stands for Open Database Connectivity. It is a component for databases in the Windows Open Services Architecture (WOSA). ODBC aims to provide simple, standard, and transparent public programming interfaces for database connections. Vendors can use ODBC to develop underlying driver programs that are transparent to users. Vendors can use different technologies to implement and optimize the drivers for different database management systems (DBMS).

ODBC mainly consists of a driver and a driver manager. A driver is a module that supports ODBC function calls. Each driver is specific to a particular database type. When you migrate the data of an application from one DBMS to another, you need only to modify the name that ODBC sets for the DBMS in the application.

Both the OB-ODBC driver and the driver manager for connecting to OceanBase Database are developed by the OceanBase team. By using OB-ODBC, you can connect to both the MySQL mode and Oracle mode of OceanBase Database.

We recommend that you use the OB-ODBC driver to connect to OceanBase Database. You can also use the MySQL-ODBC driver to connect to a MySQL tenant of OceanBase Database.

The following figure shows the architecture in which OB-ODBC or MySQL-ODBC is used on Linux to connect to OceanBase Database.

Connection environment configuration

Before you connect to OceanBase Database, you must configure the connection environment by performing the following steps:

1. Modify the odbc.ini configuration file whose path is /etc/odbc.ini. You can also place the odbc.ini file in a directory other than /etc. In this case, you need to set an environment variable for the directory.

   [ODBC Data Sources]
   data_source_name = Name
   [Name]
   Driver=Oceanbase
   Description = MyODBC 5 Driver DSN
   SERVER   = [ODP IP address]
   PORT    = [ODP port number]
   USER    = [username@tenant_name#cluster_name]
   Password  = [user password]
   Database  = [database name]
   OPTION      = 3
   charset=UTF8
   

2. Modify the cat /etc/odbcinst.ini configuration file.

   [Oceanbase]
   Driver=/u01/mysql-odbc/lib/libmyodbc5a.so
   

3. Set environment variables.

   export ODBCSYSINI=/etc
   export ODBCINI=/etc/odbc.ini
   export LD_LIBRARY_PATH=/u01/mysql/lib:/usr/lib64:$LD_LIBRARY_PATH
   export PATH=$PATH:/u01/unix-odbc/bin
   

4. Run the odbcinst -j command to check whether the configuration is correct.

   Sample response:

   unixODBC 2.3.7
   DRIVERS............: /etc/odbcinst.ini
   SYSTEM DATA SOURCES: /etc/odbc.ini
   FILE DATA SOURCES..: /etc/ODBCDataSources
   USER DATA SOURCES..: /etc/odbc.ini
   SQLULEN Size.......: 8
   SQLLEN Size........: 8
   SQLSETPOSIROW Size.: 8
   

Scenarios and examples

1. Execute the following statement to create a table named TEST in an Oracle tenant of OceanBase Database.

   CREATE TABLE "TEST" (
     "ID" NUMBER(38) NOT NULL,
     "NAME" VARCHAR2(32),
     CONSTRAINT "TEST_PK" PRIMARY KEY ("ID")
   ) COMPRESS FOR ARCHIVE REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 10
   

2. Write code for the driver to establish a database connection.

   Sample code of the driver:

   #include <stdio.h>
   #include <stdlib.h>
   #include <string.h>
   #include "sql.h"
   #include "sqlext.h"
   
   typedef struct tagODBCHandler {
       SQLHENV henv;
       SQLHDBC hdbc;
       SQLHSTMT hstmt;
   }ODBCHandler;
   
   int IS_SUCC(SQLRETURN retcode) {
       if  (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) return 1;
       return 0;
   }
   void checkError(SQLRETURN retcode, const char* msg, ODBCHandler* handler) {
       SQLCHAR message[SQL_MAX_MESSAGE_LENGTH + 1];
       SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
       SQLINTEGER error;
       SQLSMALLINT len;
       SQLRETURN tmpcode;
   
       switch (retcode){
       case SQL_SUCCESS:
           printf("%s retcode is SQLRETURN\n", msg);
           break;
       case SQL_SUCCESS_WITH_INFO:
           printf("%s retcode is SQL_SUCCESS_WITH_INFO\n", msg);
           break;
       case SQL_ERROR:
           printf("%s retcode is SQL_ERROR\n", msg);
           tmpcode = SQLError(handler->henv, handler->hdbc, handler->hstmt, sqlstate, &error, message, sizeof(message), &len);
           if (tmpcode != SQL_SUCCESS && tmpcode != SQL_SUCCESS_WITH_INFO) {
               printf("get sqlerror failed %d", tmpcode);
           } else {
               printf("error is %d, meeesage is %s, sqlstate is %s, len is %d\n", error, message, sqlstate, len);
           }
           break;
       case SQL_INVALID_HANDLE:
           printf("%s retcode is SQL_INVALID_HANDLE\n", msg);
           break;
       case SQL_STILL_EXECUTING:
           printf("%s retcode is SQL_STILL_EXECUTING\n", msg);
           break;
       case SQL_NO_DATA:
           printf("%s retcode is SQL_NO_DATA\n", msg);
           break;
       default:
           printf("%s retcode is UNKNOWN retcode\n", msg);
           break;
       }
   }
   int main(int argc, char** argv) {
       ODBCHandler handler;
       SQLRETURN retcode;
   #define MAX_NAME_LEN 255
       SQLCHAR   connOut[MAX_NAME_LEN+1];
       SQLSMALLINT len;
       //Allocate environment handle
       retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &handler.henv);
       // Set the ODBC version environment attribute  
       if (!IS_SUCC(retcode)) {
           checkError(retcode, "SQLAllocHandle", &handler);
           return -1;
       }
       retcode = SQLSetEnvAttr(handler.henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3_80, 0);
       // Allocate connection handle  
       if (!IS_SUCC(retcode)) {  
           checkError(retcode, "SQLSetEnvAttr", &handler);
           return -1;
       }
       retcode = SQLAllocHandle(SQL_HANDLE_DBC, handler.henv, &handler.hdbc);  
       if (!IS_SUCC(retcode)) {  
           checkError(retcode, "SQLAllocHandle", &handler);
           return -1;
       }
     // Set login timeout to 5 seconds  
     SQLSetConnectAttr(handler.hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);  
     // Connect to data source  
     retcode = SQLDriverConnect(handler.hdbc, NULL, (SQLCHAR*)"DSN=odbctest", SQL_NTS, connOut, MAX_NAME_LEN, &len,SQL_DRIVER_NOPROMPT);
     if (!IS_SUCC(retcode)) {  
           checkError(retcode, "SQLDriverConnect", &handler);
           return -1;
       }
     retcode = SQLAllocHandle(SQL_HANDLE_STMT, handler.hdbc, &handler.hstmt);
     if (!IS_SUCC(retcode)) {  
           checkError(retcode, "SQLAllocHandle", &handler);
           return -1;
       }
       {
           //insert
           retcode = SQLPrepare(handler.hstmt, (SQLCHAR*)"INSERT INTO test VALUES(?,'robin')", SQL_NTS);
           if (!IS_SUCC(retcode)) {  
               checkError(retcode, "SQLPrepare", &handler);
               return -1;
           }
           SQLINTEGER id = 2;
           retcode = SQLBindParameter(handler.hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &id, 0, NULL);
           if (!IS_SUCC(retcode)) {  
               checkError(retcode, "SQLBindParameter", &handler);
               return -1;
           }
           retcode = SQLExecute(handler.hstmt);
           if (!IS_SUCC(retcode)) {  
               checkError(retcode, "SQLExecute", &handler);
               return -1;
           }
           // ORACLE mode will need this section
           retcode = SQLEndTran(SQL_HANDLE_DBC, handler.hdbc, SQL_COMMIT);
           if (!IS_SUCC(retcode)) {
               checkError(retcode, "SQLCommit", &handler);
               return -1;
           }
       }
       // clean handle
       SQLFreeHandle(SQL_HANDLE_STMT, handler.hstmt);
       SQLDisconnect(handler.hdbc);
       SQLFreeHandle(SQL_HANDLE_DBC, handler.hdbc);
       SQLFreeHandle(SQL_HANDLE_ENV, handler.henv);
       return 0;
   }
   

   Notice

   Unlike MySQL tenants, automatic commit is disabled by default for Oracle tenants. Therefore, the sample code contains a section for Oracle, which is placed after the ORACLE mode will need this section comment. This section is unnecessary if you want to connect to MySQL tenants.

3. Compile and execute the driver.

   If you use GNU Compiler Collection (GCC) to write the code, specify header files in the -Ioption, library file directories in the -L option, and library names in the -l option.

   Sample statement:

   gcc test.c -L/u01/unix-odbc/lib -lodbc -I/u01/unix-odbc/include -otest
   ./test
   

   The following sample code shows how to verify the result:

   obclient -hobproxy.oceanbase.abc.com -uhr@test0_5#obtest -p'hr' -P2883
   obclient> select * from test;
   +----+-------+
   | ID | NAME  |
   +----+-------+
   |  1 | jason |
   |  2 | robin |
   +----+-------+