In MySQL mode, users may use official MySQL Connectors (connectors for MySQL 8.0 are not supported). In Oracle mode, users must use database drivers developed for OceanBase Database. OceanBase Database drivers support both the MySQL and Oracle protocols of OceanBase Database. They automatically detect if OceanBase Database is running in MySQL or Oracle mode without the need for any additional configuration. OceanBase Database supports database drivers programmed in multiple languages. A few common languages and standards are listed below:
OCI driver
OceanBase Database provides a database driver based on the Oracle Call Interface (OCI). This driver is compatible with the method and function names of OCI. For applications programmed by using OCI, you can migrate these applications to OceanBase Database without significant business-related transformation.
Sample code:
/**********************************************************
* Copyright(C) 2014 - 2020 Alibaba Inc. All Rights Reserved.
*
* Filename: example.c
* Description: ----
* Create: 2020-07-07 10:14:59
* Last Modified: 2020-07-07 10:14:59
***********************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <malloc.h>
#include "oci.h"
/*Statement handles*/
OCIEnv *envhp; /*The environment handle.*/
OCISvcCtx *svchp; /*The service environment handle.*/
OCIServer *srvhp; /*The server handle.*/
OCISession *authp; /*The session handle.*/
OCIStmt *stmthp; /*The handle to the SQL statement being processed.*/
OCIDescribe *dschp; /*The description handle.*/
OCIError *errhp; /*The error handle.*/
OCIDefine *defhp[3]; /*The definition handle.*/
OCIBind *bidhp[4]; /*The bind handle.*/
sb2 ind[3]; /*The indicator variable.*/
/*Bind parameters of the select result set*/
int szpersonid; /*Stores the personid column.*/
text szsex[2]; /*Stores the sex column.*/
text szname[10]; /*Stores the name column.*/
text szemail[10]; /*Stores the mail column.*/
char sql[256]; /*Stores the executed SQL statements.*/
static text* SQL_DROP_TB = (text*)"drop table person";
static text* SQL_CREATE_TB = (text*)"create table person(personid number, sex varchar2(256), name varchar2(256), email varchar2(256))";
void checkerr(OCIError *errhp, sword status, const char* filename, int line) {
text errbuf[512];
sb4 errcode = 0;
switch (status)
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
(void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
(void) printf("%s:%d Error - OCI_SUCCESS_WITH_INFO now get is %d:%.*s\n", filename, line, errcode, 512, errbuf);
break;
case OCI_NEED_DATA:
(void) printf("%s:%d Error - OCI_NEED_DATA\n", filename, line);
break;
case OCI_NO_DATA:
(void) printf("%s:%d Error - OCI_NODATA\n", filename, line);
break;
case OCI_ERROR:
(void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
(void) printf("%s:%d Error - now get is %d:%.*s\n", filename, line, errcode, 512, errbuf);
break;
case OCI_INVALID_HANDLE:
(void) printf("%s:%d Error - OCI_INVALID_HANDLE\n", filename, line);
break;
case OCI_STILL_EXECUTING:
(void) printf("%s:%d Error - OCI_STILL_EXECUTE\n", filename, line);
break;
case OCI_CONTINUE:
(void) printf("%s:%d Error - OCI_CONTINUE\n", filename, line);
break;
default:
break;
}
}
#define OCI_CHECK_RET(errhp, function) \
checkerr(errhp, function, __FILE__, __LINE__)
/************************************************************************/
/*Query the person table*/
/************************************************************************/
void query_tables() {
sword status = OCI_SUCCESS;
memset(sql, 0, sizeof(sql));
strcpy(sql, "select personid, name, email from person");
/*Prepare the SQL statement*/
OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT));
/*Bind the output column*/
OCI_CHECK_RET(errhp, OCIDefineByPos(stmthp, &defhp[0], errhp, 1, &szpersonid,
sizeof(szpersonid), SQLT_INT, &ind[0], 0, 0, OCI_DEFAULT));
OCI_CHECK_RET(errhp, OCIDefineByPos(stmthp, &defhp[1], errhp, 2, (ub1 *)szname,
sizeof(szname), SQLT_STR, &ind[1], 0, 0, OCI_DEFAULT));
OCI_CHECK_RET(errhp, OCIDefineByPos(stmthp, &defhp[2], errhp, 3, (ub1 *)szemail,
sizeof(szemail), SQLT_STR, &ind[2], 0, 0, OCI_DEFAULT));
/*Execute the SQL statement*/
OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)0, 0, NULL, NULL,
OCI_DEFAULT));
printf("%-10s%-10s%-10s\n", "PERSONID", "NAME", "EMAIL");
while ((status = OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) == OCI_SUCCESS) {
printf("%-10d", szpersonid);
printf("%-10s", szname);
printf("%-10s\n", szemail);
}
if (OCI_NO_DATA != status) {
printf("error ! error ! error ! err=%d\n", status);
} else {
printf("finish fetch data\n");
}
}
void insert_tables() {
memset(sql, 0, sizeof(sql));
strcpy(sql, "insert into person values(:personid,:sex,:name,:email)");
/*Prepare the SQL statement*/
OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,
OCI_DEFAULT));
/*Bind the input column*/
OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[0], errhp, (const OraText*)":personid", 9, &szpersonid,
sizeof(szpersonid), SQLT_INT, NULL, NULL, NULL, 0, NULL, 0));
OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[1], errhp, (const OraText*)":sex", 4, szsex,
sizeof(szsex), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));
OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[2], errhp, (const OraText*)":name", 5, szname,
sizeof(szname), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));
OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[3], errhp, (const OraText*)":email", 6, szemail,
sizeof(szemail), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));
/*Set the input parameter*/
szpersonid = 1;
memset(szsex, 0, sizeof(szsex));
strcpy((char*)szsex, "M");
memset(szname, 0, sizeof(szname));
strcpy((char*)szname, "obtest");
memset(szemail, 0, sizeof(szemail));
strcpy((char*)szemail, "t@ob.com");
/*Execute the SQL statement*/
OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DEFAULT));
/*Commit to database*/
OCI_CHECK_RET(errhp, OCITransCommit(svchp, errhp, OCI_DEFAULT));
while ((OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) == OCI_SUCCESS) {
printf("%-10d", szpersonid);
printf("%-10s", szsex);
printf("%-10s", szname);
printf("%-10s\n", szemail);
}
printf("finish insert tables\n");
}
void update_tables() {
memset(sql, 0, sizeof(sql));
strcpy(sql, "update person set sex='M',name='test',email='test@mail' WHERE personid=1");
/*Prepare the SQL statement*/
OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,
OCI_DEFAULT));
/*Execute the SQL statement*/
OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DEFAULT));
/*Commit to database*/
OCI_CHECK_RET(errhp, OCITransCommit(svchp, errhp, OCI_DEFAULT));
}
void delete_tables() {
memset(sql, 0, sizeof(sql));
strcpy(sql, "delete from person WHERE personid = :personid");
/*Prepare the SQL statement*/
OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,
OCI_DEFAULT));
/*Bind the input parameters*/
szpersonid = 1;
OCI_CHECK_RET(errhp, OCIBindByPos(stmthp, &bidhp[0], errhp, 1, &szpersonid,
sizeof(szpersonid), SQLT_INT, NULL, NULL, NULL, 0, NULL, 0));
/*Execute the SQL statement*/
OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DEFAULT));
/*Commit to database*/
OCI_CHECK_RET(errhp, OCITransCommit(svchp, errhp, OCI_DEFAULT));
}
int main(int argc, char *argv[]) {
char strServerName[50];
char strUserName[50];
char strPassword[50];
/*Configure the server, username, and password as appropriate*/
strcpy(strServerName, "192.160.0.1/test");
strcpy(strUserName, "test");
strcpy(strPassword, "test");
/*Initialize the OCI application environment*/
OCI_CHECK_RET(errhp, OCIInitialize(OCI_DEFAULT, NULL, NULL, NULL, NULL));
/*Initialize the environment handle*/
OCI_CHECK_RET(errhp, OCIEnvInit(&envhp, OCI_DEFAULT, 0, 0));
/*Allocate handles*/
OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX, 0, 0));
/*Server environment handle*/
OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER, 0, 0));
/*Server handle*/
OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&authp, OCI_HTYPE_SESSION, 0, 0));
/*Session handle*/
OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, 0, 0));
/*Error handle*/
OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&dschp, OCI_HTYPE_DESCRIBE, 0, 0));
/*Descriptor handle*/
/*Connect to server*/
OCI_CHECK_RET(errhp, OCIServerAttach(srvhp, errhp, (text *)strServerName,
(sb4)strlen(strServerName), OCI_DEFAULT));
/*Set username and password*/
OCI_CHECK_RET(errhp, OCIAttrSet(authp, OCI_HTYPE_SESSION, (text *)strUserName,
(ub4)strlen(strUserName), OCI_ATTR_USERNAME, errhp));
OCI_CHECK_RET(errhp, OCIAttrSet(authp, OCI_HTYPE_SESSION, (text *)strPassword,
(ub4)strlen(strPassword), OCI_ATTR_PASSWORD, errhp));
/*Set attributes of the server environment handle*/
OCI_CHECK_RET(errhp, OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
(dvoid *)srvhp, (ub4)0, OCI_ATTR_SERVER, errhp));
OCI_CHECK_RET(errhp, OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, (dvoid *)authp,
0, OCI_ATTR_SESSION, errhp));
/*Create and start a user session*/
OCI_CHECK_RET(errhp, OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT));
OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0));
/*The handle to the SQL statement being processed.*/
/************************************************************************/
/*Create the person table*/
/************************************************************************/
OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text*)SQL_DROP_TB, strlen((char *)SQL_DROP_TB), OCI_NTV_SYNTAX, OCI_DEFAULT));
OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, OCI_COMMIT_ON_SUCCESS));
OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, SQL_CREATE_TB, strlen((char *)SQL_CREATE_TB), OCI_NTV_SYNTAX, OCI_DEFAULT));
OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, OCI_DEFAULT));
/************************************************************************/
/*Query the person table*/
/************************************************************************/
query_tables();
/************************************************************************/
/*Insert a data record to the person table*/
/************************************************************************/
insert_tables();
query_tables();
/************************************************************************/
/*Update the person table*/
/************************************************************************/
update_tables();
query_tables();
/************************************************************************/
/*Delete a data record from the person table by ID. Ensure this record exists in this table.*/
/************************************************************************/
delete_tables();
query_tables();
/************************************************************************/
/*End the execution and release resources.*/
/************************************************************************/
//End the session
OCI_CHECK_RET(errhp, OCISessionEnd(svchp, errhp, authp, (ub4)0));
//Disconnect from the database
OCI_CHECK_RET(errhp, OCIServerDetach(srvhp, errhp, OCI_DEFAULT));
//Free OCI handles
OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)dschp, OCI_HTYPE_DESCRIBE));
OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT));
OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR));
OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)authp, OCI_HTYPE_SESSION));
OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)svchp, OCI_HTYPE_SVCCTX));
OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)srvhp, OCI_HTYPE_SERVER));
return 0;
}
JDBC driver
OceanBase Database provides a database driver based on Java Database Connectivity (JDBC), a standard API that provides database access for Java applications. The OceanBase Database driver adapts the JDBC API to SQL APIs of other database providers. The JDBC driver of OceanBase Database is compatible with JDBC 4.0, 4.1, and 4.2.
The prefix of the JDBC string URL in OceanBase is jdbc:oceanbase, and the URL usage is the same as that in JDBC interface. Sample code:
String url = "jdbc:oceanbase://host:port/SYS? useUnicode=true&characterEncoding=utf-8";
String username = "SYS@oracle";
String password = "";
Connection conn = null;
try {
Class.forName("com.alipay.oceanbase.jdbc.Driver");
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();
}
}