This topic describes how to build a C application with OceanBase Database and OBCI.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Prerequisites
You have set up the basic database development environment.
You have a hardware environment that meets the following requirements:
Hardware platform: x86_64
Operating system: Linux distribution of CentOS/Redhat 7.2
Compiler: GCC 4.8
Contact the technical support team to obtain the RPM installation packages of OBCI and LibOBClient.
Notice
Pay attention to the following points regarding the prerequisites:
- Starting from OBCI V2.0.4, the
includefolder in the RPM package no longer contains theoci.handociap.hheader files. For business compilation, install the Oracle clntsh RPM first, and then use its header files. - OceanBase Database: V2.2.76 and later (V4.0 and later are recommended to experience all the features of the current version)
- OBProxy (ODP): V4.0 and later (recommended)
- libobclient: V2.1.1 and later
- Install the Oracle driver files
oracle-instantclient. Starting from OBCI V2.0.4, you also need to install the Oracle header files. - You have installed OBCI and deployed the GCC compiler in advance.
Step 1: Obtain a database connection string
Contact the deployment personnel or administrator of OceanBase Database to obtain the connection string, for example:
obclient -h100.88.xx.xx -usys@oracle -p****** -P2881
The database connection string contains parameters required for accessing the database. Before you create an application, you can log in to the database 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 ODP.
-u: the username for connecting to a tenant, in the format of username@tenant name#cluster name. The default username of the administrator in Oracle mode is
sys. The cluster name is not required when you directly connect to the database, but is required when you connect to the 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 the C-related driver
After you obtain the RPM package, run the following command as the root user in the command-line tool to install the OBCI driver:
rpm -ivh obci-<version>.x86_64.rpm
rpm -ivh libobclient-<version>.x86_64.rpm
By default, the program and files in the package are installed in the following paths:
The header files are installed in the
/u01/obclient/includepath.The library files are installed in the
/u01/obclient/libpath.
Step 3: Write an application
This topic describes how a C application interacts with an OBServer node in the Oracle mode of OceanBase Database. It uses a specific example to illustrate the basic approach.
Initialize the OBCI environment and the thread.
/*Initialize the OBCI program environment*/ OCIInitialize(OCI_DEFAULT, NULL, NULL, NULL, NULL) /*Initialize the environment handle*/ OCIEnvInit(&envhp, OCI_DEFAULT, 0, 0)Allocate necessary handles and data structures.
/*Service context 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) /*Describe handle*/ OCIHandleAlloc(envhp, (dvoid **)&dschp, OCI_HTYPE_DESCRIBE, 0, 0)Establish a connection to the 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 OceanBase Server through SQL statements, and then process the data. The execution steps of an SQL statement in an OBCI application are as follows:
Call the
OCIStmtPrepare()orOCIStmtPrepare2()function to prepare the SQL statement.OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,OCI_DEFAULT)Call the
OCIBindByPos()orOCIBindByName()function to bind the addresses of input variables to the placeholders in 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
OCIStmtExecute()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 output items in 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 retrieve the result set of a query.OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)
End the user session and disconnect from the database.
/*End the session*/ OCISessionEnd(svchp, errhp, authp, (ub4)0) /*Disconnect from the database*/ OCIServerDetach(srvhp, errhp, OCI_DEFAULT)Deallocate the handles that are allocated in the program.
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 content of the test.c file is as follows:
/**********************************************************
* 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; /* Service context handle. */
OCIServer *srvhp; /* Server handle. */
OCISession *authp; /* Session handle. */
OCIStmt *stmthp; /* Statement handle. */
OCIDescribe *dschp; /* Describe handle. */
OCIError *errhp; /* Error handle. */
OCIDefine *defhp[3]; /* Define handle. */
OCIBind *bidhp[4]; /* Bind handle. */
sb2 ind[3]; /* Indicator variables. */
/* Define parameters for binding the result set of a SELECT statement. */
int szpersonid; /* Store the data of the personid column. */
text szname[51]; /* Store the data of the name column. */
text szemail[51]; /* Store the data of the mail column. */
char sql[256]; /* Store the executed SQL statement. */
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);
/* Allocate handles. */
OCIHandleAlloc(envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX, 0, 0);
/* Allocate a server environment handle. */
OCIHandleAlloc(envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER, 0, 0);
/* Allocate a server handle. */
OCIHandleAlloc(envhp, (dvoid **)&authp, OCI_HTYPE_SESSION, 0, 0);
/* Allocate an error handle. */
OCIHandleAlloc(envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, 0, 0);
/* Allocate a describe handle. */
OCIHandleAlloc(envhp, (dvoid **)&dschp, OCI_HTYPE_DESCRIBE, 0, 0);
/* Allocate a statement handle. */
OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0);
/* Set the server environment handle attributes. */
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);
/* 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);
/* Allocate memory for the define handle. */
OCIHandleAlloc(envhp, (dvoid **)&defhp[0], OCI_HTYPE_DEFINE, 0, 0);
/* Allocate memory for the bind handle. */
OCIHandleAlloc(envhp, (dvoid **)&bidhp[0], OCI_HTYPE_BIND, 0, 0);
/* Allocate memory for the bind handle. */
OCIHandleAlloc(envhp, (dvoid **)&bidhp[2], OCI_HTYPE_BIND, 0, 0);
/* Allocate memory for the bind handle. */
OCIHandleAlloc(envhp, (dvoid **)&bidhp[3], OCI_HTYPE_BIND, 0, 0);
/* Initialize the indicator variables. */
memset(ind, 0, sizeof(ind));
/* Define parameters for binding the result set of a SELECT statement. */
memset(szname, 0, sizeof(szname));
memset(szemail, 0, sizeof(szemail));
/* Define the SQL statement. */
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 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 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 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);
// Free the 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 IP address and port number for connecting to OceanBase Database, which is usually the IP address of an ODP and the port number for accessing it. The value of this parameter is in the format of
IP:port.strUserName: the username for connecting to a tenant, in the format of username@tenant name#cluster name. The default value for the username of the administrator in Oracle mode is
sys. The cluster name is not required when you directly connect to the database, but is required when you connect to the database through an ODP.strPassword: the user password.
Step 4: Run the application
After you edit the code, run the following command:
// Compile
gcc test.c -I/u01/obclient/include /u01/obclient/lib/libobci.a -L/usr/local/lib64 -lstdc++ -lpthread -ldl -lm -g -o test
If the execution result shown in the following figure is returned after the compilation, the database is connected and the statement is executed successfully.
./test
PERSONID NAME email
1 obtest t***@ob.com
More information
For more information about OBCI, see the OceanBase C API chapter on the official website.