Create a C application

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

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

/**********************************************************
* 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 variable.*/
/*Bind the result set of the SELECT statement.*/
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 statement.*/

int main(int argc, char *argv[])
{
  char strServerName[50];
  char strUserName[50];
  char strPassword[50];
  /*Set the server, 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);
  /*Server context 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 server context handle attribute.*/
  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);
  /*Statement handle.*/

  /************************************************************************/
  /*Create the person table.*/
  /************************************************************************/
  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 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 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);
  //Release 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;
}

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

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

./test

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