Build a C application|V4.2.1|OceanBase Database| docs|Distributed Database

This topic describes how to build a C application with OceanBase Database and OceanBase Call Interface (OBCI).

Applicability

This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.

Prerequisites

A basic database development environment is deployed.
The following hardware and software versions are used:
- Architecture: x86_64
- Operating system: CentOS/Red Hat Linux 7.2
- Compiler: GNU Compiler Collection (GCC) 4.8
The RPM packages of OBCI and OceanBase Connector/C have been obtained from OceanBase Technical Support.

Procedure

Step 1: Obtain a database connection string

Obtain a database connection string from a database deployment engineer or administrator. For example:

obclient  -h100.88.xx.xx -usys@oracle -p****** -P2881

The database connection string contains parameters required for accessing OceanBase Database. Before you create an application, you can log on to OceanBase Database by using the database connection string, to verify that the parameters are correct.

The parameters are described as follows:

-h: the IP address for connecting to OceanBase Database, which is sometimes the IP address of an OceanBase Database Proxy (ODP).
-u: the username for connecting to a tenant, in the format of username@tenant name#cluster name. In Oracle mode, the default username of the administrator is sys. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through an ODP.
-p: the user password.
-P: the port for connecting to OceanBase Database, which is also the listening port of the ODP.

Step 2: Install C drivers

On the CLI, run the following commands as the root user to install OBCI and OceanBase Connector/C:

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

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

The header files are installed in the /u01/obclient/include directory.
The library files are installed in the /u01/obclient/lib directory.

Step 3: Write an application

The following describes how a C application uses OBCI to interact with an OBServer node in OceanBase Database in Oracle mode.

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.

/*Server environment handle*/
 OCIHandleAlloc(envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX, 0, 0)

/*Server handle*/
 OCIHandleAlloc(envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER, 0, 0)

/*Session handle*/
 OCIHandleAlloc(envhp, (dvoid **)&authp, OCI_HTYPE_SESSION, 0, 0)

/*Error handle*/
 OCIHandleAlloc(envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, 0, 0)

/*Description handle*/
 OCIHandleAlloc(envhp, (dvoid **)&dschp, OCI_HTYPE_DESCRIBE, 0, 0)

Establish a connection to OceanBase Database and create a user session.

/*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)

Exchange data with the OBServer node by using SQL statements, and then perform data processing. Steps of executing an SQL statement in the OBCI application are as follows:

Call the OCIStmtPrepare() or OCIStmtPrepare2() function to prepare an SQL statement.

OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,OCI_DEFAULT)

Call one or more functions, such as OCIBindByPos() or OCIBindByName(), 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)

OCIBindByName(stmthp, &bidhp[0], errhp, (const OraText*)":personid", 9, &szpersonid,
                  sizeof(szpersonid), SQLT_INT, NULL, NULL, NULL, 0, NULL, 0)

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)

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)

Call the OCIStmtFetch() function to obtain the result set of the 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 OceanBase 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:

/**********************************************************
* Copyright(C) 2014 - 2020 Alibaba Inc. All Rights Reserved.
*
*  Filename: ob_oci_test.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"
/*Declare handles.*/
OCIEnv *envhp;       /*Environment handle*/
OCISvcCtx *svchp;    /*Server environment handle*/
OCIServer *srvhp;    /*Server handle*/
OCISession *authp;   /*Session handle*/
OCIStmt *stmthp;     /*Statement handle*/
OCIDescribe *dschp;  /*Descriptor handle*/
OCIError *errhp;     /*Error handle*/
OCIDefine *defhp[3]; /*Definition handle*/
OCIBind *bidhp[4];   /*Binding handle*/
sb2 ind[3];          /*Indicator variable*/
/*Parameters for binding result sets 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, "172.30.xx.xx:2881");
  strcpy(strUserName, "s**@oracle");
  strcpy(strPassword, "******");
  /*Initialize the OCI application environment.*/
  OCIInitialize(OCI_DEFAULT, NULL, NULL, NULL, NULL);
  /*Initialize the environment handle.*/
  OCIEnvInit(&envhp, OCI_DEFAULT, 0, 0);
  /*Server environment handle*/
  OCIHandleAlloc(envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX, 0, 0);
  /*Server handle*/
  OCIHandleAlloc(envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER, 0, 0);
  /*Session handle*/
  OCIHandleAlloc(envhp, (dvoid **)&authp, OCI_HTYPE_SESSION, 0, 0);
  /*Error handle*/
  OCIHandleAlloc(envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, 0, 0);
  /*Descriptor handle*/
  OCIHandleAlloc(envhp, (dvoid **)&dschp, OCI_HTYPE_DESCRIBE, 0, 0);
  /*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. Values of the parameters are from the database connection string obtained in Step 1.

strcpy(strServerName, "172.30.xx.xx:2881");
strcpy(strUserName, "s**@oracle");
strcpy(strPassword, "******");

strServerName: The value of this parameter is obtained from the -h and -P parameters, in the format of IP:port. This parameter specifies the IP address and port for connecting to OceanBase Database, which are usually the IP address of an ODP and the port for database access.
strUserName: the username for connecting to a tenant, in the format of username@tenant name#cluster name. The value of this parameter is obtained from the -u parameter. In Oracle mode, the default username of the administrator is sys. The cluster name is not required when you directly connect to OceanBase Database, but is required when you connect to OceanBase Database through an ODP.
strPassword: the user password. The value of this parameter is obtained from the -p parameter.

Step 4: Run the application

After you edit the code, run the following command:

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

After the compilation is completed, execute the sample script. If the following results are returned, you have connected to OceanBase Database, and the sample script is correctly executed.

./test

PERSONID  NAME      email
1         obtest    t***@ob.com

More information

For more information about OBCI, see the OBCI documentation.

OceanBase

Customer Stories

Documentation

Build a C application