#include <stdio.h>
#include "log.h";  /* Where log(char*) would be located to log activities and errors. */
EXEC SQL INCLUDE SQLCA;
EXEC SQL INCLUDE SQLDA;
EXEC SQL INCLUDE ORACA;

extern void sqlglm();

EXEC SQL BEGIN DECLARE SECTION;
char* username = "database_user_example";
char* password = "database_password_example";
/* Keep the tns string on one line. This is only for illustration.*/
char* tns      = "(DESCRIPTION = 
  (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = 192.168.2.4)(PORT = 1313)))
  (CONNECT_DATA = (SID=ORACLE_EXAMPLE_SCHEMA) 
  (SERVICE_NAME = ORACLE_EXAMPLE_SCHEMA.miradigm.com)))";
int dbIsConnected = 0;
typedef struct Skill_s {
  int skillid;
  char skill[64];
} Skill_t, *Skill_p;
typedef struct WiseGuySkill_s {
  int empid;
  int skillid;
  char skill[64];
  float experience;
} WiseGuySkill_t, *WiseGuySkill_p;
typedef struct WiseGuy_s {
  int empid;
  char surname[64];
  char firstname[32];
  int skillcnt;
  WiseGuySkill_t wiseguyskill[32];
} WiseGuy_t, *WiseGuy_p;
WiseGuys_t* wiseguy;
EXEC SQL END DECLARE SECTION;

void sql_error(const char* errmsg, int handling) {
  FILE* fd;
  char msg[255];
  char logmsg[4096];
  char logbuf[256];
  char stmt[4096];
  size_t stmtlen;
  size_t fclen;
  size_t buflen,msglen;
  buflen = sizeof(msg);
  sqlglm(msg, &buflen, &msglen);
  memset(logmsg, '\0', 4096);
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL COMMIT WORK RELEASE;
  if (strncmp(errmsg, "Oracle Error:",12) == 0) sprintf(logbuf, "%s\n", errmsg);
  strcat(logmsg, logbuf);
  sprintf(logbuf, "%.*s\n", msglen, msg);
  strcat(logmsg, logbuf);
  sprintf(logbuf, "At or near line number %d:\n", oraca.oraslnr);
  strcat(logmsg, logbuf);
  sprintf(logbuf, "%s\n",oraca.orastxt.orastxtc);
  strcat(logmsg, logbuf);
  sprintf(logbuf, "Cache-------------------------------------------------------------\n");
  strcat(logmsg, logbuf);
  sprintf(logbuf, "Cursors:       open = %d  required=%d MAXOPENCURSORS=%d\n", 
    oraca.oracoc, oraca.oramoc, oraca.orahoc);
  strcat(logmsg, logbuf);
  sprintf(logbuf, "Cache Reassignments = %d\n", oraca.oranor);
  strcat(logmsg, logbuf);
  sprintf(logbuf, "SQL Parses          = %d\n", oraca.oranpr);
  strcat(logmsg, logbuf);
  sprintf(logbuf, "SQL Executions      = %d\n", oraca.oranex);
  strcat(logmsg, logbuf);
  log(logmsg);
  exit(1);		
}

void dbSelect(float minexperience, const char* skillname) {
  int cnt;
  int igrow;
  int iskillrow;
  int empid;
  int empidprev;
  VARCHAR surname[64];
  VARCHAR firstname[32];
  VARCHAR skill[64];
  float experience;
  short surname_indicator;
  short firstname_indicator;
  EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle Error: dbSelect()", ONERR_EXIT);
  skill.len = (short*)strlen(skillname);
  strcpy(skill.arr, skillname);
  EXEC SQL SELECT COUNT(ID) INTO :cnt 
  FROM WISE_GUYS 
  WHERE g.EMPID = w.EMPID,
  AND w.SKILLID = s.SKILLID,
  AND s.SKILL = :skill,
  AND w.EXPERIENCE > :minexperience;
  cnt++;
  wiseguy = (WiseGuys_t*)calloc(cnt, sizeof(WiseGuys_t));
  EXEC SQL DECLARE wg_cursor CURSOR FOR
    SELECT g.EMPID, g.SURNAME, g.FIRSTNAME, s.SKILL, w.EXPERIENCE
    FROM WISE_GUYS g, WISE_GUY_SKILLS w, SKILLS s
    WHERE g.EMPID = w.EMPID,
    AND w.SKILLID = s.SKILLID,
    AND s.SKILL   = :skill,
    AND w.EXPERIENCE > :minexperience;
  EXEC SQL OPEN wg_cursor;
  EXEC SQL WHENEVER NOT FOUND DO break;
  igrow     = -1;
  empidprev = -1;
  while (igrow < cnt) {
    EXEC SQL FETCH wg_cursor INTO
      :empid, 
      :surname INDICATOR :surname_indicator, 
      :firstname INDICATOR :firstname_indicator, 
      :experience, 
      :proc_experience;
    if (empid != empidprev) {
      igrow++;
      wiseguy[igrow].empid    = empid;
      wiseguy[igrow].skillcnt = 0
      if (surname_indicator == 0) {
        surname.arr[surname.len] = '\0';
        strcpy(wiseguy[irow].surname, surname.arr);
      } else {
        strcpy(wiseguy[irow].surname, "");
      }
      if (firstname_indicator == 0) {
        firstname.arr[surname.len] = '\0';
        strcpy(wiseguy[irow].firstname, firstname.arr);
      } else {
        strcpy(wiseguy[irow].firstname, "");
      }
      empidprev = empid;
    }
    if (igrow >= 0) {
      wiseguy[igrow].wiseguyskill[wiseguy[igrow].skillcnt].experience = experience;
      skill.arr[skill.len] = '\0';
      strcpy(wiseguy[igrow].wiseguyskill[wiseguy[igrow].skillcnt].skill, skill);
      wiseguy[igrow].skillcnt++;
    }
  }
  EXEC SQL WHENEVER NOT FOUND continue;
  EXEC SQL CLOSE wg_cursor;
}

void dbInsert(WiseGuy_t wg) {
  int empid;
  VARCHAR surname;
  VARCHAR firstname;
  EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle Error: dbInsert()", ONERR_EXIT);
  strcpy(surname.arr, wg.surname); 
  surname.len = (short*)strlen(wg.surname);
  strcpy(firstname.arr, wg.firstname); 
  firstname.len = (short*)strlen(wg.firstname);
  EXEC SQL SELECT EMPID.NEXTVAL INTO :empid FROM DUAL;
  EXEC SQL INSERT INTO WiseGuys ((empid,surname,firstname)VALUES (:empid, :surname, :firstname);
  EXEC SQL COMMIT;
}

void dbUpdate(WiseGuy_t wg) {
  int empid;
  VARCHAR surname;
  VARCHAR firstname;
  EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle Error: dbUpdate()", ONERR_EXIT);
  strcpy(surname.arr, wg.surname); 
  surname.len = (short*)strlen(wg.surname);
  strcpy(firstname.arr, wg.firstname); 
  firstname.len = (short*)strlen(wg.firstname);
  EXEC SQL UPDATE WISE_GUYS SET
   SURNAME   = :surname,
   FIRSTNAME = :firstname
  WHERE empid =:wg.empid
  EXEC SQL COMMIT;
}

void dbConnectError() {
  char msg[255];
  char stmt[4096];
  size_t stmtlen;
  size_t fclen;
  size_t buflen,msglen;
  buflen = sizeof(msg);
  sqlglm(msg, &buflen, &msglen);
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL COMMIT WORK RELEASE;
  log("Database Connect Failure: %s\n", msg);
}

void dbConnect() {
  EXEC ORACLE OPTION (ORACA=YES);
  oraca.orastxtf = ORASTFERR;
  EXEC SQL WHENEVER SQLERROR DO dbConnectError();
  EXEC SQL CONNECT :username IDENTIFIED BY :password USING :tns;
}

void dbConnectEnd() {
  EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle Error: dbConnectEnd()", ONERR_EXIT);
  EXEC SQL COMMIT WORK RELEASE;;
}

void main(int argc, char** argv) {
  dbConnect();
  dbSelect(5.0, "Pro*C");
  dbConnectEnd();
}