Connect to an OceanBase database by using OBCI

This topic describes how to connect to and use an OceanBase database by using the OBCI driver.

Prerequisites

The basic database development environment is set.
The following hardware environment is prepared:
- Hardware platform: x86_64
- Operating system: CentOS or Red Hat Enterprise Linux 7.2
- Compiler: GNU Compiler Collection (GCC) 4.8
You have obtained the RPM packages of OBCI and LibOBClient from OceanBase Technical Support.

Step 1: Obtain the database connection parameters

For more information, see Obtain the connection parameters. Example:

obclient -hxxx.xxx.xxx.xxx -P1521 -u a**** -p******

The database connection parameters specify the information required to access the database. You can verify the database connection parameters by logging on to the database before using them in the sample code.

Options:

-h: the domain name of the OceanBase database to be connected.
-P: the port for connecting to the OceanBase database. By default, the port is 1521 in Oracle mode.
-u: specifies the tenant account.
-p: the account password.

Step 2: Install OBCI relevant drivers

After you obtain the RPM package, run the following commands in the command-line tool as the root user:

OBCI depends on LibOBClient. Therefore, you must install LibOBClient first.

$ rpm -ivh libobclient-<version>.x86_64.rpm

Install OBCI.

$ rpm -ivh obci-<version>.x86_64.rpm

By default, the programs and files contained in the package are installed in the following paths:

The header file is installed in the /u01/obclient/include path.
The library files are installed in the /u01/obclient/lib path.

Due to copyright restrictions, database connection using OBCI of a later version involves Oracle Instant Client . For more information about how to install Oracle Instant Client, see Oracle Instant Client Downloads.

Generally, the basic and SDK packages are to be installed.

Install the basic package.

$ rpm -ivh oracle-instantclient-basic-<version>.x86_64.rpm

Install the SDK package.

$ rpm -ivh oracle-instantclient-devel-<version>.x86_64.rpm

Step 3: Write the sample code

This topic describes the interaction between OBCI and OBServer nodes in Oracle mode with the following examples:

Initialize the OBCI environment and threads.
- Initialize the OBCI environment
```
OCIInitialize(OCI_DEFAULT, NULL, NULL, NULL, NULL)
```
- Initialize the environment handle
```
OCIEnvInit(&envhp, OCI_DEFAULT, 0, 0)
```

Allocate necessary handles and data structures.

Allocate a handle

OCIHandleAlloc(envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX, 0, 0)

A server environment handle

OCIHandleAlloc(envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER, 0, 0)

A server handle

OCIHandleAlloc(envhp, (dvoid **)&authp, OCI_HTYPE_SESSION, 0, 0)

A session handle

OCIHandleAlloc(envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, 0, 0)

An error handle

OCIHandleAlloc(envhp, (dvoid **)&dschp, OCI_HTYPE_DESCRIBE, 0, 0)

Establish a connection to the database and create a user session.

Set a username

 OCIAttrSet(authp, OCI_HTYPE_SESSION, (text *)strUserName,(ub4)strlen(strUserName), OCI_ATTR_USERNAME, errhp)

Set the password

OCIAttrSet(authp, OCI_HTYPE_SESSION, (text *)strPassword,(ub4)strlen(strPassword), OCI_ATTR_PASSWORD, errhp)

Set the attributes of the server environment handle

OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,(dvoid *)srvhp, (ub4)0, OCI_ATTR_SERVER, errhp)
OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, (dvoid *)authp,0, OCI_ATTR_SESSION, errhp)

Create a session

OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT)

Start the session

OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0)

Exchanges data with the OBServer node by using SQL statements before data processing. The steps of executing an SQL statement in an OBCI application:
1. Call the OCIStmtPrepare() or OCIStmtPrepare2() function to prepare an SQL statement.
```
OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,OCI_DEFAULT)
```
2. Call one or multiple functions, such as OCIBindByPos(), to bind the address in the input variable to the placeholder of the DML statement.
```
OCIBindByPos(stmthp, &bidhp[0], errhp, 1, &szpersonid,
 sizeof(szpersonid), SQLT_INT, NULL, NULL, NULL, 0, NULL, 0)
```
  You can also use the OCIBindByName() function.
```
OCIBindByName(stmthp, &bidhp[0], errhp, (const OraText*)":personid", 9, &szpersonid,
 sizeof(szpersonid), SQLT_INT, NULL, NULL, NULL, 0, NULL, 0)
```
3. Call the OCIStmrExecute() function to execute the SQL statement.
```
OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DEFAULT)
```
4. Call the OCIDefineByPos() function to define output variables for the data output items of the SQL statement.
```
OCIDefineByPos(stmthp, &defhp[0], errhp, 1, &szpersonid,
  sizeof(szpersonid), SQLT_INT, &ind[0], 0, 0, OCI_DEFAULT)
```
5. Call the OCIStmtFetch() function to obtain the result set of a query.
```
OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)
```
End the user session and disconnect from OceanBase Database.
- End the session
```
OCISessionEnd(svchp, errhp, authp, (ub4)0)
```
- Disconnect from the database
```
OCIServerDetach(srvhp, errhp, OCI_DEFAULT)
```

Release the allocated handles.

OCIHandleFree((dvoid *)dschp, OCI_HTYPE_DESCRIBE)
OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT)
OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR)
OCIHandleFree((dvoid *)authp, OCI_HTYPE_SESSION)
OCIHandleFree((dvoid *)svchp, OCI_HTYPE_SVCCTX)
OCIHandleFree((dvoid *)srvhp, OCI_HTYPE_SERVER)

Sample code

The following example shows the content of a test.c file:

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <malloc.h>
#include "oci.h"
/*Declare 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 statement handle.*/
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 result set parameters of SELECT statements.*/
int szpersonid; /*Stores the personid column.*/
text szname[51]; /*Stores the name column.*/
text szemail[51]; /*Stores the mail column.*/
char sql[256]; /*Stores the executed SQL statements.*/

int main(int argc, char *argv[])
{
 char strServerName[50];
 char strUserName[50];
 char strPassword[50];
 /*Set the server name, username, and password.*/
 strcpy(strServerName, "xxx.xxx.xxx.xxx:1521");
 strcpy(strUserName, "a****");
 strcpy(strPassword, "******");
 /*Initialize the OCI application environment.*/
 OCIInitialize(OCI_DEFAULT, NULL, NULL, NULL, NULL);
 /*Initialize the environment handle.*/
 OCIEnvInit(&envhp, OCI_DEFAULT, 0, 0);
 /*Allocate an environment handle.*/
 OCIHandleAlloc(envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX, 0, 0);
 /*Server environment handle.*/
 OCIHandleAlloc(envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER, 0, 0);
 /*Server handle.*/
 OCIHandleAlloc(envhp, (dvoid **)&authp, OCI_HTYPE_SESSION, 0, 0);
 /*Session handle.*/
 OCIHandleAlloc(envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, 0, 0);
 /*Error handle.*/
 OCIHandleAlloc(envhp, (dvoid **)&dschp, OCI_HTYPE_DESCRIBE, 0, 0);
 /*Descriptor handle.*/
 /*Connect to the server.*/
 OCIServerAttach(srvhp, errhp, (text *)strServerName, (sb4)strlen(strServerName), OCI_DEFAULT);
 /*Set the username and password.*/
 OCIAttrSet(authp, OCI_HTYPE_SESSION, (text *)strUserName, (ub4)strlen(strUserName), OCI_ATTR_USERNAME, errhp);
 OCIAttrSet(authp, OCI_HTYPE_SESSION, (text *)strPassword, (ub4)strlen(strPassword), OCI_ATTR_PASSWORD, errhp);
 /*Set the attributes of the server environment handle.*/
 OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX, (dvoid *)srvhp, (ub4)0, OCI_ATTR_SERVER, errhp);
 OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, (dvoid *)authp, 0, OCI_ATTR_SESSION, errhp);
 /*Create and start a user session.*/
 OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT);
 OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0);
 /*The handle to the SQL statement being processed.*/

 /************************************************************************/
 /*Create a table named person.*/
 /************************************************************************/
 static text* SQL_CREATE_TB = (text*)"create table person(personid number, name varchar(256), email varchar(256))";
 /*Prepare the SQL statement.*/
 OCIStmtPrepare(stmthp, errhp, SQL_CREATE_TB, strlen((char *)SQL_CREATE_TB),OCI_NTV_SYNTAX, OCI_DEFAULT);
 /*Execute the SQL statement.*/
 OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, OCI_DEFAULT);
 /*Commit data to the database.*/
 OCITransCommit(svchp, errhp, OCI_DEFAULT);

 /************************************************************************/
 /*Insert data.*/
 /************************************************************************/
 memset(sql, 0, sizeof(sql));
 strcpy(sql, "insert into person values(:personid,:name,:email)");
 /*Prepare the SQL statement.*/
 OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql),OCI_NTV_SYNTAX, OCI_DEFAULT);
 /*Bind the input columns.*/
 OCIBindByName(stmthp, &bidhp[0], errhp, (const OraText*)":personid", 9, &szpersonid, sizeof(szpersonid), SQLT_INT, NULL, NULL, NULL, 0, NULL, 0);
 OCIBindByName(stmthp, &bidhp[2], errhp, (const OraText*)":name", 5, szname, sizeof(szname), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0);
 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(szname, 0, sizeof(szname));
 strcpy((char*)szname, "obtest");
 memset(szemail, 0, sizeof(szemail));
 strcpy((char*)szemail, "t@ob.com");
 /*Execute the SQL statement.*/
 OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DEFAULT);
 /*Commit data to the database.*/
 OCITransCommit(svchp, errhp, OCI_DEFAULT);

 /************************************************************************/
 /*Query the person table.*/
 /************************************************************************/
 strcpy(sql, "select personid ,name,email from person;");
 /*Prepare the SQL statement.*/
 OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);
 /*Bind the output columns.*/
 OCIDefineByPos(stmthp, &defhp[0], errhp, 1, &szpersonid, sizeof(szpersonid), SQLT_STR, &ind[0], 0, 0, OCI_DEFAULT);
 OCIDefineByPos(stmthp, &defhp[1], errhp, 2, (ub1 *)szname, sizeof(szname), SQLT_STR, &ind[1], 0, 0, OCI_DEFAULT);
 OCIDefineByPos(stmthp, &defhp[2], errhp, 3, (ub1 *)szemail, sizeof(szemail), SQLT_STR, &ind[2], 0, 0, OCI_DEFAULT);
 /*Execute the SQL statement.*/
 OCIStmtExecute(svchp, stmthp, errhp, (ub4)0, 0, NULL, NULL,
 OCI_DEFAULT);
 printf("%-10s%-10s%-10s\n", "PERSONID", "NAME", "email");
 while ((OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) != OCI_NO_DATA)
 {
 printf("%-10d", szpersonid);
 printf("%-10s", szname);
 printf("%-10s\n", szemail);
 break;
 }
 /*Commit data to the database.*/
 OCITransCommit(svchp, errhp, OCI_DEFAULT);

 /************************************************************************/
 /*Drop the person table.*/
 /************************************************************************/
 static text* SQL_DROP_TB = (text*)"drop table person";
 /*Prepare the SQL statement.*/
 OCIStmtPrepare(stmthp, errhp, (text*)SQL_DROP_TB, strlen((char *)SQL_DROP_TB), OCI_NTV_SYNTAX, OCI_DEFAULT);
 /*Execute the SQL statement.*/
 OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, OCI_COMMIT_ON_SUCCESS);
 /*Commit data to the database.*/
 OCITransCommit(svchp, errhp, OCI_DEFAULT);
 
 // End the session.
 OCISessionEnd(svchp, errhp, authp, (ub4)0);
 // Disconnect from the database.
 OCIServerDetach(srvhp, errhp, OCI_DEFAULT);
 // Release OCI handles.
 OCIHandleFree((dvoid *)dschp, OCI_HTYPE_DESCRIBE);
 OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT);
 OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR);
 OCIHandleFree((dvoid *)authp, OCI_HTYPE_SESSION);
 OCIHandleFree((dvoid *)svchp, OCI_HTYPE_SVCCTX);
 OCIHandleFree((dvoid *)srvhp, OCI_HTYPE_SERVER);
 return 0;
}

Modify the database connection parameters in the code. Refer to the following parameters and format. The parameter values are obtained in Step 1.

strcpy(strServerName, "xxx.xxx.xxx.xxx:1521");
strcpy(strUserName, "a****");
strcpy(strPassword, "******");

strServerName: the domain name and port of the OceanBase database to be connected, which correspond to the -h and -Poptions. For an Oracle tenant, the default port number is 1521.
strUserName: the tenant account, which corresponds to the -u option.
strPassword: the tenant account password, which corresponds to the -p option.

Step 4: Execute the sample code

After you edit the code, run the following command:

$ gcc test.c -I/u01/obclient/include /u01/obclient/lib/libobci.a -L/usr/local/lib64 -lstdc++ -lpthread -ldl -lm -g -o test

If you use a later version of OBCI, install Oracle Instant Client. -I must use the Oracle directory as shown in the following example:

$ gcc test.c -I/usr/include/oracle/21/client64/ -L/u01/obclient/lib/ -L/usr/local/lib64 -lobci -lobclnt -g  -o test

After the compilation, specify the execution path.
```
$ export LD_LIBRARY_PATH=/u01/obclient/lib
```
Run the sample code.
```
$ ./test
```
If the following result is returned, the database is connected and the sample statement is executed correctly.
```
PERSONID  NAME      email
49        obtest    t@ob.com
```