/*---------------------------------------------------------------------------
  FileName : proc2.pc
  Function : connect to scott/tiger and get emp datas
             use struct host_variable,struct indicator
SQL> desc emp;
 name                                      NULL?    type
 ----------------------------------------- -------- -------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)
  -------------------------------------------------------------------------*/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>

#define DEBUG
#ifdef DEBUG
#define prt_stt(stt) printf("%s]  %d rows   pre_rows=%d   ecode=%d\n",\
                             stt,rows,pre_rows,sqlca.sqlcode)
#else
#define prt_stt(stt)
#endif

#define USER_NAME     "SCOTT"
#define USER_PASS     "TIGER"
#define MAX_DATA        4
#define SZ_MSG        512
#define PWD_LEN        11 

typedef struct emp_info { 
   char       emp_name[PWD_LEN]; 
   float      salary; 
   float      commission; 
} EMP_INFO; 
#define SZ_EMPINFO  (sizeof(EMP_INFO))

typedef struct emp_info_ind { 
   short      ind_enm,
              ind_sal,
              ind_com;
} EMP_INFO_IND;

EXEC SQL BEGIN DECLARE SECTION;
   char           username[PWD_LEN];
   char           password[PWD_LEN];
   EMP_INFO       emp_info1;
   EMP_INFO       emp_info[MAX_DATA];
   EMP_INFO_IND   ind_emp_info1;
   EMP_INFO_IND   ind_emp_info[MAX_DATA];
EXEC SQL END DECLARE SECTION;

EXEC SQL DECLARE salespeople CURSOR FOR
     SELECT ENAME, SAL, COMM FROM EMP WHERE JOB LIKE 'SALES%';
EXEC SQL DECLARE all CURSOR FOR
     SELECT ENAME, SAL, COMM FROM EMP ;

void sql_error(char *msg) { 
   char err_msg[SZ_MSG];
   size_t buf_len=SZ_MSG, msg_len;

   EXEC SQL WHENEVER SQLERROR CONTINUE;

   printf("\n%s\n", msg);

/* Call sqlglm() to get the complete text of the error message.  */
   sqlglm(err_msg, &buf_len, &msg_len);
   printf("%.*s\n", msg_len, err_msg);

   EXEC SQL ROLLBACK RELEASE;
   exit(EXIT_FAILURE);
} 

int main(int argc,char *argv[]) { 
   connect();
   get_1rec();
   get_Nrec(); 
   get_1rec_all();
 
/* Close to transaction    */
   EXEC SQL COMMIT WORK RELEASE; 
   exit(EXIT_SUCCESS); 
} 

/*---------------------------------------------------------
   Function : connect to oacle database
  ---------------------------------------------------------*/
int connect() {
   strcpy(username, USER_NAME);
   strcpy(password, USER_PASS);

   EXEC SQL WHENEVER SQLERROR DO sql_error("ERROR in connect : ");

   EXEC SQL CONNECT :username IDENTIFIED BY :password;
   printf("\nConnected to ORACLE as user: %s\n\n", username);
}

/*--------------------------------------------------------------
   Function : get 1 record from database with host structure
  --------------------------------------------------------------*/
int get_1rec() {

   EXEC SQL WHENEVER SQLERROR DO sql_error("ERROR in get_1rec : ");
   EXEC SQL OPEN salespeople;

/* Get ready to print results. */
   printf("The company's sales people are followings. [get 1]\n\n");
   prt_subttl("SalesPerson");

/* Loop, fetching all salesperson's statistics.
 * Cause the program to break the loop when no more
 * data can be retrieved on the cursor.
 */
   EXEC SQL WHENEVER NOT FOUND DO break;

   for (;;) {
      EXEC SQL FETCH salespeople INTO :emp_info1 :ind_emp_info1;
      prt_data(&emp_info1,&ind_emp_info1,1);
   }
   printf("\n");

   EXEC SQL CLOSE salespeople;
}

/*---------------------------------------------------------
   Function : get N records from database with host array
  ---------------------------------------------------------*/
int get_Nrec() {
   int           i,rows,pre_rows=0;

   EXEC SQL WHENEVER SQLERROR DO sql_error("ERROR in get_Nrec : ");
   EXEC SQL OPEN salespeople;

/* Get ready to print results. */
   printf("The company's sales people are followings. [get %d]\n\n",MAX_DATA);
   prt_subttl("SalesPerson");

/* Loop, fetching all salesperson's statistics.
 * Cause the program to break the loop when no more
 * data can be retrieved on the cursor.
 */
   EXEC SQL WHENEVER NOT FOUND DO break;

   for (;;) {
      EXEC SQL FETCH salespeople INTO :emp_info :ind_emp_info;
      rows = sqlca.sqlerrd[2] - pre_rows;
      prt_stt("in");
      prt_data(emp_info,ind_emp_info,rows);
      pre_rows = sqlca.sqlerrd[2];
   }
   rows = sqlca.sqlerrd[2] - pre_rows;
   prt_stt("out");
   prt_data(emp_info,ind_emp_info,rows);
   printf("\n");

   EXEC SQL CLOSE salespeople;
}

/*--------------------------------------------------------------------------
   Function : get 1 record from database with host variables and indicator
  --------------------------------------------------------------------------*/
int get_1rec_all() {

   EXEC SQL WHENEVER SQLERROR DO sql_error("ERROR in get_1rec_all : ");
   EXEC SQL OPEN all;

/* Get row to print results. */
   printf("The company's people are followings. [get 1]\n\n");
   prt_subttl("Name       ");

/* Loop, fetching all salesperson's statistics.
 * Cause the program to break the loop when no more
 * data can be retrieved on the cursor.
 */
   EXEC SQL WHENEVER NOT FOUND DO break;

   for (;;) {
      EXEC SQL FETCH all INTO :emp_info1 :ind_emp_info1;
      prt_data(&emp_info1,&ind_emp_info1,1);
   }
   printf("\n");

   EXEC SQL CLOSE all;
}

/*---------------------------------------------------------
   Function : print records
  ---------------------------------------------------------*/
int prt_data(EMP_INFO *emp,EMP_INFO_IND *ind,int row) {
   int i;

   for (i=0; i<row; i++) {
      printf("%s %9.2f ", emp->emp_name, emp->salary);
      if (ind->ind_com == 0)  
         printf("%12.2f\n", emp->commission);
      else
         printf("%12s\n", " ");
      emp++;
   }
}

int prt_subttl(char *t1) {
   printf("%11.11s   Salary   Commission\n",t1);
   printf("-----------  -------   ----------\n");
}