/*-------------------------------------------------------------------+
| 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));
}