#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

static text *username = (text *) "STUDENT";
static text *password = (text *) "LEARN";

/* Define SQL statements to be used in the program. */

static text *course_count = (text *)"SELECT COUNT(*) FROM COURSE";
static text *course_ten = (text *)"SELECT course_no, description, cost FROM course WHERE course_no = 10";
static text *course_low = (text *)"SELECT course_no, description, cost FROM course WHERE course_no < 100";

static OCIEnv *envhp;   // The Env handle is the most basic for Oracle
static OCIError *errhp; // And we use the error handle to retrieve any err info

/* Function prototypes for locally defined functions but with comments */

static void checkerr(/*_ OCIError *errhp, sword status _*/);
static void cleanup(/*_ void _*/);
static void myfflush(/*_ void _*/);
int main(/*_ int argc, char *argv[] _*/);

static sword status;

int main(argc, argv)
int argc;
char *argv[];
{
  sword    ccount;
  sword    cno;
  sword    ccost;
  sb1      descr_buf[51];

  OCISession *authp = (OCISession *) 0;
  OCIServer *srvhp;
  OCISvcCtx *svchp;
  OCIStmt   *stmthp;
  OCIDefine *defnp = (OCIDefine *) 0;
  OCIDefine *defnp1 = (OCIDefine *) 0;
  OCIDefine *defnp2 = (OCIDefine *) 0;
  OCIDefine *defnp3 = (OCIDefine *) 0;

  (void) OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,
                       (dvoid * (*)(dvoid *, size_t)) 0,
                       (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                       (void (*)(dvoid *, dvoid *)) 0 );

  (void) OCIEnvInit( (OCIEnv **) &envhp, OCI_DEFAULT, (size_t) 0,
                     (dvoid **) 0 );

  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,
                   (size_t) 0, (dvoid **) 0);

  /* server contexts */
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER,
                   (size_t) 0, (dvoid **) 0);

  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX,
                   (size_t) 0, (dvoid **) 0);

  (void) OCIServerAttach( srvhp, errhp, (text *)"", strlen(""), 0);

  /* set attribute server context in the service context */
  (void) OCIAttrSet( (dvoid *) svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp,
                     (ub4) 0, OCI_ATTR_SERVER, (OCIError *) errhp);

  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **)&authp,
                        (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0);

  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) username, (ub4) strlen((char *)username),
                 (ub4) OCI_ATTR_USERNAME, errhp);

  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) password, (ub4) strlen((char *)password),
                 (ub4) OCI_ATTR_PASSWORD, errhp);

  checkerr(errhp, OCISessionBegin ( svchp,  errhp, authp, OCI_CRED_RDBMS,
                          (ub4) OCI_DEFAULT));

  (void) OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                   (dvoid *) authp, (ub4) 0,
                   (ub4) OCI_ATTR_SESSION, errhp);

  /* Prepare an SQL statement */

  // We begin by allocating a handle for our SQL statement(s)

  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  // Once we have the handle, the next step is always to prepare
  // the statement

  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, course_count,
                                (ub4) strlen((char *) course_count),
                                (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
  /* bind the input variable */

  checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 1, (dvoid *) &ccount,
                   (sword) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0,
                   (ub2 *)0, OCI_DEFAULT));

  /* execute and fetch */

  // Be sure and check the return status and account for any possibilities
  // like no tuples returned, a single tuple returned, or multiple tuples
  // returned.

  if (status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
               (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
  {
    if (status == OCI_NO_DATA) {
      fprintf(stderr, "No data on select count(*) from course\n");
    } else
    {
      checkerr(errhp, status);
      cleanup();
      return OCI_ERROR;
    }
  }
  printf("Course count: %d\n", ccount);

  // The next example will execute an SQL statement with more than one
  // projected attributes, but still with only a single tuple returned.

  // We begin by preparing the statement.  Note that we can reuse the
  // statement handle.

  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, course_ten,
                                (ub4) strlen((char *) course_ten),
                                (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

  /* bind the input variables */

  checkerr(errhp, OCIDefineByPos(stmthp, &defnp1, errhp, 1, (dvoid *) &cno,
                   (sword) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0,
                   (ub2 *)0, OCI_DEFAULT));

  checkerr(errhp, OCIDefineByPos(stmthp, &defnp2, errhp, 2, (dvoid *) descr_buf,
                   (sword) sizeof(descr_buf), SQLT_STR, (dvoid *) 0, (ub2 *)0,
                   (ub2 *)0, OCI_DEFAULT));

  checkerr(errhp, OCIDefineByPos(stmthp, &defnp3, errhp, 3, (dvoid *) &ccost,
                   (sword) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0,
                   (ub2 *)0, OCI_DEFAULT));

  if (status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
               (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
  {
    if (status == OCI_NO_DATA) {
      fprintf(stderr, "No data on select from course\n");
    } else
    {
      checkerr(errhp, status);
      cleanup();
      return OCI_ERROR;
    }
  }
  printf("Course number, descr, cost: %d | %s | %d\n", cno, descr_buf, ccost);

  // Final example demonstrates fetching multiple tuples as the result
  // of an SQL query

  // We begin by preparing the statement.  Note that we can reuse the
  // statement handle.

  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, course_low,
                                (ub4) strlen((char *) course_low),
                                (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

  /* bind the input variables */

  checkerr(errhp, OCIDefineByPos(stmthp, &defnp1, errhp, 1, (dvoid *) &cno,
                   (sword) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0,
                   (ub2 *)0, OCI_DEFAULT));

  checkerr(errhp, OCIDefineByPos(stmthp, &defnp2, errhp, 2, (dvoid *) descr_buf,
                   (sword) sizeof(descr_buf), SQLT_STR, (dvoid *) 0, (ub2 *)0,
                   (ub2 *)0, OCI_DEFAULT));

  checkerr(errhp, OCIDefineByPos(stmthp, &defnp3, errhp, 3, (dvoid *) &ccost,
                   (sword) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0,
                   (ub2 *)0, OCI_DEFAULT));

  // Note on the stmt execution that we set the fourth parameter to zero
  // to defer any fetch until the loop below.

  if (status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0, (ub4) 0,
               (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
  {
    if (status == OCI_NO_DATA) {
      fprintf(stderr, "No data on select from course\n");
    } else
    {
      checkerr(errhp, status);
      cleanup();
      return OCI_ERROR;
    }
  }

  while ((status = OCIStmtFetch2(stmthp, errhp, (ub4) 1, (ub2) OCI_FETCH_NEXT,
				 (sb4) 0, OCI_DEFAULT)) == OCI_SUCCESS) {
    printf("Course number, descr, cost: %d | %s | %d\n", 
	   cno, descr_buf, ccost);
  }
  if (status == OCI_NO_DATA) {
    fprintf(stderr, "No data on select from course\n");
  } else
    {
      checkerr(errhp, status);
      cleanup();
      return OCI_ERROR;
    }
}


void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512];
  sb4 errcode = 0;

  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;
  case OCI_NEED_DATA:
    (void) printf("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*s\n", 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("Error - OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
    (void) printf("Error - OCI_CONTINUE\n");
    break;
  default:
    break;
  }
}


/*
 *  Exit program with an exit code.
 */
void cleanup()
{
  if (envhp)
    (void) OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV);
  return;
}


void myfflush()
{
  eb1 buf[50];

  fgets((char *) buf, 50, stdin);
}