Re: exceptions in SPL
Exceptions are negative error numbers -- NOTFOUND is not an exception
within the meaning of the term... To code around this, I think the best
way to handle it is probably via a loop that is executed once at most:
CREATE PROCEDURE ... RETURNING INTEGER;
DEFINE address_id INTEGER;
FOREACH 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
RETURN address_id;
END FOREACH;
-- Not found, so insert it...
INSERT INTO address
VALUES (0, org, dep, str, twn, cnty, pcode, ctry);RETURN dbinfo("sqlca.sqlerrd1");
END PROCEDURE;
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
}From: "K. Carter" <kcarter@motiv.co.uk>
}Subject: exceptions in SPL
}Date: Wed, 26 Jun 1996 14:55:20 +0100
}X-Informix-List-Id: <news.25450>
}
}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;
}
}--
}Kathleen Carter PhD
}Consultant
}Motiv Systems Ltd WWW: http://www.motiv.co.uk
}Orwell House Tel: +44 1223 576318
}Cowley Road Fax: +44 1223 576319
}Cambridge CB4 4WY Email: k.carter@motiv.co.uk