Subject: Bug :- Test  Triggers  &  Procedures From I4GL
From: fmathews@systems.dhl.com ("Felix K. Mathews")
Newsgroups: comp.databases.informix
Date: 23 Jan 1997 14:53:44 -0500

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