zOs/SQL/STPRFET
$#@
call sqlConnect DBTF
$;
$<=[
set current sqlid = 's100447' ;
drop procedure A540769.fetP1;
drop procedure A540769.fetP2;
drop procedure gdb9998.fetP1;
drop procedure gdb9998.fetP2;
terminator } ;
create procedure gdb9998.fetP1(in name varchar(20),out out varchar(500))
reads sql data
begin
declare vrs varchar(20);
declare paC int;
declare eof int default 0;
declare sqlCode int default 0;
declare prCu cursor for
select version, parm_count
from sysibm.sysRoutines r
where r.name = fetP1.name;
declare continue handler for not found
begin
set out = out || ' notFound=' || strip(char(sqlCode));
set eof = 1;
end;
set out = 'begin';
open prCu;
set out = out || ' open';
l1: loop
fetch prCu into vrs, paC;
if eof = 1 then
set out = out || ' leave';
leave l1;
end if;
set out = out || ' =' || strip(vrs);
end loop l1;
set out = out || ' endLoop';
close prCu;
set out = out || ' close';
end
}
create procedure gdb9998.fetP2(in name varchar(20),out out varchar(500))
reads sql data
begin
declare vrs varchar(20);
declare paC int;
declare eof int default 0;
declare sqlCode int default 0;
declare prCu cursor for
select version, parm_count
from sysibm.sysRoutines r
where r.name = fetP2.name;
set out = 'begin';
open prCu;
set out = out || ' open';
begin
declare exit handler for not found
set out = out || ' notFound=' || strip(char(sqlCode));
loop
fetch prCu into vrs, paC;
set out = out || ' =' || strip(vrs);
end loop;
end;
set out = out || ' endLoop';
close prCu;
set out = out || ' close';
end
}
terminator ; }
call gdb9998.fetP1(FETP1, :o);
call gdb9998.fetP1(VERSP1, :o);
call gdb9998.fetP1(xxxYY, :o);
call gdb9998.fetP2(FETP1, :o);
call gdb9998.fetP2(VERSP1, :o);
call gdb9998.fetP2(xxxYY, :o);
commit;
$] call sqlStmtsOpt
$#out 20110608 21:05:52
sqlCode 0: set current sqlid = 's100447'
sqlCode -204: drop procedure A540769.fetP1
sqlCode -204: drop procedure A540769.fetP2
sqlCode -204: drop procedure gdb9998.fetP1
sqlCode -204: drop procedure gdb9998.fetP2
sqlCode 0: create procedure gdb9998.fetP1(in name varchar(20),out o...
sqlCode 0: create procedure gdb9998.fetP2(in name varchar(20),out o...
--- called GDB9998.FETP1, sqlCode 0
parm 1 i NAME = FETP1
parm 2 o OUT $o = begin open =V1 notFound=100 leave endLoop close
sqlCode 0: call gdb9998.fetP1(FETP1, :o)
--- called GDB9998.FETP1, sqlCode 0
parm 1 i NAME = VERSP1
parm 2 o OUT $o = begin open notFound=100 leave endLoop close
sqlCode 0: call gdb9998.fetP1(VERSP1, :o)
--- called GDB9998.FETP1, sqlCode 0
parm 1 i NAME = xxxYY
parm 2 o OUT $o = begin open notFound=100 leave endLoop close
sqlCode 0: call gdb9998.fetP1(xxxYY, :o)
--- called GDB9998.FETP2, sqlCode 0
parm 1 i NAME = FETP1
parm 2 o OUT $o = begin open =V1 notFound=100 endLoop close
sqlCode 0: call gdb9998.fetP2(FETP1, :o)
--- called GDB9998.FETP2, sqlCode 0
parm 1 i NAME = VERSP1
parm 2 o OUT $o = begin open notFound=100 endLoop close
sqlCode 0: call gdb9998.fetP2(VERSP1, :o)
--- called GDB9998.FETP2, sqlCode 0
parm 1 i NAME = xxxYY
parm 2 o OUT $o = begin open notFound=100 endLoop close
sqlCode 0: call gdb9998.fetP2(xxxYY, :o)
sqlCode 0: commit
$#out 20110608 21:05:04
*** run error ***
sqlCode SQLCODE not numeric
sqlCode SQLCODE sqlState=SQLSTATE
errMC=SQLERRMC
warnings= 0=SQLWARN.0 1=SQLWARN.1 2=SQLWARN.2 3=SQLWARN.3 4=SQLWARN.4 5=SQLW
errD.1=SQLERRD.1 2=SQLERRD.2 3=SQLERRD.3
errD.4=SQLERRD.4 5=SQLERRD.5 6=SQLERRD.6
stmt = execSql call GDB9998.FETP1 using descriptor :M.SQLSTMTCALL
with M.SQLSTMTCALL = M.SQLSTMTCALL
$#out 20110608 14:36:10
sqlCode 0: set current sqlid = 's100447'
sqlCode 0: drop procedure A540769.fetP1
sqlCode 0: drop procedure A540769.fetP2
sqlCode -204: drop procedure gdb9998.fetP1
sqlCode -204: drop procedure gdb9998.fetP2
sqlCode 0: create procedure gdb9998.fetP1(in name varchar(20),out out v...
sqlCode 0: create procedure gdb9998.fetP2(in name varchar(20),out out v...
--- called GDB9998.FETP1, sqlCode 0
parm 1 i NAME = FETP1
parm 2 o OUT $o = begin open =V1 notFound=100 leave endLoop close
sqlCode 0: call gdb9998.fetP1(FETP1, :o)
--- called GDB9998.FETP1, sqlCode 0
parm 1 i NAME = VERSP1
parm 2 o OUT $o = begin open =V1 =V2 notFound=100 leave endLoop close
sqlCode 0: call gdb9998.fetP1(VERSP1, :o)
--- called GDB9998.FETP1, sqlCode 0
parm 1 i NAME = xxxYY
parm 2 o OUT $o = begin open notFound=100 leave endLoop close
sqlCode 0: call gdb9998.fetP1(xxxYY, :o)
--- called GDB9998.FETP2, sqlCode 0
parm 1 i NAME = FETP1
parm 2 o OUT $o = begin open =V1 notFound=100 endLoop close
sqlCode 0: call gdb9998.fetP2(FETP1, :o)
--- called GDB9998.FETP2, sqlCode 0
parm 1 i NAME = VERSP1
parm 2 o OUT $o = begin open =V1 =V2 notFound=100 endLoop close
sqlCode 0: call gdb9998.fetP2(VERSP1, :o)
--- called GDB9998.FETP2, sqlCode 0
parm 1 i NAME = xxxYY
parm 2 o OUT $o = begin open notFound=100 endLoop close
sqlCode 0: call gdb9998.fetP2(xxxYY, :o)
sqlCode 0: commit
$#out 20110603 18:19:44
sqlCode 0: set current sqlid = 's100447'
sqlCode 0: drop procedure gdb9998.fetP1
sqlCode 0: drop procedure gdb9998.fetP2
sqlCode 0: create procedure gdb9998.fetP1(in name varchar(20),out out v...
sqlCode 0: create procedure gdb9998.fetP2(in name varchar(20),out out v...
--- called gdb9998.FETP1, sqlCode 0
parm 1 i NAME = FETP1
parm 2 o OUT $o = begin open =V1 notFound=100 leave endLoop close
sqlCode 0: call gdb9998.fetP1(FETP1, :o)
--- called gdb9998.FETP1, sqlCode 0
parm 1 i NAME = VERSP1
parm 2 o OUT $o = begin open =V1 =V2 notFound=100 leave endLoop close
sqlCode 0: call gdb9998.fetP1(VERSP1, :o)
--- called gdb9998.FETP1, sqlCode 0
parm 1 i NAME = xxxYY
parm 2 o OUT $o = begin open notFound=100 leave endLoop close
sqlCode 0: call gdb9998.fetP1(xxxYY, :o)
--- called gdb9998.FETP2, sqlCode 0
parm 1 i NAME = FETP1
parm 2 o OUT $o = begin open =V1 notFound=100 endLoop close
sqlCode 0: call gdb9998.fetP2(FETP1, :o)
--- called gdb9998.FETP2, sqlCode 0
parm 1 i NAME = VERSP1
parm 2 o OUT $o = begin open =V1 =V2 notFound=100 endLoop close
sqlCode 0: call gdb9998.fetP2(VERSP1, :o)
--- called gdb9998.FETP2, sqlCode 0
parm 1 i NAME = xxxYY
parm 2 o OUT $o = begin open notFound=100 endLoop close
sqlCode 0: call gdb9998.fetP2(xxxYY, :o)
sqlCode 0: commit
$#out 20110602 13:37:12