christophermarkstrauss wrote:

>Hi All
>can someone please help, i am writing my first update procedure and
>am having a problem with the error trapping.
>in my strored procedure (Update section), if i pass it a non existant
>record key (IP_FKey) then the SQL does not jump to the WHEN ANY
>statement but continues as if the update succeeded.
>
>My question is: How do I test for an error such as failed update.
>>From reading the pdf's i was under the impression that this should
>have worked?
>
>
>

When you say Update section, I assume you mean the section where the
"Update" statement is, therefore IP_METHOD <.>1.

You do realize, that the statement does not "fail" simply because the
where clause finds no records to update

WHERE FKEY = :IP_FKEY;

Right?

It runs fine, and you will get no SQLCODE out of the ordinary. It
simply finds no rows to update.

>My stored proc:
>SET TERM ^^ ;
>CREATE PROCEDURE UP_CATEGORY (
> IP_METHOD SmallInt,
> IP_FKEY Integer,
> IP_CREATEDATE Date,
> IP_CREATETIME Time,
> IP_CODE VarChar(20),
> IP_DESCE VarChar(80),
> IP_EUSER VarChar(30))
> returns (
> RESULT VarChar(80))
>AS
>DECLARE VARIABLE W_TABLENAME VARCHAR(20);
>DECLARE VARIABLE W_RESULT VARCHAR(3);
>begin
> W_TABLENAME = 'CATEGORY';
> RESULT = 'NOK';
> W_RESULT = '';
> IF (IP_METHOD = 1) THEN
> BEGIN
> INSERT INTO CATEGORY(FKEY, CREATEDATE, CREATETIME, CODE, DESCE,
>EUSER)
> VALUES (GEN_ID(GEN_CATEGORY,
>1), :IP_CREATEDATE, :IP_CREATETIME, :IP_CODE, :IP_DESCE, :IP_EUSER);
> EXECUTE PROCEDURE
>EP_REGISTER_EVENT :W_TABLENAME, :IP_FKEY, :IP_METHOD
>RETURNING_VALUES :W_RESULT;
> RESULT = W_RESULT;
> WHEN ANY DO
> BEGIN
> RESULT = 'NOK - sqlcode = ' || SQLCODE;
> EXIT;
> END
> END
> ELSE
> BEGIN
> UPDATE CATEGORY
> SET CREATEDATE = :IP_CREATEDATE,
> CREATETIME = :IP_CREATETIME,
> CODE = :IP_CODE,
> DESCE = :IP_DESCE,
> EUSER = :IP_EUSER
> WHERE FKEY = :IP_FKEY;
> EXECUTE PROCEDURE
>EP_REGISTER_EVENT :W_TABLENAME, :IP_FKEY, :IP_METHOD
>RETURNING_VALUES :W_RESULT;
> RESULT = W_RESULT;
> WHEN ANY DO
> BEGIN
> RESULT = 'NOK - SqlCode = ' || SQLCODE;
> EXIT;
> END
> END
>end
>^^
>
>