Re: exceptions in SPL

In article <31D14148.792A@motiv.co.uk>, "K. Carter" <kcarter@motiv.co.uk> writes >Using SPL, I wrote the following to see if a given record was in the >database, then if it was not found, to insert it. Either way, the >procedure is intended to return the serial number of the address. I >assumed this was an ideal situation for using the "NOT FOUND" exception >but in fact the code in the exception is never executed. On tracing the >code I find that if the address is not present then address_id is set to >NULL. So for the moment I've rewritten the code to test for NULL >instead, but I am puzzled by this behaviour. > >Kathy Carter > >BEGIN > ON EXCEPTION IN ( > 100 -- NOT FOUND > ) > INSERT INTO address > VALUES (0, org, dep, str, twn, cnty, pcode, ctry);> address_id = dbinfo("sqlca.sqlerrd1"); > END EXCEPTION; > > SELECT addr_id > INTO address_id > FROM address > WHERE > organisation = org > AND department = dep > AND street = str > AND town = twn > AND county= cnty > AND postcode = pcode > AND country = ctry;>END; > >RETURN address_id; > I am not sure if the NOU FOUND exception is 100 or -100... I would have thought you could use NOTFOUND rather than the value as you can with any other Informix language. Also I would initialize address_id to NULL before the select. It is not the select which sets it to NULL if it is does not find anything. NULL is ***SOMETIMES*** the default value if something is not initialized but NOT ALWAYS. E.g. In INFORMIX-4GL (Compiled version) sometimes integers have a default value of NULL and sometimes -2145692347 or someother similar figure around 2 biliion starting with -21.. I can't remember the exact value ;->. This can even vary across platforms with the same version. -- David Williams