--- In [email protected], Michael Ludwig <milu71@...> wrote:
>
> Jack Mills schrieb am 30.03.2012 um 12:30 (-0000):
>
> > I can post the full sp if it would make thing clearer.
>
> Why not try and bisecting the problem down to the smallest query that
> exhibits the error? That's a proven debugging technique that always
> works.
>
> --
> Michael Ludwig
>
Hi Michael thanks for the response.
I have tested each of the individual queries & they run ok, the problem is
presumably in my code or a timing type issue with firebird.
The SP compiles & debugs perfectly. the sp has 1 input param, 34 local
variables & no output parameters yet it gives an output parameters mismatch
error on the execute statement line.
here is the sp
begin
state = 0;
test = 1;
delete from UOP_CHECK;
select max(State_No) from ptst where UOP_No = :uop
into :max_state;
while (state <= max_state)
do
begin
select max(Test_No) from ptst where UOP_No = :uop and State_No = :state
into :maxtest;
while (test <= maxtest)
do
begin
ta = 'OK';
select c.Test_ID_New, c.ta_check
from ptst p
left outer join tst_cfgd c on p.test_code = c.Test_Code and p.m1
= c.m1 and p.m2 = c.m2 and p.m3 = c.m3
where p.uop_no = :uop and p.state_no = :state and p.test_no =
:test
into :id, :sel_query;
sel_query = replace (sel_query, 'where p.uop_no =','where p.uop_no =
'||:uop||' and p.state_no = '||:state||' and p.test_no = '||:test||'');
execute statement sel_query into
:ta1,:ta2,:ta3,:ta4,:ta5,:ta6,:ta7,:ta8,:ta9,:ta10,:ta11,:ta12,:ta13,:ta14,:ta15,:ta16,:ta17,:ta18,:ta19,:ta20,:ta21,:ta22,:ta23,:ta24,:ta25,:ta26;
if(ta1 = 'bad' or ta2 = 'bad' or ta3 = 'bad' or ta4 = 'bad' or ta5 =
'bad' or ta6 = 'bad' or ta7 = 'bad' or ta8 = 'bad' or ta9 = 'bad' or ta10 =
'bad' or ta11 = 'bad' or ta12 = 'bad'
or ta13 = 'bad' or ta14 = 'bad' or ta15 = 'bad' or ta16 = 'bad' or
ta17 = 'bad' or ta18 = 'bad' or ta19 = 'bad' or ta20 = 'bad' or ta21 = 'bad' or
ta22 = 'bad' or ta23 = 'bad'
or ta24 = 'bad' or ta25 = 'bad' or ta26 = 'bad')
then ta = 'bad';
Insert into UOP_CHECK (ID_NEW, UOP_NO, STATE_NO, TEST_NO, ta, ta1,
ta2,ta3,ta4,ta5,ta6,ta7,ta8,ta9,ta10,ta11,ta12,ta13,ta14,ta15,ta16,ta17,ta18,ta19,ta20,ta21,ta22,ta23,ta24,ta25,ta26)
values
(:id,:uop,:state,:test,:ta,:ta1,:ta2,:ta3,:ta4,:ta5,:ta6,:ta7,:ta8,:ta9,:ta10,:ta11,:ta12,:ta13,:ta14,:ta15,:ta16,:ta17,:ta18,:ta19,:ta20,:ta21,:ta22,:ta23,:ta24,:ta25,:ta26);
test = test+1;
end
test = 1;
state = state+1;
end
end
If I change the statement
select c.Test_ID_New, c.ta_check
from ptst p
left outer join tst_cfgd c on p.test_code = c.Test_Code and p.m1 =
c.m1 and p.m2 = c.m2 and p.m3 = c.m3
where p.uop_no = :uop and p.state_no = :state and p.test_no =
:test
into :id, :sel_query;
to
select ta_check from tst_cfgd where test_id_new = 551 into sel_query;
then the sp runs ok, the problem here is that I am using a fixed test_id_new
rather than the the correct one, which changes for each test in each state but,
the execute statement now runs without error.
I'm not sure what to try next so would appreciate any advice.
Jack
