Stored Procedure Failure
We have just converted to Informix 7.3 and HPUX 11.00 and are getting
the following errors on execution this stored procedure. This error did
not occur when running the same procedure under informix 7.14.
We have reported the problem to Informix and our application vendor but so
far have not received any answers. Any help would be appreciated.
Thanks
Ray Spinhirne rays@admin.sedwards.edu
Director Computer Services
St. Edward's University
----------- basic procedure (processed by special make to create sql)
procedure goodstnd
privilege owner
description "Test for good standing (gpa) "
inputs in_id integer "student id"
in_yr char(4) "FA year"
in_sess char(4) "FA session"
returns char(10) "simple Y/N return"
notes "Parameters are standarized for FA-compliance, even though"
"they might not all be used everytime."
begin procedure
DEFINE ans char(4);
if ( select count(*)
from stu_stat_rec, stufa_rec
where stufa_rec.id = in_id
and stufa_rec.fa_yr = in_yr
and stu_stat_rec.prog = stufa_rec.prog
and stu_stat_rec.id = stufa_rec.id
and
(
(stu_stat_rec.cum_gpa < 2.00 and stu_stat_rec.prog = "UNDG")
or
(stu_stat_rec.cum_gpa < 3.00 and stu_stat_rec.prog = "GRAD")
)
and stu_stat_rec.cum_qual_hrs > 0
and stufa_rec.id not in (select id from ctc_rec
where id = in_id
and stat = "C"
and tick[1,2] = "FY"
and tick[3,4] = in_yr[1,2]
and (resrc = "APPEALY"
or (resrc = "APPEALC"
and in_sess in ("FA ","SU "))
)
)
)
> 0 then
let ans = "N";
else
let ans = "Y";
end if
return ans;
end procedure
grant
execute to (group carsprog)
----------------------------------------------------------------
{ this sample gave the error every time (4) it was run}
{ However reducin the number of rows slected produced examples that would}
{ sometimes give the error and sometimes not}
{ In addition this example did not always fail at the same record }
select id from stufa_rec where fa_yr = "9900"
and id < 90000 order by id into temp a
with no log;
select
id,
goodstnd(id,"9900","FA00") goodstnd
from
a;
-------------------------------- run 1 -----------------------------------
149 row(s) retrieved into temp table.
id goodstnd
1 Y
[about 20 lines cut]
30584 Y
32217 Y
874: Floating point exception error has occurred.
Error in line 11Near character position 1
149 row(s) retrieved into temp table.
------------------------------run 2 ---------------------------------------
id goodstnd
1 Y
[about 50 lines cut]
41291 Y
874: Floating point exception error has occurred.
Error in line 11Near character position 1
----------------------------------------------------------------------
----------------------- part of log file -----------------------------
14:37:15 Assert Failed: Exception Caught. Type: MT_EX_OS, Context: mem
14:37:15 Informix Dynamic Server Version 7.31.UC4
14:37:15 Who: Session(6079, rays@admin, 24667, -797180872)
Thread(6112, sqlexec, d07ac89c, 1)
File: mtex.c Line: 330
14:37:15 Action: Please notify Informix Technical Support.
14:37:15 stack trace for pid 19589 written to /tmp/af.1bc8dc7b
14:37:21 See Also: /tmp/af.1bc8dc7b, shmem.1bc8dc7b.0
14:38:06 Checkpoint Completed: duration was 1 seconds.
14:38:50 Assert Failed: Exception Caught. Type: MT_EX_OS, Context: mem
14:38:50 Informix Dynamic Server Version 7.31.UC4
14:38:50 Who: Session(6111, rays@admin, 25131, -797168104)
Thread(6144, sqlexec, d07b8890, 1)
File: mtex.c Line: 330
14:38:50 Action: Please notify Informix Technical Support.
14:38:50 stack trace for pid 19589 written to /tmp/af.1be8dcda
14:38:56 See Also: /tmp/af.1be8dcda, shmem.1be8dcda.0