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

set OPTION_QUIET=ON;
drop database $DB1;
drop database $DB2;
set OPTION_QUIET=OFF;

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

exec_sql -q "drop table $TABLE1";
create table $TABLE1( 
   code		int, 		# User Code
   name		varchar(32), 	# User Name
   address	varchar(64) 	# User's Address
				# 
);
#
insert into $TABLE1 values(1, 'sakaida',  'osaka');  
insert into $TABLE1 values(2, 'postgres', 'america'); 
insert into $TABLE1 values(11,'', 'kobe');  
insert into $TABLE1(code,address) values(12,'akasi'); 
echo " "
select	code as "# code #", 
	name as "# name #", 
	address as "# address #"
from $TABLE1;
#
# OPTION: -TB -S ',' -N '-N-' -Z '-Z-'
set OPTION_HEADER=OFF;
set OPTION_BOTTOM=OFF;
set OPTION_SEPARATOR=',';
set OPTION_NULLSTRING='-N-';
set OPTION_ZEROSTRING='-Z-';
select * from $TABLE1;

set EXEC_SQL_OPTION DEFAULT;

# OPTION: -L -C 'TITLE'
set OPTION_FRAME=ON;
set OPTION_CAPTION='TITLE';
select * from $TABLE1;

set EXEC_SQL_OPTION DEFAULT;

# OPTION: -X'
exec_sql -X "select * from $TABLE1";

# OPTION: -X -A -S ':'
exec_sql -X -A -S ':' "select * from $TABLE1";

# OPTION: -H -N '-NULL-' -Z '-0-'
set OPTION_HTML=ON;

set OPTION_NULLSTRING='-NULL-';
set OPTION_ZEROSTRING='-0-';
select	code as "# code #", 
	name as "# name #", 
	address as "# address #"
  from $TABLE1;

# OPTION: -HB
set OPTION_NULLSTRING;
set OPTION_ZEROSTRING;
set OPTION_BOTTOM=ON;
select * from $TABLE1;

# OPTION: -H -C 'test case' -O 'BORDER=1' 
set OPTION_BOTTOM=OFF;
set OPTION_CAPTION='test case';
set OPTION_TABLETAG='BORDER=1';
select * from $TABLE1;

set exec_sql_option default;

#
#   db2 Database
#
set connection db2;  
exec_sql -q "drop table $TABLE2";
create table $TABLE2( 
   code		int, 		# User Code
   name		varchar(32),  	# User Name
				#(english)
   address	varchar(64) 	# User's Address
);
#
insert into $TABLE2 values(1001, 'sakaida',  'osaka');  
insert into $TABLE2 values(1002, 'postgres', 'america');  
insert into $TABLE2 values(1111, 'mywife', 'kobe');  
copy $TABLE2 from STDIN; << EOF 
111	suzuki	kobe
222	itirou	kobe
EOF
echo " "
#
select * from $TABLE2; 
exec_sql -d db1  "select * from $TABLE1";
#
disconnect all;
#
#*****************************************
connect to template1 user $USER $PASSWD;
drop database $DB1;
drop database $DB2;
disconnect all;
#