#!./pgbash
source ./Test.conf
#---------------------------------------------------------------
#   fetch.bsh
#---------------------------------------------------------------
#
connect to template1 user $USER $PASSWD;

set option_quiet=ON;
drop database $DB1;
set option_quiet=OFF;

create database $DB1;
disconnect template1;
#
echo "****************** FETCH test ******************" 
echo " " 
#
connect to $DB1 as db1 user $USER $PASSWD; 
exec_sql -m
#
#   db1 Database
#
set connection db1;  

set option_quiet=ON;
drop table $TABLE1;
create table $TABLE1( 
   code		int, 		# User Code
   name		varchar(32), 	# User Name
   address	varchar(64) 	# User's Address
); 
set option_quiet=OFF;
#
insert into $TABLE1 values(1, 'sakaida',  'osaka');  
insert into $TABLE1(code,name) values(2, 'postgres'); 
insert into $TABLE1 values(11,'mywife', 'kobe');  
insert into $TABLE1 values(11,'child1', 'hirosima');
insert into $TABLE1 values(11,'child2', 'akasi'); 
#
set option_nullstring='-NULL-';
set option_zerostring='-0-';
select * from $TABLE1;

set exec_sql_option default;

#
#  declare -- fetch INTO 
#
echo "# fetch case no.1"
begin; 
declare cur cursor for select * from $TABLE1; 
while (( SQLCODE == SQL_OK ))
do
	fetch in cur INTO :CODE :IND_C,
		:NAME :IND_N , :ADDRESS indicator :IND_A;
	if (( SQLCODE < 0 || SQLCODE == SQL_NOT_FOUND )); then
		break;
	else
		echo "code=$CODE :$IND_C, name=$NAME :$IND_N, address=$ADDRESS :$IND_A"
	fi
done
end;
#
#  declare -- fetch INTO
#
echo "# fetch case no.2"
begin;
declare cur cursor for select * from $TABLE1;
if(( SQLCODE == SQL_OK ))
then
	declares -i x ; let x=0
	while ((x < 3))
	do
		fetch in cur INTO :CODE indicator :IND_C , 
					:NAME :IND_N, :ADDRESS :IND_A;
		echo "code=$CODE :$IND_C, name=$NAME :$IND_N, address=$ADDRESS :$IND_A"
		let x=x+1
	done
fi
end;
#
# Error
#
echo "------------- Error Test ----------------"
begin;
declare cur cursor for select * from $TABLE1;
fetch in cur INTO :CODE indicator :IND_C,
			:NAME/:IND_N, :ADDRESS :IND_A;
end;
#
begin;
declare cur cursor for select * from $TABLE1;
fetch in cur INTO :CODE indicator :IND_C,
			:NAME_READONLY :IND_N, :ADDRESS :IND_A , :xxx ;
end;
#
begin;
declare cur cursor for select * from $TABLE1;
fetch in cur INTO :CODE indicator :IND_C,
			:NAME_READONLY :IND_N;
end;
#
###declares -r NAME_READONLY
###begin;
###declare cur cursor for select * from $TABLE1;
###fetch in cur INTO :CODE indicator :IND_C,
###			:NAME_READONLY :IND_N, :ADDRESS :IND_A;
###end;
#
disconnect all;
#
#*****************************************
sleep 1
connect to template1 user $USER $PASSWD;
drop database $DB1;
disconnect all;
#