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

set OPTION_QUIET=TRUE;
drop database $DB1;
drop database $DB2;
set OPTION_QUIET=FALSE;

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, 
   name		varchar(32), 
   address	varchar(64) 
);
#
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 * from $TABLE1;
#
set OPTION_HEADER=FALSE;
set OPTION_BOTTOM=FALSE;
set OPTION_SEPARATOR=',';
set OPTION_NULLSTRING='-N-';
set OPTION_ZEROSTRING='-Z-';
select * from $TABLE1;

set EXEC_SQL_OPTION DEFAULT;

set OPTION_FRAME=TRUE;
set OPTION_CAPTION='TITLE';
select * from $TABLE1;

set EXEC_SQL_OPTION DEFAULT;

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

set OPTION_HTML=TRUE;
set OPTION_NULLSTRING='-NULL-';
set OPTION_ZEROSTRING='-0-';
select * from $TABLE1;

set OPTION_NULLSTRING;
set OPTION_ZEROSTRING;
set OPTION_BOTTOM=TRUE;
select * from $TABLE1;

set OPTION_BOTTOM=FALSE;
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, 
   name		varchar(32), 
   address	varchar(64) 
);
#
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;
#