# oceanbase-access

Part of **OCEANBASE**

# OceanBase Database Access

## Capabilities Overview

| Sub-capability | Calling Mode | Description |
|----------------|--------------|-------------|
| Connect to Database | Synchronous | Connect to OceanBase Database using database drivers. |

## API Calling Patterns

### Authentication
OceanBase Database uses **username/password authentication** at the database connection level (not API keys or tokens). Credentials are provided directly in the connection string or via driver-specific session attributes.

- For JDBC: Include username and password in `DriverManager.getConnection(url, username, password)`
- For OCI: Set username and password using `OCIAttrSet()` on the session handle
- No environment variable is required—credentials are passed programmatically

This is standard database authentication; no bearer tokens or cloud IAM roles are used for direct driver connections.

### Service Endpoint
OceanBase Database is accessed via **direct TCP connections** to database server endpoints, not HTTP/REST APIs.

- JDBC URL pattern: `jdbc:oceanbase://<host>:<port>/<database>`
- OCI connection string: `<host>/<service_name>` (e.g., `"192.160.0.1/test"`)
- Common ports: `2883` (MySQL mode), `2881` (Oracle mode)
- Replace `<host>` with your OceanBase server IP or hostname

No region-based cloud endpoints are used—connections go directly to your deployed OceanBase instance.

### Synchronous Pattern
All database operations via OCI and JDBC drivers are 
1. Establish a connection using driver-specific initialization
2. Prepare SQL statements (`OCIStmtPrepare` or `Connection.prepareStatement`)
3. Bind parameters if needed (`OCIBindByName` or `PreparedStatement.setXXX`)
4. Execute the statement (`OCIStmtExecute` or `executeQuery`/`executeUpdate`)
5. Process results immediately (no polling or async callbacks)
6. Commit transactions explicitly if needed (`OCITransCommit` or `Connection.commit()`)
7. Close resources in reverse order of creation

There is no async task submission, streaming, or WebSocket usage in the native drivers.

## Parameter Reference

### Connect to Database

| Parameter | Type | Required | Default | Constraints | Description |
|----------|------|----------|---------|-------------|-------------|
| host | string | Yes | — | Valid IP or hostname | OceanBase server address |
| port | integer | Yes | — | 1–65535 | Database listener port (typically 2881 or 2883) |
| database / service_name | string | Yes | — | Non-empty | Target database or tenant name |
| username | string | Yes | — | Non-empty | Database user account |
| password | string | Yes | — | — | User password |
| useUnicode | boolean | No | false | true / false | Enable Unicode support (JDBC only) |
| characterEncoding | string | No | — | e.g., `utf-8` | Character encoding (JDBC only) |

## Code Examples

### Connect and Query (Java) - All Regions

```java
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();
    }
}
```

### Full CRUD Operations (C/C++ with OCI) - All Regions

```cpp
#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.*/
OCIError *errhp;     /*The error 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_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;
}
```

## FAQ

Q: Does OceanBase support ODBC drivers?
A: The documentation confirms support for OCI (C/C++) and JDBC (Java) drivers. ODBC support may be available through third-party or compatibility layers, but it is not covered in the official driver documentation provided.

Q: Can I use OceanBase with Python or Node.js?
A: The extracted documentation only describes OCI and JDBC drivers. For other languages like Python or Node.js, you would typically use MySQL or Oracle-compatible drivers (e.g., `mysql-connector-python` for MySQL mode) since OceanBase supports protocol compatibility.

Q: Is SSL/TLS encryption supported for connections?
A: While not explicitly detailed in the provided examples, OceanBase generally supports SSL for secure connections. Refer to your deployment’s configuration and driver documentation for enabling SSL (e.g., JDBC URL parameters like `useSSL=true`).

Q: What is the difference between Oracle mode and MySQL mode in connection strings?
A: In Oracle mode, the username often includes a tenant suffix (e.g., `SYS@oracle`), and SQL syntax follows Oracle conventions. In MySQL mode, standard MySQL-style usernames and SQL are used. The port also differs: 2881 for Oracle mode, 2883 for MySQL mode.

Q: Do I need to manage connection pooling manually?
A: The native drivers do not include built-in connection pooling. For production applications, integrate with external pooling libraries (e.g., HikariCP for Java, or custom pools in C++) to manage connections efficiently.

## Pricing & Billing

### Billing Model
Free for basic database driver usage. OceanBase Database itself may incur infrastructure costs depending on deployment (self-managed vs. Alibaba Cloud OceanBase instances), but the drivers and connectivity layer have no separate charges.

### Free Tier
No explicit pricing mentioned; assumed free for basic use.

### Usage Limits
No usage limits specified in the document.