Development example

/**********************************************************
* Copyright(C) 2014 - 2022 Alibaba Inc. All Rights Reserved.
*
*  Filename: example.c
*  Description: ----
*  Create: 2020-07-07 10:14:59
*  Last Modified: 2022-05-05 14:32: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 environment handle. */
OCIServer *srvhp;    /* Server handle. */
OCISession *authp;   /* Session handle. */
OCIStmt *stmthp;     /* Statement handle. */
OCIDescribe *dschp;  /* Describe handle. */
OCIError *errhp;     /* Error handle. */
OCIBind *bidhp[4];   /* Bind handle. */
OCIDefine *defhp[3]; /* Define handle. */
sb2 ind[3];          /* Indicator variable. */
/* Parameters for binding the result set of a SELECT statement. */
int szpersonid;     /* Stores the value of the personid column. */
text szsex[2];      /* Stores the value of the sex column. */
text szname[10];    /* Stores the value of the name column. */
text szemail[10];   /* Stores the value of the email column. */
char sql[256];      /* Stores the executed SQL statement. */
static text* SQL_DROP_TB   = (text*)"drop table person";
static text* SQL_CREATE_TB = (text*)"create table person(personid number, sex varchar2(256), name varchar2(256), email varchar2(256), primary key(personid))";

void checkerr(OCIError *errhp, sword status, const char* filename, int line) {
  text errbuf[512];
  sb4 errcode = 0;
  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                       errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("%s:%d Error - OCI_SUCCESS_WITH_INFO now get is %d:%.*s\n", filename, line, errcode, 512, errbuf);
    break;
  case OCI_NEED_DATA:
    (void) printf("%s:%d Error - OCI_NEED_DATA\n", filename, line);
    break;
  case OCI_NO_DATA:
    (void) printf("%s:%d Error - OCI_NODATA\n", filename, line);
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                       errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("%s:%d Error - now get is %d:%.*s\n", filename, line, errcode, 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("%s:%d Error - OCI_INVALID_HANDLE\n", filename, line);
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("%s:%d Error - OCI_STILL_EXECUTE\n", filename, line);
    break;
  case OCI_CONTINUE:
    (void) printf("%s:%d Error - OCI_CONTINUE\n", filename, line);
    break;
  default:
    break;
  }
}
#define OCI_CHECK_RET(errhp, function) \
  checkerr(errhp, function, __FILE__, __LINE__)

/************************************************************************/
/* Query the person table. */
/************************************************************************/
void query_tables() {
    sword status = OCI_SUCCESS;
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "select personid, name, email from person");
    /* Prepare the SQL statement. */
    OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT));
    /* Bind the output columns. */
    OCI_CHECK_RET(errhp, OCIDefineByPos(stmthp, &defhp[0], errhp, 1, &szpersonid,
                   sizeof(szpersonid), SQLT_INT, &ind[0], 0, 0, OCI_DEFAULT));
    OCI_CHECK_RET(errhp, OCIDefineByPos(stmthp, &defhp[1], errhp, 2, (ub1 *)szname,
                   sizeof(szname), SQLT_STR, &ind[1], 0, 0, OCI_DEFAULT));
    OCI_CHECK_RET(errhp, OCIDefineByPos(stmthp, &defhp[2], errhp, 3, (ub1 *)szemail,
                   sizeof(szemail), SQLT_STR, &ind[2], 0, 0, OCI_DEFAULT));
    /* Execute the SQL statement. */
    OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)0, 0, NULL, NULL,
                   OCI_DEFAULT));
    printf("%-10s%-10s%-10s\n", "PERSONID", "NAME", "EMAIL");
    while ((status = OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) == OCI_SUCCESS) {
        printf("%-10d", szpersonid);
        printf("%-10s", szname);
        printf("%-10s\n", szemail);
    }
    if (OCI_NO_DATA != status) {
        printf("error ! error ! error ! err=%d\n", status);
    } else {
        printf("finish fetch data\n");
    }
}

void insert_tables() {
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "insert into person values(:personid,:sex,:name,:email)");
    /* Prepare the SQL statement. */
    OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,
                   OCI_DEFAULT));
    /* Bind the input columns. */
    OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[0], errhp, (const OraText*)":personid", 9, &szpersonid,
                  sizeof(szpersonid), SQLT_INT, NULL, NULL, NULL, 0, NULL, 0));
    OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[1], errhp, (const OraText*)":sex", 4, szsex,
                  sizeof(szsex), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));
    OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[2], errhp, (const OraText*)":name", 5, szname,
                  sizeof(szname), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));
    OCI_CHECK_RET(errhp, 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(szsex, 0, sizeof(szsex));
    strcpy((char*)szsex, "M");
    memset(szname, 0, sizeof(szname));
    strcpy((char*)szname, "obtest");
    memset(szemail, 0, sizeof(szemail));
    strcpy((char*)szemail, "t@ob.com");

    /* Execute the SQL statement. */
    OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DEFAULT));
    /* Commit to the database. */
    OCI_CHECK_RET(errhp, OCITransCommit(svchp, errhp, OCI_DEFAULT));
    printf("finish insert tables\n");
}

void insert_tables_default() {
  int szpersonid[5] = {11,12,13,14,15};
  text szsex[5][2]={"M", "W", "M", "W", "M"};
  text szname[5][10] = {"obtest1", "obtest2", "obtest3", "obtest4", "obtest5"};
  text szemail[5][10] = {"1@ob.com","2@ob.com","3@ob.com","4@ob.com","5@ob.com"};

  memset(sql, 0, sizeof(sql));
  strcpy(sql, "insert into person values(:personid,:sex,:name,:email)");
  /* Prepare the SQL statement. */
  OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,
                 OCI_DEFAULT));
  /* Bind the input columns. */
  OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[0], errhp, (const OraText*)":personid", 9, &szpersonid,
                sizeof(szpersonid[0]), SQLT_INT, NULL, NULL, NULL, 0, NULL, 0));
  OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[1], errhp, (const OraText*)":sex", 4, szsex,
                sizeof(szsex[0]), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));
  OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[2], errhp, (const OraText*)":name", 5, szname,
                sizeof(szname[0]), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));
  OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[3], errhp, (const OraText*)":email", 6, szemail,
                sizeof(szemail[0]), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));

  /* Execute the SQL statement. */
  OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)5, (ub4)0, (CONST OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DEFAULT));
  /* Commit to the database. */
  OCI_CHECK_RET(errhp, OCITransCommit(svchp, errhp, OCI_DEFAULT));
  printf("finish insert_tables_arraybind_default\n");
}
void insert_tables_batcherr() {
  sword status = 0;
  int szpersonid[5] = {21,22,21,21,21};
  text szsex[5][2]={"M", "W", "M", "W", "M"};
  text szname[5][10] = {"obtest21", "obtest22", "obtest23", "obtest24", "obtest25"};
  text szemail[5][10] = {"21@ob.com","22@ob.com","23@ob.com","24@ob.com","25@ob.com"};

  memset(sql, 0, sizeof(sql));
  strcpy(sql, "insert into person values(:personid,:sex,:name,:email)");
  /* Prepare the SQL statement. */
  OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,
                 OCI_DEFAULT));
  /* Bind the input columns. */
  OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[0], errhp, (const OraText*)":personid", 9, &szpersonid,
                sizeof(szpersonid[0]), SQLT_INT, NULL, NULL, NULL, 0, NULL, 0));
  OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[1], errhp, (const OraText*)":sex", 4, szsex,
                sizeof(szsex[0]), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));
  OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[2], errhp, (const OraText*)":name", 5, szname,
                sizeof(szname[0]), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));
  OCI_CHECK_RET(errhp, OCIBindByName(stmthp, &bidhp[3], errhp, (const OraText*)":email", 6, szemail,
                sizeof(szemail[0]), SQLT_STR, NULL, NULL, NULL, 0, NULL, 0));

  /* Execute the SQL statement. */
  status = OCIStmtExecute(svchp, stmthp, errhp, (ub4)5, (ub4)0, (CONST OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_BATCH_ERRORS);
  if (status != OCI_SUCCESS) {
    OCIError *errhp2;
    OCIError *errhndl;
    long num_errs = 0;

    OCIHandleAlloc((void *)envhp, (void **)&errhp2, (ub4) OCI_HTYPE_ERROR, 0, 0);
    OCI_CHECK_RET(errhp, OCIAttrGet(stmthp, OCI_HTYPE_STMT, &num_errs, 0, OCI_ATTR_NUM_DML_ERRORS, errhp2));
    for (unsigned int i = 0;  i < num_errs; ++i) {
      ub4 row_offset = i;
      text errbuf[512];
      sb4 errcode = 0;
      OCIParamGet(errhp, OCI_HTYPE_ERROR, errhp2, (void **)&errhndl, i);
      OCIAttrGet(errhndl, OCI_HTYPE_ERROR, &row_offset, 0, OCI_ATTR_DML_ROW_OFFSET, errhp2);
      OCIErrorGet((dvoid *)errhndl, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
      printf("batch error, offset=%d, (%d):%s\n", row_offset, i , errbuf);
    }
  }

  /* Commit to the database. */
  OCI_CHECK_RET(errhp, OCITransCommit(svchp, errhp, OCI_DEFAULT));
  printf("finish insert_tables_arraybind_batcherr\n");
}

void update_tables() {
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "update person set sex='M',name='test',email='test@mail' WHERE personid=1");
    /* Prepare the SQL statement. */
    OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,
                   OCI_DEFAULT));
    /* Execute the SQL statement. */
    OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DEFAULT));
    /* Commit to the database. */
    OCI_CHECK_RET(errhp, OCITransCommit(svchp, errhp, OCI_DEFAULT));
}

void delete_tables() {
    memset(sql, 0, sizeof(sql));
    strcpy(sql, "delete from person WHERE personid = :personid");
    /* Prepare the SQL statement. */
    OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text *)sql, strlen(sql), OCI_NTV_SYNTAX,
                   OCI_DEFAULT));
    /* Bind the input parameters. */
    szpersonid = 1;
    OCI_CHECK_RET(errhp, OCIBindByPos(stmthp, &bidhp[0], errhp, 1, &szpersonid,
                 sizeof(szpersonid), SQLT_INT, NULL, NULL, NULL, 0, NULL, 0));
    /* Execute the SQL statement. */
    OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0, (CONST OCISnapshot *)0, (OCISnapshot *)0, (ub4)OCI_DEFAULT));
    /* Commit to the database. */
    OCI_CHECK_RET(errhp, OCITransCommit(svchp, errhp, OCI_DEFAULT));
}

int main(int argc, char *argv[]) {
    char strServerName[50];
    char strUserName[50];
    char strPassword[50];
    /* Set the server name, username, and password based on your actual settings. */
    strcpy(strServerName, "xxx.xxx.xxx.xxx:port/test");
    strcpy(strUserName, "test");
    strcpy(strPassword, "test");
    /* Initialize the OCI application environment. */
    OCI_CHECK_RET(errhp, OCIEnvCreate(&envhp, OCI_DEFAULT | OCI_THREADED, (void *)0, 0, 0, 0, (size_t)0, NULL));
    /* Allocate handles. */
    OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX, 0, 0));
    /* Server environment handle. */
    OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER, 0, 0));
    /* Server handle. */
    OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&authp, OCI_HTYPE_SESSION, 0, 0));
    /* Session handle. */
    OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR, 0, 0));
    /* Error handle. */
    OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&dschp, OCI_HTYPE_DESCRIBE, 0, 0));
    /* Describe handle. */
    /* Connect to the server. */
    OCI_CHECK_RET(errhp, OCIServerAttach(srvhp, errhp, (text *)strServerName,
                    (sb4)strlen(strServerName), OCI_DEFAULT));
    /* Set the username and password. */
    OCI_CHECK_RET(errhp, OCIAttrSet(authp, OCI_HTYPE_SESSION, (text *)strUserName,
               (ub4)strlen(strUserName), OCI_ATTR_USERNAME, errhp));
    OCI_CHECK_RET(errhp, OCIAttrSet(authp, OCI_HTYPE_SESSION, (text *)strPassword,
               (ub4)strlen(strPassword), OCI_ATTR_PASSWORD, errhp));
    /* Set the attributes of the server environment handle. */
    OCI_CHECK_RET(errhp, OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
               (dvoid *)srvhp, (ub4)0, OCI_ATTR_SERVER, errhp));
    OCI_CHECK_RET(errhp, OCIAttrSet(svchp, OCI_HTYPE_SVCCTX, (dvoid *)authp,
               0, OCI_ATTR_SESSION, errhp));
    /* Create and start a user session. */
    OCI_CHECK_RET(errhp, OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT));
    OCI_CHECK_RET(errhp, OCIHandleAlloc(envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT, 0, 0));
    /* Statement handle. */

    /************************************************************************/
    /* Create the person table. */
    /************************************************************************/
    OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, (text*)SQL_DROP_TB, strlen((char *)SQL_DROP_TB), OCI_NTV_SYNTAX, OCI_DEFAULT));
    OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, OCI_COMMIT_ON_SUCCESS));
    OCI_CHECK_RET(errhp, OCIStmtPrepare(stmthp, errhp, SQL_CREATE_TB, strlen((char *)SQL_CREATE_TB), OCI_NTV_SYNTAX, OCI_DEFAULT));
    OCI_CHECK_RET(errhp, OCIStmtExecute(svchp, stmthp, errhp, 1, 0, 0, 0, OCI_DEFAULT));

    /************************************************************************/
    /* Query the person table. */
    /************************************************************************/
    query_tables();

    /************************************************************************/
    /* Insert a row into the person table. */
    /************************************************************************/
    insert_tables();
    insert_tables_default();
    insert_tables_batcherr();
    query_tables();

    /************************************************************************/
    /* Update the person table. */
    /************************************************************************/
    update_tables();
    query_tables();

    /************************************************************************/
    /* Delete the ID field from the person table. First, the record must exist in the database. */
    /************************************************************************/
    delete_tables();
    query_tables();

    /************************************************************************/
    /* End and release resources. */
    /************************************************************************/
    // End the session.
    OCI_CHECK_RET(errhp, OCISessionEnd(svchp, errhp, authp, (ub4)0));
    // Disconnect from the database.
    OCI_CHECK_RET(errhp, OCIServerDetach(srvhp, errhp, OCI_DEFAULT));
    // Release the OCI handles.
    OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)dschp, OCI_HTYPE_DESCRIBE));
    OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)stmthp, OCI_HTYPE_STMT));
    OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)errhp, OCI_HTYPE_ERROR));
    OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)authp, OCI_HTYPE_SESSION));
    OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)svchp, OCI_HTYPE_SVCCTX));
    OCI_CHECK_RET(errhp, OCIHandleFree((dvoid *)srvhp, OCI_HTYPE_SERVER));
    return 0;
}