/*
* test.pgc
* --------
* A simple program to test some of the features of the ecpg preprocessor
* This program takes names and amounts input by the user using scanf
* (entering a -1 amount terminates input) and
* stores them as tuples in table t1 in the template1 database. If you want to
* try please change this to your own database name. Also you need to 
* create a table t1 before trying to run this code.
* This file can be compiled by first running the preprocessor on it:
* ecpg test.pgc
*
* This produces a file test.c	
*
* To compile this file type
* gcc -g -I /usr/local/pgsql/include  test.c -L /usr/local/pgsql/lib -lecpg\ 
* -lpq  -lsocket -lnsl
*
* Then you can run it by typing a.out
*/

#include <stdio.h>

exec sql include sqlca; /* include sql communication area header */

/*
* not_found()
* -----------
* Purpose: prints a message a cursor runs out of tuples
*/
void not_found (void) 
{
	printf("No more tuples found\n");
}

/*
* main()
* ------
* Purpose: This is where the operations described at the top of the file
* are implemented
*
*/
main()
{

exec sql begin declare section; /* some sql variables */

	VARCHAR name[15]; 
	int i;
	int tot;
	int number =1;

exec sql end declare section;


exec sql whenever sqlerror sqlprint; /* if get an error print it 
					 good for debugging */


exec sql whenever not found do not_found(); /* what to do if a fetch can't
						find any more tuples */


exec sql connect 'template1'; /* connect to template1 database */

exec sql drop table t1; /* get rid of table t1 if it already exists */

exec sql create table t1 (name varchar(15), amount int); /* now create it
*/


while(number>0)
{

	printf("Please enter name:\n");
	scanf("%s",name.arr);
	
	name.len = strlen(name.arr); /* setting up the name VARCHAR
					structure. Remember SQL datatypes
					not found in C defined using
					structures */

	
	printf("Please enter amount:\n");
	scanf("%d",&number);

	if (number >0){
		exec sql insert into t1(name,amount) values (:name, :number);
			/* we use :varname to pass C structures to SQL */
	}
}

exec sql declare people_cursor cursor for 
	select name, amount from t1 where amount >5 order by amount;
		/* declare a cursor for tuples with amount >5 */		

exec sql select count(*) into :tot from t1 where amount >5; 
	/* set tot to be the number of such tuples */

printf("Tot= %d\n",tot);

exec sql open people_cursor; /*open cursor to read tuples */

for(i=1; i<=tot+1; i++) /* go one more row than needed to demonstrate
				not found condition handling */
{
	exec sql fetch people_cursor into :name, :number; /*fetch next row
							from cursor */

	printf("Name: %s\n", name.arr);	/* print it */
	printf("Amount: %d\n", number);
}

exec sql close people_cursor; /* close cursor */

exec sql commit; /*commit transaction */


}