ON EXCEPTION STATEMENT
HI GUYS:
I GOT A PROBLEM WITH A STORED PROCEDURE USING ON EXCEPTION STATEMENT, HERE IS
THE CODE:
create procedure test() returning integer;DEFINE result integer;
DEFINE sql_err, isam_err integer;
DEFINE error_info varchar(70);
define valor integer;
ON EXCEPTION SET sql_err, isam_err, error_info
insert into ola values(1);let result = 1;
return result;
RAISE EXCEPTION sql_err, isam_err, error_info;
let result = 0;
return result;
END EXCEPTION with resume;
end procedure;
this stored procedure have to return 1 or 0, but return nothing, I don't know
what I'm missing or mistaking.
Please help...regards.
I assume that what you want is that if the INSERT fails the function should
return 0 but if it succeeds it should return 1. In that case, it should be
coded as:
create procedure test() returning integer;DEFINE result integer;
DEFINE sql_err, isam_err integer;
DEFINE error_info varchar(70);
define valor integer;
ON EXCEPTION SET sql_err, isam_err, error_info
return 0;
END EXCEPTION;
insert into ola values(1);let result = 1;
return result;
end procedure;
The ON EXCEPTION ... END EXCEPTION block is an independent block within the
procedure. It will be called whenever there is an error raised by the code
in the mainline code of the procedure, including by any SQL executed there.
the RAISE EXCEPTION operations is for you to be able to execute the ON
EXCEPTION...END EXCEPTION block manually based on conditions existing in
your code which are not errors in the strict sense of the word, but are
exceptional conditions in the logic of the procedure. In this case the
failure of the INSERT is sufficient to raise the exception, so you do not
have to do so manually.
Finally, by placing the INSERT and both RETURN statements into the ON
EXCEPTION ... END EXCEPTION block itself, it is NEVER executed and you have
essentially created an empty procedure that does nothing unless it
encounters an error doing nothing.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Mon, Sep 13, 2010 at 9:29 AM, CARLOS LINO <carlos_lv8@hotmail.com> wrote:
> HI GUYS:
>
> I GOT A PROBLEM WITH A STORED PROCEDURE USING ON EXCEPTION STATEMENT, HERE
> IS
> THE CODE:
>
> create procedure test() returning integer;> DEFINE result integer;
> DEFINE sql_err, isam_err integer;
> DEFINE error_info varchar(70);
> define valor integer;
> ON EXCEPTION SET sql_err, isam_err, error_info
> insert into ola values(1);> let result = 1;
> return result;
> RAISE EXCEPTION sql_err, isam_err, error_info;
> let result = 0;
> return result;
> END EXCEPTION with resume;
> end procedure;
>
> this stored procedure have to return 1 or 0, but return nothing, I don't
> know
> what I'm missing or mistaking.
>
> Please help...regards.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016361644b3796ffb0490246116