Connect C applications to OceanBase Database

This topic describes how to connect C applications to OceanBase Database.

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

• You have obtained the installation packages of OceanBase Call Interface (OBCI) and OceanBase Connector/C (also known as libobclient) from OceanBase Technical Support.

Install OceanBase Connector/C

On the CLI, run the following commands as the root user to install OBCI and LibOBClient:

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.

Examples

Steps of connecting C applications to OBServer of OceanBase Database in Oracle mode by using OBCI:

1. Initialize the OBCI environment and threads.

   /*Initialize the OBCI environment.*/
   OCIInitialize(OCI_DEFAULT, NULL, NULL, NULL, NULL)
   
   /*Initialize the OCI process environment.*/
   OCIEnvInit(&envhp, OCI_DEFAULT, 0, 0)
   

2. Allocate necessary handles and data structures.

   /*Allocate and initialize an OCI 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)
   

3. 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)
   

4. Exchange data with the OBServer by using SQL statements, and then perform data processing. Steps of executing an SQL statement in the 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() 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)
      

   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 the query.

      OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)
      

5. 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)
   

6. 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;       /*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 binding handle.*/
sb2 ind[3];          /*The indicator variable.*/
/*Bind parameters of the select result set.*/
text szpersonid[9]; /*Stores the personid column.*/
text szsex[2];      /*Stores the sex column.*/
text szname[51];    /*Stores the name column.*/
text szemail[51];   /*Stores the mail column.*/
text szphone[26];   /*Stores the phone 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, "***1**");
  /*Initialize the OCI application environment.*/
  OCIInitialize(OCI_DEFAULT, NULL, NULL, NULL, NULL);
  /*Initialize the OCI process environment.*/
  OCIEnvInit(&envhp, OCI_DEFAULT, 0, 0);
  /*Allocate and initialize an OCI 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.*/
  /************************************************************************/
  /*Query the person table.*/
  /************************************************************************/
  strcpy(sql, "select personid ,name,phone from person;");
  /*Prepare an SQL statement.*/
  OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);
  /*Bind the output columns.*/
  OCIDefineByPos(stmthp, &defhp[0], errhp, 1, (ub1 *)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 *)szphone,
                 sizeof(szphone), 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", "PHONE");
  while ((OCIStmtFetch(stmthp,
                       errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) != OCI_NO_DATA)
  {
    printf("%-10s", szpersonid);
    printf("%-10s", szname);
    printf("%-10s\n", szphone);
  }

  /*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;
}

Specify the following database connection information based on the actual condition.

• strServerName: ip:port. Specifies the IP address and port for connecting to OceanBase Database, which is usually the IP address of an OBProxy.

• strUserName: specifies the username for connecting to the tenant, in the username@tenant name format. In Oracle mode, the default username of the administrator is SYS.

  Note

  In a private cloud or standalone deployment scenario, the format of the username is String username = "username@tenant#cluster".

• strPassword: the password of the user.

After you edit the code, you can run the following commands to compile and run the code. If the query results are returned correctly, you have connected to the database.

// 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
// Run the code.
./test

For more information about how to install and use OBCI, see OBCI.