Stored Procedure Errors
All
The subject may be misleading in this case, but here goes.
IDS 7.31.FD6X1 running on SunOS 5.8 Generic_108528-20 sparc.
I'm writing my first stored procedure and have sorted out the error trapping
using 'on exception', but what I want to do is to return a row detailing
that no rows were found i.e. I want to trap 'error' 100. Is this possible
within SPL or is 100 recognised as a valid correct status and not trappable?
code snippet:-
BEGIN
ON EXCEPTION SET esql, eisam -- Catch all
errors
IF esql = 100 THEN --
No rows found
LET w_x = "NoOrder";
RETURN w_x, w_y;
ELSE
LET w_orderno = "SQLError";
LET w_x = esql;
LET w_y = eisam;
RETURN w_x, w_y;
END IF
END EXCEPTION
FOREACH
SELECT x
, y
INTO w_x
, w_y
FROM line
WHERE a = w_x
RETURN w_x, w_y WITH RESUME;
END FOREACH
END
This simply returns 'No Rows Found' rather than a single row containing
'NoOrder', ''
Thanks
Keith
**********************************************************************************
This message is sent in strict confidence for the addressee only. It may
contain legally privileged information. The contents are not to be disclosed
to anyone other than the addressee. Unauthorised recipients are requested
to preserve this confidentiality and to advise the sender immediately of any
error in transmission.
This footnote also confirms that this email message has been swept for the
presence of computer viruses, however we cannot guarantee that this message
is free from such problems.
**********************************************************************************
sending to informix-list
Use DBINFO('sqlca.sqlerrd2') to check if the number of rows processed was
zero.
Regards,
Doug Lawry
www.douglawry.webhop.org
"Simmons, Keith" <keith.simmons@office2office.biz>
wrote in message news:c36nem$k41$1@terabinaries.xmission.com...
>
> All
>
> The subject may be misleading in this case, but here goes.
>
> IDS 7.31.FD6X1 running on SunOS 5.8 Generic_108528-20 sparc.
>
> I'm writing my first stored procedure and have sorted out the error
trapping
> using 'on exception', but what I want to do is to return a row detailing
> that no rows were found i.e. I want to trap 'error' 100. Is this possible
> within SPL or is 100 recognised as a valid correct status and not
trappable?
>
> code snippet:-
> BEGIN
> ON EXCEPTION SET esql, eisam -- Catch all
> errors
> IF esql = 100 THEN --
> No rows found
> LET w_x = "NoOrder";
> RETURN w_x, w_y;
> ELSE
> LET w_orderno = "SQLError";
> LET w_x = esql;
> LET w_y = eisam;
> RETURN w_x, w_y;
> END IF
> END EXCEPTION
> FOREACH
> SELECT x
> , y
> INTO w_x
> , w_y
> FROM line
> WHERE a = w_x>
> RETURN w_x, w_y WITH RESUME;
>
> END FOREACH
> END
>
> This simply returns 'No Rows Found' rather than a single row containing
> 'NoOrder', ''
>
> Thanks
>
> Keith
Hi,
Try use:
ON EXCEPTION -<errornumber>
SET esql, eisam
statement....
END EXCEPTION ;
i.e.
ON EXCEPTION -268
SET esql, eisam
LET w_message = "-268 Unique constraint constraint-name violated."
return esql, eisam, w_message
END EXCEPTION ;
OR:
on exception in ( -535, -255 )
SET esql, eisam
LET w_message = "Error 535 or 255 occured."
return esql, eisam, w_message
END EXCEPTION ;
REgards ..... Ferronato
"Simmons, Keith" <keith.simmons@office2office.biz> wrote in message news:<c36nem$k41$1@terabinaries.xmission.com>...
> All
>
> The subject may be misleading in this case, but here goes.
>
> IDS 7.31.FD6X1 running on SunOS 5.8 Generic_108528-20 sparc.
>
> I'm writing my first stored procedure and have sorted out the error trapping
> using 'on exception', but what I want to do is to return a row detailing
> that no rows were found i.e. I want to trap 'error' 100. Is this possible
> within SPL or is 100 recognised as a valid correct status and not trappable?
>
> code snippet:-
> BEGIN
> ON EXCEPTION SET esql, eisam -- Catch all
> errors
> IF esql = 100 THEN --
> No rows found
> LET w_x = "NoOrder";
> RETURN w_x, w_y;
> ELSE
> LET w_orderno = "SQLError";
> LET w_x = esql;
> LET w_y = eisam;
> RETURN w_x, w_y;
> END IF
> END EXCEPTION
> FOREACH
> SELECT x
> , y
> INTO w_x
> , w_y
> FROM line
> WHERE a = w_x>
> RETURN w_x, w_y WITH RESUME;
>
> END FOREACH
> END
>
> This simply returns 'No Rows Found' rather than a single row containing
> 'NoOrder', ''
>
> Thanks
>
> Keith
>
>
> **********************************************************************************
> This message is sent in strict confidence for the addressee only. It may
> contain legally privileged information. The contents are not to be disclosed
> to anyone other than the addressee. Unauthorised recipients are requested
> to preserve this confidentiality and to advise the sender immediately of any
> error in transmission.
> This footnote also confirms that this email message has been swept for the
> presence of computer viruses, however we cannot guarantee that this message
> is free from such problems.
> **********************************************************************************
>
> sending to informix-list
AFAIK 'not found' won't throw an exception you need to look
at dbinfo('sqlca.sqlerrd2 or 3)
"Simmons, Keith" wrote:
>
> All
>
> The subject may be misleading in this case, but here goes.
>
> IDS 7.31.FD6X1 running on SunOS 5.8 Generic_108528-20 sparc.
>
> I'm writing my first stored procedure and have sorted out the error trapping
> using 'on exception', but what I want to do is to return a row detailing
> that no rows were found i.e. I want to trap 'error' 100. Is this possible
> within SPL or is 100 recognised as a valid correct status and not trappable?
>
> code snippet:-
> BEGIN
> ON EXCEPTION SET esql, eisam -- Catch all
> errors
> IF esql = 100 THEN --
> No rows found
> LET w_x = "NoOrder";
> RETURN w_x, w_y;
> ELSE
> LET w_orderno = "SQLError";
> LET w_x = esql;
> LET w_y = eisam;
> RETURN w_x, w_y;
> END IF
> END EXCEPTION
> FOREACH
> SELECT x
> , y
> INTO w_x
> , w_y
> FROM line
> WHERE a = w_x>
> RETURN w_x, w_y WITH RESUME;
>
> END FOREACH
> END
>
> This simply returns 'No Rows Found' rather than a single row containing
> 'NoOrder', ''
>
> Thanks
>
> Keith
>
> **********************************************************************************
> This message is sent in strict confidence for the addressee only. It may
> contain legally privileged information. The contents are not to be disclosed
> to anyone other than the addressee. Unauthorised recipients are requested
> to preserve this confidentiality and to advise the sender immediately of any
> error in transmission.
> This footnote also confirms that this email message has been swept for the
> presence of computer viruses, however we cannot guarantee that this message
> is free from such problems.
> **********************************************************************************
>
> sending to informix-list
--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
www.oninit.com #