Manage drivers

Java driver

We recommend that you use oceanbase-client, which is the JDBC driver of OceanBase Database.

To connect to MySQL tenants, you can also use the official JDBC driver of MySQL: mysql-connector-Java. We recommend that you use V5.1.30 or V5.1.40. Other versions may present compatibility issues. Notice

• oceanbase-client is fully compatible with the JDBC driver of MySQL. It can also identify whether OceanBase is running in MySQL or Oracle mode, because it is compatible with these two modes in the protocol layer. oceanbase-client is also compatible with the OB 2.0 protocol.

• Mysql-connector-Java only supports MySQL mode.

Instructions for using oceanbase-client

Set the prefix of the connection string to jdbc:oceanbase. All other usage is the same as native MySQL. Notice

In oceanbase-client V1.0.9, the name of the driver class is com.alipay.oceanbase.obproxy.mysql.jdbc.Driver.

In later versions, the name of the driver class is com.alipay.oceanbase.jdbc.Driver.

Sample code:

  String url = "jdbc:oceanbase://xxx.xxx.xxx.xxx:2883/SYS?useUnicode=true&characterEncoding=utf-8";       // The URL of the database in the following format: IP_address:OBProxy port number/database name
  String username = "SYS@test1#obtest";                                     // The username in the following format: username@tenant name#cluster name
  String password = "****";                                           // The password
  Connection conn = null;                                                                                         
  try {                                                                                                           
      Class.forName("com.alipay.oceanbase.obproxy.mysql.jdbc.Driver");                       // The driver class name
      conn = DriverManager.getConnection(url, username, password);
      PreparedStatement ps = conn.prepareStatement("select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;");
      ResultSet rs = ps.executeQuery();
      rs.next();
      System.out.println("sysdate is:" + rs.getString(1));
      rs.close();
      ps.close();
  } catch (Exception e) {
      e.printStackTrace();
  } finally {
      if (null != conn) {
          conn.close();
      }
  }

Mysql-connector-Java usage notes

Set the prefix of the connection string to jdbc:mysql. The name of the driver class is com.mysql.jdbc.Driver.

Sample code:

String url = "jdbc:mysql://xxx.xxx.xxx.xxx:2883/hr?useUnicode=true&characterEncoding=utf-8";          // The URL of the database in the following format: IP_address:OBProxy port number/database name
String username = "root@test2#obtest";                                     // The username in the following format: username@tenant name#cluster name
String password = "****";                                            // The password
Connection conn = null;
try {
    Class.forName("com.mysql.jdbc.Driver");                                    // The driver class name
    conn = DriverManager.getConnection(url, username, password);
    PreparedStatement ps = conn.prepareStatement("select date_format(now(),'%Y-%m-%d %H:%i:%s');");
    ResultSet rs = ps.executeQuery();
    rs.next();
    System.out.println("sysdate is:" + rs.getString(1));
    rs.close();
    ps.close();
} catch (Exception e) {
    e.printStackTrace();
} finally {
    if (null != conn) {
        conn.close();
    }
}

Use the JDBC driver

The table below describes some required parameters in JDBC. You can specify these parameters in the connection properties of the connection pool or in the JDBC URL.

Application connection pool settings

We recommend that you use connection pools for connecting applications to databases for implementing business operations. For Java applications, we recommend you use Druid Connection Pool. Configuration example:

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <!-- Basic attributes: url, username, and password -->
    <property name="url" value="jdbc:mysql://ip:port/db?socketTimeout=30000&amp;connectTimeout=3000" />
    <property name="username" value="{user}" />
    <property name="password" value="{password}" />
    <!-- The initial, minimum, and maximum numbers of connections. -->
    <property name="maxActive" value="4" />                                    // Set the initialSize, minIdle, or maxActive property based on your business scale
    <property name="initialSize" value="2" />
    <property name="minIdle" value="2" />
    <!-- The timeout interval for obtaining a connection, in milliseconds. -->
    <property name="maxWait" value="1000" />
    <!-- The interval for detecting idle connections that need to be released, in milliseconds. -->
    <property name="timeBetweenEvictionRunsMillis" value="60000" />
    <!-- The minimum duration in which a connection remains idle in the connection pool, in milliseconds. -->
    <property name="minEvictableIdleTimeMillis" value="300000" />
    <!-- The SQL statement for checking whether a connection is available. -->
    <property name="validationQuery" value="SELECT foo FROM bar" />                        // Use a real business table with only a few records in the statement for checking
    <!-- Specifies whether to enable idle connection detection. -->
    <property name="testWhileIdle" value="true" />
    <!-- Specifies whether to check the connection status before obtaining a connection. -->
    <property name="testOnBorrow" value="false" />
    <!-- Specifies whether to check the connection status when returning a connection. -->
    <property name="testOnReturn" value="false" />
</bean>

ODBC driver

Open Database Connectivity (ODBC) 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 the underlying driver programs that are transparent to the users. They 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. The driver supports ODBC function calls. Each driver is specific to a particular database type. When you migrate data of an application from one DBMS to another, you only need 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 customized. You can use this driver to connect to MySQL and Oracle tenants of OceanBase Database.

The following figure shows the architecture of using OB-ODBC on Linux to connect to OceanBase Database.

Connection environment configurations

Before you connect to the database, you must configure the connection environment. Perform the following steps:

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

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

2. Configure cat /etc/odbcinst.ini

   [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. After you finish the configuration, 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
   

Example

1. The following statement creates the TEST table 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 a driver for database connections

   Sample code of a 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 the logon timeout value 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, automatic commit is disabled by default in Oracle tenants. Therefore, the code contains an section for Oracle, which you can find after the ORACLE mode will need this section comment. This section is unnecessary for connecting to MySQL tenants.

3. Compile and execute the driver

   When you use GCC to compile the code, specify the 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
   

   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 |
   +----+-------+
   

MySQL C API

The mysqlclient library contains C APIs. When you use GCC to compile the code, specify the header files in the -Ioption, library file directories in the -L, option, and library names in the -l option. Sample statement:

gcc test.c -I/usr/include/mysql/ -L/usr/lib64/mysql -lmysqlclient

Notice

The library name is the library file name without the "lib" at the beginning and the ".so" at the end.

Sample code:

#include <mysql.h>
#include <stdio.h>
#include <string.h>

void main(void) {
    MYSQL conn;
    char server = "xxx.xxx.xxx.xxx";
    char user = "root@test#obtest";                        // The username in the following format: username@tenant_name#cluster_name
    char password = "****";                            // The password
    char *database = "test";                            // The database name
    char str_sqls;
    int status;
    int result;
    int i;

    conn = mysql_init(NULL); / Connect to database /
    / connect to server with the CLIENT_MULTI_STATEMENTS option /
    if (mysql_real_connect (conn, server, user, password,
            database, 3306, NULL, CLIENT_MULTI_STATEMENTS) == NULL)
    {
        printf("mysql_real_connect() failed\n");
        mysql_close(conn);
        exit(1);
    }
    / execute multiple statements /
    strcat(str_sqls, "DROP TABLE IF EXISTS test_table;");
    strcat(str_sqls, "CREATE TABLE test_table(id BIGINT);");
    strcat(str_sqls, "INSERT INTO test_table VALUES(10);");
    status = mysql_query(conn, str_sqls);
    if (status)
    {
        printf("Could not execute statement(s)");
        mysql_close(conn);
        exit(0);
    }
    mysql_close(conn);
}