SPL Exception

Hi, I have an SPL procedure shown below DEFINE kn INTEGER; SELECT COUNT(*) INTO kn FROM OF_JNDI WHERE SID = ids AND OID = ido AND Name = idc; IF kn = 0 THEN INSERT INTO OF_JNDI VALUES (ids, ido, idc, classname, child); ELSE RAISE EXCEPTION -746, 23000, "23000"; END IF; What I wanted to do originally was to raise an official SQLState exception so in my Java code through JDBC I get do catch (SQLException ex) { String sqlstate = ex.getSQLState (); if (sqlstate != null && sqlstate.equals ("23000") || However the sqlstate always appears to be the vendor code of IX000. 1) Is there any way of changing this? 2) The other thing I tried was RAISE EXCEPTION -746, 0, "23000"; However off this I go null for message and next exception. SQLState was IX000 and vendor code -746. Why wasn't I getting a message out? 3) With the first method shown I have to do sqlstate.equals ("IX000") && ex.getErrorCode () == -746 && ex.getNextException () != null && ex.getNextException ().getErrorCode () == 23000 - not very nice! Does anyone know a better way of doing this. Thanks, Nick Sent via Deja.com http://www.deja.com/

rnc@my-deja.com wrote: > > Hi, > > I have an SPL procedure shown below > > DEFINE kn INTEGER; > SELECT COUNT(*) INTO kn FROM OF_JNDI WHERE SID = ids AND OID = ido > AND Name = idc;> IF kn = 0 THEN > INSERT INTO OF_JNDI VALUES (ids, ido, idc, classname, child);> ELSE > RAISE EXCEPTION -746, 23000, "23000"; > END IF; > > What I wanted to do originally was to raise an official SQLState > exception so in my Java code through JDBC I get do > > catch (SQLException ex) > { > String sqlstate = ex.getSQLState (); > if (sqlstate != null && sqlstate.equals ("23000") || > However the sqlstate always appears to be the vendor code of IX000. > > 1) Is there any way of changing this? > > 2) The other thing I tried was > RAISE EXCEPTION -746, 0, "23000"; > However off this I go null for message and next exception. SQLState > was IX000 and vendor code -746. Why wasn't I getting a message out? > > 3) With the first method shown I have to do > sqlstate.equals ("IX000") && > ex.getErrorCode () == -746 && > ex.getNextException () != null && > ex.getNextException ().getErrorCode () == 23000 > - not very nice! > Does anyone know a better way of doing this. I don't think there's a direct way to raise an SQLSTATE exception in SPL. The last significant changes in SPL just pre-date the introduction of SQLSTATE. If you can work out which Informix error numbers get translated to SQLSTATE "23000", then you should be able to do a RAISE EXCEPTION in the stored procedure that raises that exception. Judging from the manual, "23000" is an "Integrity constraint violation". I would expect that error -971 (Integrity constraint violations) might do the trick, but you could also look at errors -691 (Missing key in referenced table for referential constraint) or -692 (Key value for constraint is still being referenced) which are specifically referential constraint violations and should probably map to "23000" unless there is a more specific SQLSTATE to which they can be mapped. Experimentation and a message file decompiler are both useful with issues such as this. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"