/************************************************************************
*		
*	
*
* Required files: PERSONNEL database
*
*	Compilation instructions (after you copy the sqlpre.com file over to
*                                  your directory):
*
*  @sqlpre filename (no extension)
*  run filename
*
*************************************************************************/
#include <stdio.h> 
#include <string.h>
#include <descrip.h>

void display();	  
void getinfo(char [], char [], int *);
void sys$putmsg(unsigned long *,int,int,int);

EXEC SQL INCLUDE SQLCA;
EXEC SQL declare alias_1 alias filename 'personnel.db';
EXEC SQL declare c cursor for select * from alias_1.employees;

char emp_id[8], last_name[20];
int salary;

 main()
 {
	char name[20];
	int  min,max;
	float raise;

	char *sqlprog = 
        "update alias_1.employees set salary=salary*? where last_name = ?";
		 

 	printf("\n\n\n******* Disconnect from default ***************\n");
 	EXEC SQL disconnect default;
 	if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
	printf("\n\n\n");

	printf("********* Establish CONNECTION 1 **********\n");

	EXEC SQL connect to 'alias alias_1 filename personnel.db' as 'a1';
	if (SQLCA.SQLCODE !=  0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);

	printf("********* Insert several records  **********\n");
	getinfo(emp_id, last_name, &salary);

	EXEC SQL insert into alias_1.employees (employee_id, last_name, salary)
	values (:emp_id, :last_name, :salary);
 	if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);

        getinfo(emp_id, last_name, &salary);
        EXEC SQL insert into alias_1.employees (employee_id, last_name,salary)
        values (:emp_id, :last_name, :salary);
        if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);

	printf("Enter employee name for raise: ");
	scanf("%s", name);
	printf("Enter raise factor: ");
	scanf("%f", &raise);
	getchar();

/*      Dynamic SQL */
	
	EXEC SQL prepare dynprog from :sqlprog;
	printf("*********Execute dynamic SQl statement*****\n");
       	EXEC SQL execute dynprog using :raise,:name;
        if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);

	display();

        while(1) {
        printf("Enter SQL statemement to prepare and execute\n");
	gets(sqlprog);
	EXEC SQL prepare dynprog from :sqlprog;
	EXEC SQL execute dynprog;
	display();
	}
	 

	printf("***********COMMITTING CHANGES ***************\n");
	EXEC SQL commit;	

 	printf("*************** DISCONNECT ALL CONNECTIONS ***************\n");
	EXEC SQL disconnect all;
 	if (SQLCA.SQLCODE != 0) SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
	
}
void getinfo(char id[], char name[], int *salary)
{
   printf("Enter employee id: ");
   scanf("%s", id);

   printf("Enter employee name: ");
   scanf("%s", name);

   printf("Enter salary: ");
   scanf("%d", salary);
}
void display()
{
  EXEC SQL open c;
  
  EXEC SQL fetch c into :emp_id, :last_name, :salary;

  if (SQLCA.SQLCODE != 0)
    SYS$PUTMSG(&RDB$MESSAGE_VECTOR,0,0,0);
  else
    do {
       printf("id: %s\n", emp_id);
       printf("last name: %s\n", last_name);
       printf("salary: %d\n", salary);
       EXEC SQL fetch c into :emp_id, :last_name, :salary;
       } while( SQLCA.SQLCODE == 0);

  EXEC SQL close c;
}