Re: Bug :- Test Triggers & Procedures From I4GL
Hi,
I've entered this as B68960 into the Informix PTS database. It reproduces
in one form or another in both OnLine and SE, with all modes of database
logging, and using just ESQL/C (I4GL is not necessary to reproduce the
problem). I was running on a Sun Sparc 10 with Solaris 2.5.1 and testing
with 7.21.UC1 (OnLine, SE and ESQL/C). I used 6.04.UC1 I4GL to demonstrate
the problem before creating the ESQL/C reproduction.
The bug report in PTS follows.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
===========================================================================
BUG NUMBER 68960 01/24/97
Product: ONLINE Date: 01/24/97 Dup. Bug ID:
Type: SW Submitter: johnl
---- Short Description ----
IF YOU PREPARE AN UPDATE STATEMENT AND EXECUTE IT REPEATEDLY, AND ON THE
FIRST TIME THE UPDATE FAILS WITH A TRIGGER EXCEPTION, THE SECOND SUCCEEDS
---- Long Description ----
This was reported as a problem with I4GL and OnLine, but it is
reproducible with ESQL/C and OnLine. The original report follows,
together with the ESQL/C reproduction which demonstrates that the problem
is not in I4GL per se. The bug was demonstrated on Solaris 2.5.1 with
OnLine & ESQL/C 7.21.UC1.
=== Original Email (spellings corrected) ===
Date: Thu, 23 Jan 1997 10:48:52 -0800
From: "Felix K. Mathews" <fmathews@systems.dhl.com>
To: informix-list@rmy.emory.edu
Subject: Bug :- Test Triggers & Procedures From I4GL
X-Informix-List-Id: <list.12799>
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 initial values --
---------------------------
insert into b_tst1 values ("US","United States 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 = "Prepared Update SQLCODE = ", sqlca.sqlcode
using "------&"," ",err_get(sqlca.sqlcode) clipped
prompt l_sql for l_sql
end while
end main
-- ================================================================== --
@EOF
chmod 664 prep_up