/*-------------------------------------------------------------------+
|                COPYRIGHT (C) 1995, SAS INSTITUTE INC.              |
|                  UNPUBLISHED - ALL RIGHTS RESERVED                 |
|                     S A S / C   S A M P L E                        |
|                                                                    |
|                                                                    |
|         NAME: ORASASC                                              |
|     LANGUAGE: C                                                    |
|      PURPOSE: This program is an example of using the ORACLE Pro*C |
|               Precompiler with SAS/C.                              |
|   MVS -                                                            |
|      COMPILE: See JCL in prefix.SAMPLE.AUX(SQLJCL).                |
|         LINK: See JCL in prefix.SAMPLE.AUX(SQLJCL).                |
|      EXECUTE: See JCL in prefix.SAMPLE.AUX(ORALOAD).               |
|         NOTE: This example requires that you have the ORACLE Pro*C |
|               Precompiler, version 1.3.                            |
+-------------------------------------------------------------------*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>
#define MAXEMPLOY 600
#define SASC
#define SQLCA_INIT
void printdb();
void sortdb();
void getdb();
static int cmp(const void *,const void *);

EXEC SQL BEGIN DECLARE SECTION;     /* Declare host variables */
  char *uname = "SCOTT";
  char *pw = "TIGER";
 int empno;                     /* temporary variables for SQL */
 VARCHAR elast[10];
 char efirst[2];
 VARCHAR j[4];
 int d;
 float s;
 char sqlstmt[110];         /* dynamic SQL statement */
EXEC SQL END DECLARE SECTION;

  typedef struct {             /* Employee Structure */
   int empno;                  /* employee number,   */
   char efirst[2];             /* first initial,     */
   char elast[10];          /* last name,         */
   char job[4];             /* position abbrev.   */
   int deptno;                 /* dept number        */
   float sal;                  /* monthly salary     */
  } EMP_STRUCT;
 EMP_STRUCT e[MAXEMPLOY];

 char temp[5];
 char c;
 int i,count;             /* array counter,record counter */

EXEC SQL DECLARE SQLOBJ STATEMENT;

EXEC SQL INCLUDE SQLCAC;     /* this was sqlca teb*/

void main()
{
  EXEC SQL WHENEVER SQLERROR GOTO iferror;
  EXEC SQL CONNECT :uname IDENTIFIED BY :pw;
   printf("Connected to ORACLE. \n");

  for(i=0;i<110;i++)              /* initialize sqlstmt */
   sqlstmt[i] = ' ';
                                  /* set sqlstmt for dynamic call */
  strcpy(sqlstmt,"INSERT INTO EMP (EMPNO, EFIRST, ELAST, JOB, SAL,");
  strcat(sqlstmt," DEPTNO) VALUES (:empno, :efirst, :elast, :j,");
  strcat(sqlstmt," :s, :d)");
  for(i=110;sqlstmt[i]='\0';i--)
    sqlstmt[i] = ' ';
  EXEC SQL PREPARE SQLOBJ FROM :sqlstmt;

 for(;;)
 {
 /* get the current database from ORACLE */
  getdb();

 /* sort the database by name */
  sortdb();

 /* print the database */
  printdb();

  printf("Would you like to 1) insert, or 2) delete a record, or ");
  printf("0) quit? \n");
  while((c = getchar()) !=  0 )
   {
    getchar();              /* get rid of excess characters */
    switch (c)
    {
    case '0':              /* QUIT */
     {
      EXEC SQL COMMIT WORK RELEASE;
      exit(0);
     }
     case '1':             /* INSERT */
      {
      strcpy(j.arr,"New");   /* set initial values of 3 vars */
      s = 0.0;
      d = 100;
      printf("Enter the employee information in the ");
      printf("form: LASTNAME FIRSTINITIAL EMPNO \n");
      printf("The lastname should be <10 chars. \n");
      scanf("%s %s %d",elast.arr,efirst,&empno);
      getchar();            /* get rid of excess characters */

     EXEC SQL EXECUTE SQLOBJ USING :empno,:efirst,:elast,:j,:s,:d;

      break;
      }
     case '2':          /* DELETE */
     {
      printf("Enter the Employee Number to delete: ");
      scanf("%d",&empno);
      getchar();            /* get rid of excess characters */

      EXEC SQL DELETE FROM EMP WHERE EMPNO = :empno;
      break;
     }
    default:
     {
      printf("Invalid response. Please choose again. \n");
      break;
     }
    }
   getdb();         /* updated database -- sort and print */
   sortdb();
   printdb();

  printf("Would you like to 1) insert, or 2) delete a record, or ");
  printf("0) quit? \n");
  }
 }

 /* if an SQL error should result during processing */
 iferror:
 {
  printf("SQL error: %.70s \n",sqlca.sqlerrm.sqlerrmc);
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
 }

  return;
}
void printdb()
/*-----------------------------------------------------------------*/
/* This routine prints the current database as read in from ORACLE */
/* by the routine getdb(). The information is stored in e array.   */
/*-----------------------------------------------------------------*/
{
 /* print the database */
  printf("The current database: \n");
  printf("Employee  Emp No.  Position   Salary   DeptNo.\n");
 for(i=0;i<count;i++)
  printf("%s,%s     %d     %s     %6.2f     %d\n",e[i].elast,
  e[i].efirst,e[i].empno,e[i].job,e[i].sal,e[i].deptno);

 return;
}

void getdb()
/*-----------------------------------------------------------------*/
/* This routine requests the current database information from     */
/* ORACLE using embedded SQL.  Information is stored in e array.   */
/*-----------------------------------------------------------------*/
{
  EXEC SQL WHENEVER NOT FOUND GOTO rtrn;

  count = -1; /* count collects the number of records from ORACLE */

  EXEC SQL DECLARE X CURSOR FOR
           SELECT *
           FROM EMP;
  EXEC SQL OPEN X;

 for(i=0;;i++)         /* This loop is exited when ORACLE signals */
 {                     /* the end of the db, or an error occurs.  */
  count++;
  EXEC SQL FETCH X INTO :empno, :efirst, :elast, :j, :s, :d;
  elast.arr[elast.len] = '\0';
  efirst[1] = '\0';
  strupr(efirst);
  strupr(elast.arr);
  j.arr[j.len] = '\0';
  strcpy(e[i].elast,elast.arr);
  strcpy(e[i].efirst,efirst);
  e[i].empno = empno;
  strcpy(e[i].job,j.arr);
  e[i].sal = s;
  e[i].deptno = d;
 }
 rtrn:               /* flow continues here if end of db is reached */
  EXEC SQL CLOSE X;
 return;
 iferror:        /* program quits here if an error occurs */
 {
  printf("SQL error: %.70s \n",sqlca.sqlerrm.sqlerrmc);
  EXEC SQL WHENEVER SQLERROR CONTINUE;
  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
 }
}
void sortdb()
/*------------------------------------------------------------------*/
/* sortdb() uses qsort to arrange the database information in order */
/* by last name and first initial.                                  */
/*------------------------------------------------------------------*/
{
  size_t n;

  n = count;
  qsort(e,n,sizeof(EMP_STRUCT),&cmp);

  return;
}
static int cmp(const void *first, const void *second)
{
  int x;
  EMP_STRUCT *efirst, *esecond;

  efirst = (EMP_STRUCT *) first;
  esecond = (EMP_STRUCT *) second;

  x=strcmp(efirst->elast,esecond->elast);
  if(x) return(x);
  else return(strcmp(efirst->efirst,esecond->efirst));
}