IDS 7.31 : Managing exceptions in stored procedures 
Author Message
 IDS 7.31 : Managing exceptions in stored procedures

Hi again,

I need feedback on exception management within stored procedures. All of
my procedures have the following structure :

CREATE PROCEDURE myproc  ( ...  )
   RETURNING ...;
-- variable definition and initialization
BEGIN

   -- exception management bloc
   ON EXCEPTION SET v_errnum, v_eisam
      IF v_errnum IN (...) THEN
          -- do something special
      ELSE
          RAISE EXCEPTION v_errnum, v_eisam;
      END IF;
   END EXCEPTION;

   -- do something
   RETURN (...);

END
END PROCEDURE;

So all of the exceptions that are fired are passed out of myproc by the
'exception management bloc'. It works well but I loose the message part
of the exception. For example, if the exception -268 (unique constraint
violated) is fired, I get the folowing message in the process which
executed myproc :
"..... SQL error [-268] : unique constraint ( ) violated. [ISAM Error
...........]"
But the message I want to get back is the following :
"..... SQL error [-268] : unique constraint ('constraint_name')
violated. [ISAM Error ...........]"

So how is it possible to manage exceptions with keeping track of the
complete message ? Is there a  variable to SET and RAISE with v_errnum
and v_eisam ? If yes, wich data type is it ?

Thanks for your help.

--
- ------------------------------------------------------------------ -
-  Arnaud ANDRIEUX                      tel direct : 02.99.12.52.22  -
-  Alliance Qualite Logiciel             http://www.***.com/            -
-  GROUPE SILICOMP   http://www.***.com/      -
-  Rue de la Chataigneraie - B.P. 127                                -
-  35513 CESSON SEVIGNE    - FRANCE                                  -
-  tel : (33) (0)2.99.12.50.00          fax : (33) (0)2.99.63.70.40  -
- ------------------------------------------------------------------ -



Wed, 18 Jun 1902 08:00:00 GMT
 IDS 7.31 : Managing exceptions in stored procedures

Houps !!

Sorry for asking you such a question ! I found the solution in Informix docs
(I was looking in the wrong one this morning !). As I was wondering, you
just have to declare a varchar variable and manage it just like v_errnum and
v_eisam in my exemple.

....

Quote:

> Hi again,

> I need feedback on exception management within stored procedures. All of
> my procedures have the following structure :

> CREATE PROCEDURE myproc  ( ...  )
>    RETURNING ...;
> -- variable definition and initialization
> BEGIN

>    -- exception management bloc
>    ON EXCEPTION SET v_errnum, v_eisam
>       IF v_errnum IN (...) THEN
>           -- do something special
>       ELSE
>           RAISE EXCEPTION v_errnum, v_eisam;
>       END IF;
>    END EXCEPTION;

>    -- do something
>    RETURN (...);

> END
> END PROCEDURE;

> So all of the exceptions that are fired are passed out of myproc by the
> 'exception management bloc'. It works well but I loose the message part
> of the exception. For example, if the exception -268 (unique constraint
> violated) is fired, I get the folowing message in the process which
> executed myproc :
> "..... SQL error [-268] : unique constraint ( ) violated. [ISAM Error
> ...........]"
> But the message I want to get back is the following :
> "..... SQL error [-268] : unique constraint ('constraint_name')
> violated. [ISAM Error ...........]"

> So how is it possible to manage exceptions with keeping track of the
> complete message ? Is there a  variable to SET and RAISE with v_errnum
> and v_eisam ? If yes, wich data type is it ?

> Thanks for your help.

> --
> - ------------------------------------------------------------------ -
> -  Arnaud ANDRIEUX                      tel direct : 02.99.12.52.22  -
> -  Alliance Qualite Logiciel            http://www.aql.fr            -
> -  GROUPE SILICOMP   http://www.silicomp.fr      -
> -  Rue de la Chataigneraie - B.P. 127                                -
> -  35513 CESSON SEVIGNE    - FRANCE                                  -
> -  tel : (33) (0)2.99.12.50.00          fax : (33) (0)2.99.63.70.40  -
> - ------------------------------------------------------------------ -

--
- ------------------------------------------------------------------ -
-  Arnaud ANDRIEUX                      tel direct : 02.99.12.52.22  -
-  Alliance Qualite Logiciel            http://www.aql.fr            -
-  GROUPE SILICOMP   http://www.silicomp.fr      -
-  Rue de la Chataigneraie - B.P. 127                                -
-  35513 CESSON SEVIGNE    - FRANCE                                  -
-  tel : (33) (0)2.99.12.50.00          fax : (33) (0)2.99.63.70.40  -
- ------------------------------------------------------------------ -


Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. IDS 7.31 : User-defined exceptions in SPL

2. Limits for procedures in IDS 7.31

3. Restoring IDS 7.31 on IDS 2000

4. IDS 7.31 -> IDS.2000 9.21 upgrade

5. IDS 2000 vrs IDS 7.31

6. IDS.2000 and IDS 7.31 on one unix box

7. How many stored procedures can use Informix 7.31?

8. Upgrading IDS from 7.31 to 9.xx

9. IDS 7.31 utf/arabic

10. error with IDS 7.31 UD1 dbexport

11. Memory Leak in IDS 7.31 UD4 for HP-UX 11.0

12. Informix IDS 7.30 to 7.31 (Informix has replied please upgrade)


 
Powered by phpBB® Forum Software