Bug :- Test Triggers & Procedures From I4GL
Folks,
A few months ago we found a bug in the execution of triggers & stored
procedures from I4GL. We have seen this bug for "prepared" and "simple"
SQL-statements in Engine 5.04.UC1 with I4GL-4.13.UD1, but in case of
Engine 7.20.UC2 with I4GL-6.03.UC2, the bug exists only in case of
"prepared" statements. I thought it will be good if all those who are
interested, can test their Informix engines for this bug and make a list
of failing engines, you can make use of the test code attached.
Sorry for delaying this, for so-long.
Thanks
Felix ( fmathews@systems.dhl.com )
#-- ========== Following is the test code ( shell-archive ) ========= --
# This is a shell archive. Remove anything before this line,
# then unpack it by saving it in a file and typing "sh file".
#
# This archive contains:
# trg_upd.sql prep_upd.4gl smpl_upd.4gl
#
LANG=""; export LANG
PATH=/bin:/usr/bin:/usr/sbin:/usr/ccs/bin:$PATH; export PATH
echo x - trg_upd.sql
cat >trg_upd.sql <<'@EOF'
--====================================================================--
-- trg_upd.sql
--====================================================================--
--drop database trg_tst; -- Make this active for re-creation.
create database trg_tst in ibs with buffered log;
create table b_tst1
(tst1_cd char(2) not null,
tst1_nm char(25) not null
);
create table b_tst3
(tst3_cd char(3) not null,
tst3_nm char(15) not null
);
create table b_tst2
(srv_area_cd char(3),
srv_area_nm char(20),
tst1_cd char(2),
tst3_cd char(3)
);
---------------------
-- Dummy procedure --
---------------------
create procedure init_vl()define l_count integer;
define l_str char(99);
set debug file to "spltrace.tst" with append;
let l_str = "- init_vl() -"; trace l_str;
select count(*) into l_count from b_tst1;end procedure;
-----------------------------------------
-- Check whether country code is valid --
-----------------------------------------
create procedure vl_tst1(a_tst1 like b_tst1.tst1_cd)define l_count integer;
define l_str char(99);
set debug file to "spltrace.tst" with append;
let l_count = 0;
select count(*) into l_count from b_tst1 where tst1_cd = a_tst1; let l_str = "- vl_tst1(" || a_tst1 || ") -" || l_count ||"-";
trace l_str;
if ( l_count = 0 ) then
raise exception -746,0 ,"Invalid Country Code";
end if;
end procedure;
------------------------------------------
-- Check whether currency code is valid --
------------------------------------------
create procedure vl_tst3(a_tst3 like b_tst3.tst3_cd)define l_count integer;
define l_str char(99);
set debug file to "spltrace.tst" with append;
let l_count = 0;
select count(*) into l_count from b_tst3 where tst3_cd = a_tst3; let l_str = "- vl_tst3(" || a_tst3 || ") -" || l_count ||"-";
trace l_str;
if ( l_count = 0 ) then
raise exception -746, 0 ,"Invalid Currency Code";
end if;
end procedure;
-----------------------------------------------------------------------
-- On Update call procedures for validation country and service area --
-----------------------------------------------------------------------
create trigger tu_b_tst2 update on b_tst2
referencing new as new old as old
for each row
(execute procedure init_vl())
,(execute procedure vl_tst1(new.tst1_cd))
,(execute procedure vl_tst3(new.tst3_cd));
---------------------------
-- Insert nintial values --
---------------------------
insert into b_tst1 values ("US","United Stes of America");
insert into b_tst3 values ("USD","US Dollar");
insert into b_tst2 values ("SFO","San Francisco","US","USD");
insert into b_tst2 values ("JFK","New York","US","USD");
@EOF
chmod 664 trg_upd.sql
echo x - prep_upd.4gl
cat >prep_upd.4gl <<'@EOF'
-- ================================================================== --
-- prep_upd.4gl
-- ================================================================== --
--
-- This 4GL program will loop forever until interrupted.
-- Each iteration will try to update the b_tst2 table.
-- Each iteration will halt and not continue until <RETURN> is pressed.
--
-- Triggers should raise exception in each iteration
--
database trg_tst
main
define l_sql char(99)
whenever error continue
let l_sql = "update b_tst2 set tst3_cd = 'xxx'", -- Invalid Data
"where srv_area_cd = 'SFO'" -- This Key
Exists
prepare upd_sid from l_sql
while (1)
execute upd_sid
let l_sql = "Simple Update SQLCODE = ", sqlca.sqlcode
using "-------"," ",err_get(sqlca.sqlcode) clipped
prompt l_sql for l_sql
end while
end main
-- ================================================================== --
@EOF
chmod 664 prep_upd.4gl
echo x - smpl_upd.4gl
cat >smpl_upd.4gl <<'@EOF'
-- ================================================================== --
-- smpl_upd.4gl
-- ================================================================== --
--
-- This 4GL program will loop forever until interrupted.
-- Each iteration will try to update the b_tst2 table.
-- Each iteration will halt and not continue until <RETURN> is pressed.
--
-- Triggers should raise exception in each iteration
--
database trg_tst
main
define l_sql char(99)
whenever error continue
while (1)
update b_tst2 set tst3_cd = 'xxx' -- invalid Data
where srv_area_cd = 'SFO' -- This Key Exists
let l_sql = "Simple Update SQLCODE = ", sqlca.sqlcode
using "-------"," ",err_get(sqlca.sqlcode) clipped
prompt l_sql for l_sql
end while
end main
-- ================================================================== --
@EOF
chmod 664 smpl_upd.4gl
exit 0