Hi there,

I have a procedure which runs every minute to update the hold status
in an order (IDS 11.50)

It looks up a table called ord_track and if it finds a record in
there, updates the status accordingly and if the row is updated and
throughs no exception
it deletes the row.

But I find sometimes that the status has not been updated and the row
has been deleted. The only reason that I see that the row cannot be
updated is if the row is locked.

But shouldn't in this case through an exceptiton and the delete part
should not execute? How come the delete part is executing?

What am I doing wrong? (see the BEGIN EXCEPTION part)


CREATE PROCEDURE update_hold_status()
DEFINE v_ord_id INTEGER;
DEFINE v_hold_status SMALLINT;
DEFINE n_hold_status SMALLINT;
DEFINE v_order_source_type SMALLINT;
DEFINE v_def_order_source CHAR(20);
DEFINE err_num INTEGER;



SET ISOLATION REPEATABLE READ;

FOREACH ord_track_cursor FOR
SELECT ord_id, order_source_type INTO v_ord_id, v_order_source_type
from ord_track where ord_id > 0
SELECT hold_status, def_order_source INTO v_hold_status,
v_def_order_source FROM ord where ord_id = v_ord_id;

IF v_hold_status in (2,3) THEN
LET n_hold_status = 3;
ELSE
LET n_hold_status = 1;
END IF

IF v_order_source_type = 2 THEN
LET v_def_order_source = 'REP';
END IF

LET err_num = NULL;

BEGIN
ON EXCEPTION
SET err_num
END EXCEPTION
UPDATE ord SET hold_status = n_hold_status, def_order_source =
v_def_order_source where ord_id = v_ord_id;
IF err_num is null then
DELETE FROM ord_track where ord_id = v_ord_id;
END IF
END



END FOREACH

SET ISOLATION COMMITTED READ;

END PROCEDURE
;

I would put it into a trx; dono if it would fix your problem though:

eq:
ON EXCEPTION
ROLLBACK WORK;
return "RETRY in a minute";
END EXCEPTION

FOREACH... -- carefull please that the commit does not close the
foreach!! test please!!!

BEGIN WORK;
UPDATE ord SET hold_status = n_hold_status, def_order_source =
v_def_order_source where ord_id = v_ord_id;

DELETE FROM ord_track where ord_id = v_ord_id;
COMMIT WORK;

END FOREACH
return "DONE whole loop";

or you could try and put triggers on the tables when it deletes a row;
insert a row in some sort of diag table
and if it updates a row insert a row in some sort of diag table;
compare the pair if you mis one??
OR you could start trace for the spl; however this could produce a
whole lot of info...

one more thing:
if v_hold_status = 3
and IF v_order_source_type != 2

it does not seem to change the data?? or am i missing something??

Superboer.



On 29 apr, 17:02, Gentian Hila <genti.t... (AT) gmail (DOT) com> wrote:

A rollback may be useful, good idea. But wouldn't the exception if
caught not even let the delete happend at all?


I think I can solve this issue like this too:

LET nrows = NULL;



UPDATE ord SET hold_status = n_hold_status, def_order_source =
v_def_order_source where ord_id = v_ord_id;

LET nrows = DBINFO('sqlca.sqlerrd2');
IF nrows=1 then

DELETE FROM ord_track where ord_id = v_ord_id;
END IF



But what worries me is why I am not catching the exception as it looks
fine to me. Using exception to me seems like a better way than looking
at the number of the rows that were updated.

As far as the rest of the procedure, it's working well, no need to
worry about that.

I am not sure where you got the following






On Thu, Apr 29, 2010 at 11:55 AM, Superboer <superboer7 (AT) t-online (DOT) de> wrote: