OBCI APIs|V3.2.4|OceanBase Database| docs|Distributed Database

OBCI APIs

Last Updated：2023-10-24 09:23:03 Updated

This topic describes common OceanBase Call Interface (OBCI) APIs and provides examples.

Overview of C API functions

The following table lists all available C APIs in OBCI.

For more information about the APIs, see OceanBase Connector/C.

OBCI objects

The following table describes the general internal objects of Oracle Call Interface (OCI) that are supported by OBCI.

Object	Description
OCIEnv	The environment handle of OCI.
OCIError	The error processing handle of OCI, which is used to obtain error information.
OCISvcCtx	The service context of OCI, such as the memory and connections.
OCIStmt	The statement processing object of OCI.
OCIBind	The bind variable of OCI.
OCIDefine	The result variable of OCI.
OCIDescribe	The definition type of OCI.
OCIServer	The backend OBServer of OCI.
OCISession	The session of an OCI connection.
OCITrans	The OCI transaction.
OCICPool	The connection pool of OCI.
OCIAuthInfo	The information about OCI connection verification.
OCILobLocator	The OCI large object (LOB) locator, which is used for LOB function processing.
OCIParam	The OCI parameter, which is used to obtain metadata.
OCIRowid	The ROWID value of OCI.
OCIDate	The OCI date.
OCIDateTime	The OCI timestamp.
OCIInterval	The time interval of OCI.
OCINumber	The high-precision numeric value of OCI.
OCIString	The OCI string.

OBCI functions

Initialization-related functions

The following table describes the functions that are used for processing initialization and connection authentication.

Function	Description
OCIEnvCreate	Creates and initializes an environment handle.
OCIEnvNlsCreate	Creates and initializes an environment handle based on which OCI functions work. It serves as an enhanced version of the OCIEnvCreate() function.
OCIEnvInit	Initializes and allocates an environment handle.
OCIInitialize	Initializes the OCI application environment. OCI initializes internal global variables and loads some configuration information by calling this function.
OCILogoff	Closes a connection to a server that is established by calling the OCILogon() or OCILogon2() function.
OCILogon	Establishes a connection to a specified database server by using a username and a password and initializes the related context handle.
OCILogon2	Establishes a connection to a specified database server by using a username and a password and initializes the related context handle. This function supports connection pools.
OCIServerAttach	Attaches a database server to a specified connection handle.
OCIServerDetach	Detaches a connection handle from a database server.
OCISessionBegin	Starts a session with a specified database server on a specified context handle based on the logon credentials.
OCISessionEnd	Releases the session with the database server that is started by calling the OCISessionBegin() function on the context handle.
OCIPing	Checks whether the connection and server are active. A call to this function succeeds only when the server is active and the connection exists.
OCITerminal	Terminates the thread processing.

Object initialization-related functions

The following table describes the functions that are related to handles and descriptors.

Function	Description
OCIAttrGet	Obtains an attribute value of a handle.
OCIAttrSet	Sets an attribute for a handle.
OCIDescriptorAlloc	Allocates a descriptor handle to store descriptors.
OCIDescriptorFree	Releases a descriptor allocated by calling the OCIDescriptorAlloc() function.
OCIHandleAlloc	Allocates and initializes handles.
OCIHandleFree	Releases handles allocated by calling the OCIHandleAlloc() function.
OCIParamGet	Obtains a descriptor handle at a specified position.

Functions related to parameter binding and result data processing

The following table describes the functions that are related to the Bind, Define, and Describe operations and result set space.

Function	Description
OCIBindArrayOfStruct	Binds parameters as arrays.
OCIBindByName	Binds a parameter to a placeholder in an SQL statement by parameter name.
OCIBindByPos	Binds a parameter to a placeholder in an SQL statement by parameter position.
OCIDefineArrayOfStruct	Binds a column in a return value set as an array.
OCIDefineByPos	Binds the value range of each column in a return value set by the position of the value.

Statement processing-related functions

The following table describes the functions that are related to statement processing.

Function	Description
OCIStmtPrepare	Prepares an SQL statement, initializes an Stmtp object, and then calls the OCIStmtExecute() function to execute the statement.
OCIStmtExecute	Executes the prepared SQL statement.
OCIStmtFetch	Fetches rows from the result set of an SQL statement. After an SQL statement is executed, this function can be called multiple times to return all rows of the result set until this function returns OCI_NO_DATA.
OCIStmtRelease	Releases the statement handles fetched by calling the OCIStmtPrepare2() function.

Transaction processing-related functions

The following table describes the functions that are related to transaction processing.

Function	Description
OCITransStart	Starts a transaction.
OCITransCommit	Commits an SQL execution task.
OCITransRollback	Rolls back an SQL execution task.

Data type-related functions The following table describes the functions that are related to different data types.

Data object	Function	Description
OCIString	OCIStringAllocSize	Obtains the size of allocated string memory in bytes or code points (Unicode).
OCIString	OCIStringAssign	Assigns a string to another string.
OCIString	OCIStringAssignText	Assigns a string to another string.
OCIString	OCIStringPtr	Obtains the pointer to a specified string.
OCIString	OCIStringSize	Obtains the size of a specified string.
OCIString	OCIStringResize	Obtains the size of a specified string.
OCILoblocator	OCILobGetLength	Returns the length of the LOB, in bytes.
OCILoblocator	OCILobRead	Reads the content of a specified length from a LOB.
OCILoblocator	OCILobWrite	Continuously writes content to a LOB.
OCILoblocator	OCILobLocatorIsInit	Checks whether a specified LOB or BFILE locator is initialized.
OCILoblocator	OCILobOpen	Opens a LOB or a BFILE object.
OCILoblocator	OCILobClose	Closes a LOB or a BFILE object.
OCILoblocator	OCILobIsOpen	Tests whether a LOB or a FILE object is open.
OCILoblocator	OCILobTrim	Truncates a LOB value.
OCILoblocator	OCILobTrim2	Truncates a LOB value. This function can be used only for LOBs that are not greater than 48 MB in size in OceanBase.
OCIDate	OCIDateSysDate	Obtains the current system date and time of a client.
OCIDate	OCIDateToText	Converts a date into a string of a specified format.
OCIDate	OCIDateFromText	Converts a string into a date in a specified format.
OCIDateTime	OCIDateTimeToText	Converts a datetime value into a string in a specified format.
OCIDateTime	OCIDateTimeFromText	Converts a datetime value into a string in a specified format.
OCIDateTime	OCIDateTimeConstruct	Constructs a datetime value.
OCIDateTime	OCIDateTimeGetTime	Obtains the time, including the values for the hour, minute, second, and fractional second, from a datetime value.
OCIDateTime	OCIDateTimeGetDate	Obtains the date, including values for the year, month, and day, from a datetime value.
OCIDateTime	OCIDateTimeGetTimeZoneOffset	Obtains the time zone, including values for the hour and minute, from a datetime value.
OCINumber	OCINumberToInt	Converts a value of the Oracle `NUMBER` type into an integer.
OCINumber	OCINumberToReal	Converts a value of the Oracle `NUMBER` type into a real number.
OCINumber	OCINumberToText	Converts a value of the Oracle `NUMBER` type into a string in a specified format.
OCINumber	OCINumberIsInt	Tests whether an OCINumber value is an integer.
OCINumber	OCINumberFromText	Converts a string into a value of the Oracle NUMBER type.

Other functions

The following table describes some other general functions.

Function	Description
OCIBreak	Performs an immediate (asynchronous) termination on the current OBCI feature that is associated with a server.
OCIClientVersion	Returns the version of the OCI client where the application runs.
OCIServerVersion	Returns the version of the OCI server.
OCIErrorGet	Returns an error message and an OceanBase error code to the provided buffer.
OCIPasswordChange	Changes the password of an account.
OCIPing	Makes a round trip call to a server to check whether the connection and the server are active.

Examples

Call the basic APIs to create a table, query the table, insert data, and update the data

/**********************************************************
* Copyright(C) 2014 - 2020 Alibaba Inc. All Rights Reserved.
*
*  Filename: example.c
*  Description: ----
*  Create: 2021-05-05 10:14:59
*  Last Modified: 2021-05-05 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 the parameters in the SELECT result set.*/
int szpersonid;     /*Store the personid column.*/
text szsex[2];      /*Store the sex column.*/
text szname[10];    /*Store the name column.*/
text szemail[10];   /*Store the mail column.*/
char sql[256];      /*Store 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 checker(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) \
  checker(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 parameters.*/
    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 data to the 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 data to the 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 data to the 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.*/
    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));
    /*The descriptor handle.*/
    /*Connect to the server.*/
    OCI_CHECK_RET(errhp, OCIServerAttach(srvhp, errhp, (text *)strServerName,
                    (sb4)strlen(strServerName), OCI_DEFAULT));
    /*Set the 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 the 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 table named person.*/
    /************************************************************************/
    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 that 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));
    //Release 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;
}