/************************************************************************
*		
*	
*
* 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 getinfo(char [], char [], int *);
void SYS$PUTMSG(char *,int,int,int);

 EXEC SQL INCLUDE SQLCA;
 main()
 {
	char emp_id[8], last_name[20], name[20];
	int salary, min,max;
	char dbname[]="personnel.db";

	EXEC SQL declare alias_1 alias filename 'personnel.db';
 	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);

	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("\n\n\n");

        printf("********* Retrieve several records  **********\n");	
	printf("Enter salary min: ");
	scanf("%d", &min);
	printf("Enter salary max: ");
	scanf("%d", &max);

	EXEC SQL declare c cursor for select employee_id,last_name,
	salary from alias_1.employees 
	where salary between :min and :max;

   	EXEC SQL open c;

    printf("Employees with salary between %d and %d\n\n",min,max);

	
	  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("\n\n\n");
	    printf ("Employee_id = %s\n",emp_id);
	    printf ("Last_name   = %s\n",last_name);
	    printf("Salary = %d\n", salary);
	    printf("\n\n\n");
            EXEC SQL fetch c into :emp_id, :last_name, :salary ;
	   } while (SQLCA.SQLCODE == 0);
	

	EXEC SQL close c;

	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);
	EXEC SQL rollback;
}
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);
}