RE: SQLCA in stored procedure
I think perhaps Informix-Press need to put a sticker on the front of =
their manuals, directing people to the DBINFO / SQLCA information. =
Attached is a reply I sent less than a week ago on this topic, and there =
have been at least three other people ask exactly the same question in =
recent times.
Mahesh: below is how you trap sql error 100 in SPL -
-RET
__________________________________________________________________________=
_____
From: Richard Thomas on Fri, 1 May 1998 2:14 PM
Subject: RE: Exception Handling
To: informix-list@iiug.org; PRAVEEN MOHANAN
PRAVEEN MOHANAN wrote:
>
> Hi...Users....
> Env. Informix OWS 7.20. What exception is raised where
> there r no rows found after a select stmt. I am trying to write a
> procedure & wanted to trap this exception. Is there any way to find ,
> except
> select count(*) from ..... where....>
> if the count < 1 then
> .....
> end if
>
>
> regards,
>
> Praveen
>
Praveen:
I'm not sure how you intend to use this (is the SQL statement whose =
result you wish to test inside or outside the procedure?) Presuming it's =
outside, try this:
CREATE PROCEDURE stop_if_no_rows()
IF DBINFO('sqlca.sqlerrd2') =3D 0 THEN
RAISE EXCEPTION -746, 0 , "No rows were found in that query"; END IF;
END PROCEDURE;
SELECT *
FROM customer
WHERE 1 =3D 0
INTO TEMP a_temp_table WITH NO LOG;
EXECUTE PROCEDURE stop_if_no_rows();
SELECT ...
HTH
RET
+------------------------------------------+
| Richard Thomas |
| DBA - Marketing Information Systems |
| Optus IT |
| email: richard_thomas@yes.optus.com.au |
| Ph: +61 2 9342 7188 |
| "My opinions are my opinions" |
+------------------------------------------+