Install and configure the ODBC driver for Linux|V2.0.9|OceanBase Connector/ODBC

After you obtain the OceanBase Connector/ODBC installation package for Linux, perform the following steps to install and configure it.

Version requirements

libobclient: V2.1.2

Step 1: Check whether the driver is installed and the driver version

Check whether OceanBase Connector/ODBC is installed and view the corresponding version information.

rpm -qa | grep odbc
rpm -qa | grep obclient

If it is installed, the output is as follows:

# rpm -qa | grep odbc
ob-connector-odbc-2.0.9.2-20230719101407.el7.alios7.x86_64
ob-unixodbc-2.0.8.3-20231114143843.el7.alios7.x86_64

# rpm -qa | grep obclient
libobclient-2.1.2-20211201104607.el7.alios7.x86_64

If you want to replace it with another version, run the following commands to uninstall the software package.

# rpm -ev ob-unixodbc
# rpm -ev ob-connector-odbc
# rpm -ev libobclient

If it is not installed, install the software dependency package first, and then install the ODBC driver.

Step 2: Install the software dependency packages

You need to install two tools of the Libtool software dependency package: libtool-ltdl and libreadline.

Install libtool-ltdl

Run the following command to check whether libtool-ltdl is installed.
```
rpm -qa | grep libtool-ltdl
```
If libtool-ltdl is installed, the command returns a result similar to the following:
```
libtool-ltdl-2.4.2-22.el7_3.x86_64
```
If libtool-ltdl is not installed, run the following commands to install it:
```
sudo yum search libtool-ltdl
sudo yum install libtool-ltdl
```

Install libreadline

Run the following command to check whether libreadline is installed.
```
rpm -qa|grep readline
```
If libreadline is installed, the command returns a result similar to the following:
```
readline-6.2-9.1.alios7.x86_64
```
If libreadline is not installed, run the following commands to install it:
```
sudo yum search readline
sudo yum install readline
```
Check whether the file exists after the installation.
```
ls -lth /usr/lib64/libreadline.so.6
```

If the /usr/lib64/libreadline.so.6 file does not exist, run the following command to create a symbolic link.

# rpm -qa|grep readline
readline-8.0-3.ky10.x86_64

# rpm -ql readline-8.0-3.ky10.x86_64
/usr/lib64/libhistory.so.7
/usr/lib64/libhistory.so.7.0
/usr/lib64/libhistory.so.8
/usr/lib64/libhistory.so.8.0
/usr/lib64/libreadline.so.7
/usr/lib64/libreadline.so.7.0
/usr/lib64/libreadline.so.8
/usr/lib64/libreadline.so.8.0
/usr/share/licenses/readline
/usr/share/licenses/readline/COPYING
/usr/share/licenses/readline/USAGE

# ln -s /usr/lib64/libreadline.so.8 /usr/lib64/libreadline.so.6
# ls -lth /usr/lib64/libreadline.so.6
lrwxrwxrwx 1 root root 27 Jan  3 14:56 /usr/lib64/libreadline.so.6 -> /usr/lib64/libreadline.so.8

Step 3: Install the OceanBase ODBC driver

After you install the Libtool software dependency package, run the following commands to install the ODBC driver:

sudo rpm -ivh libobclient-<version_no>.rpm
sudo rpm -ivh ob-connector-odbc-<version_no>.rpm
sudo rpm -ivh ob-unixodbc-<version_no>.rpm

After the installation is complete, run the following commands to check the installation status and version:

rpm -qa | grep odbc
rpm -qa | grep obclient

If the installation is successful, the output is similar to the following:

# rpm -qa | grep odbc
ob-connector-odbc-2.0.9.2-20230719101407.el7.alios7.x86_64
ob-unixodbc-2.0.9.2-20230719100704.el7.alios7.x86_64

# rpm -qa | grep obclient
libobclient-2.2.0-20221017150844.el7.alios7.x86_64

Step 4: Configure the configuration files

Configure odbc.ini

Configure odbc.ini to /etc/odbc.ini (you can also set the environment variable to a custom directory). Execute the following command to edit the odbc.ini configuration file.

sudo vim /etc/odbc.ini

After executing the above command, you will see the configuration file. The content and explanation of the configuration file are as follows:

[ODBC Data Sources]
data_source_name = OBDSN

[OBDSN]
Driver=Oceanbase
Description = OceanBase Driver DSN
SERVER = 10.10.20.20
PORT = 2883
USER = ALVIN@oboracle#obcluster
Password = xxx
Database = ALVIN
OPTION = 4
charset=UTF8

[OBDSNMYSQL]
Driver=Oceanbase
Description = OceanBase Driver DSN
SERVER = xxx.xxx.xxx.xxx
PORT = 10003
USER = tester@sys
Password = 1******
Database = unittests
OPTION = 4
charset=UTF8

[OBDSNMULTIHOST]
Driver=Oceanbase
Description = OceanBase Driver DSN with Multi-IP Support
MULTIHOST = 1
SERVER = xxx.xxx.xxx.xxx:38882,xxx.xxx.xxx.xxx:38884
USER = tester@sys

The parameters and their constraints in the configuration file are described in the following table.

Parameter	Description
Driver	The driver specified in odbcinst.ini, which is user-defined.
Description	The description of the driver.
SERVER	The IP address of the database. You can specify multiple IP addresses, which are separated by commas. For example: `xxx.xxx.xxx.xxx:38882,xxx.xxx.xxx.xxx:38884`.
PORT	The actual port of the database.
MULTIHOST	The switch for multi-IP configuration. Set it to 1 to enable the multi-IP feature (default value is 0).
USER	The username of the database.
Password	The password of the database.
Database	The name of the database.
OPTION	The configuration option (enable logging, OPTION=4).
charset	The character set of the database, which defaults to UTF8.

Press i on the keyboard to enter the editing mode and add the following content to the file. Note that there must be no space before [Oceanbase].

[Oceanbase]
 Description = ODBC for Oceanbase
 Driver = /u01/ob-connector-odbc/lib64/mariadb/libobodbc.so

Finally, press esc on the keyboard and enter :wq to save and exit the file.

Configure odbcinst.ini

Configure odbcinst.ini to /etc/odbcinst.ini (you can also set the environment variable to a custom directory). Execute the following command to edit the odbcinst.ini configuration file.

sudo vim /etc/odbcinst.ini

Press i on the keyboard to enter the editing mode and add the following content to the file. Note that there must be no space before [Oceanbase].

[Oceanbase]
 Description = ODBC for Oceanbase
 Driver = /u01/ob-connector-odbc/lib64/mariadb/libobodbc.so

Finally, press esc on the keyboard and enter :wq to save and exit the file.

Step 5: Set environment variables.

Temporarily add environment variables

The environment variables added in this way are only valid for the current terminal session and will be invalid after the terminal session is exited.

$export ODBCSYSINI=/etc
$export ODBCINI=/etc/odbc.ini
$export LD_LIBRARY_PATH=/u01/unix-odbc/lib:/u01/obclient/lib:/u01/ob-connector-odbc/lib64/mariadb:$LD_LIBRARY_PATH

Permanently add environment variables for the current user

The environment variables added in this way will still be valid for the current user after the terminal session is exited.

vim ~/.bashrc

Edit the .bashrc file and add the following information at the end of the file:

export ODBCSYSINI=/etc
export ODBCINI=/etc/odbc.ini
export LD_LIBRARY_PATH=/u01/unix-odbc/lib:/u01/obclient/lib:/u01/ob-connector-odbc/lib64/mariadb:$LD_LIBRARY_PATH

Save the changes and exit the file, then execute the following command to make the configuration take effect.

source ~/.bashrc

Permanently add environment variables for all users

The environment variables added in this way will still be valid for all users after the terminal session is exited.

vim /etc/profile

Edit the /etc/profile file and add the following information at the end of the file:

export ODBCSYSINI=/etc
export ODBCINI=/etc/odbc.ini
export LD_LIBRARY_PATH=/u01/unix-odbc/lib:/u01/obclient/lib:/u01/ob-connector-odbc/lib64/mariadb:$LD_LIBRARY_PATH

Save the changes and exit the file, then execute the following command to make the configuration take effect.

source /etc/profile

Step 6: Check the environment variable configuration

After the environment variables are configured, use the odbcinst -j command to check whether the configuration is correct:

/u01/unix-odbc/bin/odbcinst -j

The returned result is as follows:

unixODBC 2.3.0
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

Step 7: Check whether the environment dependencies are correct

Run the following command to check whether the environment dependencies are correct. Make sure that the dependencies of libobclnt.so.20 correspond to the installed libobclient-xxx.rpm package.

Run the following command to switch to the directory where the ODBC connector library files are stored:

cd /u01/ob-connector-odbc/lib64/mariadb

Run the following command to list the dependencies of libobodbc.so:

ldd /u01/ob-connector-odbc/lib64/mariadb/libobodbc.so

The output is as follows:

linux-vdso.so.1 =>  (0x00007ffe4d3fc000)
libobclnt.so.20 => /u01/obclient/lib/libobclnt.so.20 (0x00007f063217c000)
libodbcinst.so.2 => /u01/unix-odbc/lib/libodbcinst.so.2 (0x00007f0631f64000)
libm.so.6 => /lib64/libm.so.6 (0x00007f0631c62000)
libc.so.6 => /lib64/libc.so.6 (0x00007f0631894000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f0631690000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f0631474000)
libltdl.so.7 => /lib64/libltdl.so.7 (0x00007f063126a000)
/lib64/ld-linux-x86-64.so.2 (0x00007f0632675000)

Step 8: Test the connection

Test the ISQL connection

The DriverManager-related tools are located in the /u01/unix-odbc directory. Run the connection command and ensure it displays success.

/u01/unix-odbc/bin/isql -v OBDSN

The expected output is as follows:

+-------------------------------+
|  Connected!                   |
|                               |
|  sql-statement                |
|  help [tablename]             |
|  quit                         |
|                               |
+-------------------------------+
SQL>

(Optional) Step 9: Compile the C++ test program

This section provides an example of how to compile a C++ test program using OceanBase Connector/ODBC to create a table, insert data, query data, and delete data.

Use the following command to create the test.cpp file:

vim test.cpp

The following file content provides a configuration example. You only need to replace DSN=OBDSN in the example with your actual DSN name. Here is an example of the OBDSN in the /etc/odbc.ini file:

#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=OBDSN", 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;
  }
  {
    //drop
    SQLExecDirect(handler.hstmt, (SQLCHAR*)"drop table PERSON", SQL_NTS);
    SQLFreeStmt(handler.hstmt, SQL_CLOSE);
  }
  {
    //create
    retcode = SQLExecDirect(handler.hstmt, (SQLCHAR*)"create table PERSON(id int, num int, name varchar2(100))", SQL_NTS);
    if (!IS_SUCC(retcode)) {
      checkError(retcode, "SQLExecDirect", &handler);
      return -1;
    }
    SQLFreeStmt(handler.hstmt, SQL_CLOSE);
  }
  {
    //insert
    retcode = SQLPrepare(handler.hstmt, (SQLCHAR*)"insert into PERSON values(?,?,'test')", SQL_NTS);
    if (!IS_SUCC(retcode)) {
      checkError(retcode, "SQLPrepare", &handler);
      return -1;
    }
    SQLINTEGER id = 0;
    SQLINTEGER num = 0;
    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 = SQLBindParameter(handler.hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &num, 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;
    }
    SQLFreeStmt(handler.hstmt, SQL_CLOSE);
  }
  {
    //update
    SQLINTEGER id = 0;
    retcode = SQLPrepare(handler.hstmt, (SQLCHAR*)"update PERSON set num=2, name='hello' where id=?", SQL_NTS);
    if (!IS_SUCC(retcode)) {
      checkError(retcode, "SQLPrepare", &handler);
      return -1;
    }
    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;
    }
    SQLFreeStmt(handler.hstmt, SQL_CLOSE);
  }

  {
    //select
    retcode = SQLPrepare(handler.hstmt, (SQLCHAR*)"select * from PERSON", SQL_NTS);
    if (!IS_SUCC(retcode)) {
      checkError(retcode, "SQLPrepare", &handler);
      return -1;
    }
    retcode = SQLExecute(handler.hstmt);
    if (!IS_SUCC(retcode)) {
      checkError(retcode, "SQLExecute", &handler);
      return -1;
    }

    SQLINTEGER id = 0;
    SQLINTEGER num = 0;
    char name[100] = { 0 };
    retcode = SQLBindCol(handler.hstmt, 1, SQL_INTEGER, &id, sizeof(id), NULL);
    if (!IS_SUCC(retcode)) {
      checkError(retcode, "SQLBindCol", &handler);
      return -1;
    }
    retcode = SQLBindCol(handler.hstmt, 2, SQL_INTEGER, &num, sizeof(num), NULL);
    if (!IS_SUCC(retcode)) {
      checkError(retcode, "SQLBindCol", &handler);
      return -1;
    }
    retcode = SQLBindCol(handler.hstmt, 3, SQL_CHAR, name, sizeof(name), NULL);
    if (!IS_SUCC(retcode)) {
      checkError(retcode, "SQLBindCol", &handler);
      return -1;
    }
    retcode = SQLFetch(handler.hstmt);
    if (!IS_SUCC(retcode)) {
      checkError(retcode, "SQLFetch", &handler);
      return -1;
    }
    SQLFreeStmt(handler.hstmt, SQL_CLOSE);
    printf("id=%d, num=%d, name=%s", id, num, name);
  }

  {
    //delete
    SQLINTEGER id = 0;
    retcode = SQLPrepare(handler.hstmt, (SQLCHAR*)"delete from PERSON where id=?", SQL_NTS);
    if (!IS_SUCC(retcode)) {
      checkError(retcode, "SQLPrepare", &handler);
      return -1;
    }
    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;
    }
    SQLFreeStmt(handler.hstmt, SQL_CLOSE);
  }

  // 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;
}

Run the following command to test:

g++ -I /u01/unix-odbc/include/ test.cpp -L/u01/ob-connector-odbc/lib64/mariadb -lobodbc -o test
./test