Subject Re: Server Exceptions (was Re: [firebird-support] Is using SELECT COUNT (*) in a stored procedure a bad idea? (Once Again))
Author Alexandre Benson Smith
At 15:46 27/01/2004 -0200, you wrote:

>At 17:47 27/01/2004 +0100, you wrote:
> >Hi Alexandre,
> > > Anyone has any suggestions about it ???
> >
> >Well, it would be nice to link a particular "EXCEPTION" to a certain FK
> >violation. But, the exception should be able to store longer messages than
> >78 characters. At the client side, you could - for example - use the
> >exception and get your language specific exception description for it.
> >
> >btw, I'm using the foreign key constraint name in the client side exception
> >and I'm formulating a user-understandable exception from this.
> >
> >With regards,
> >
> >Martijn Tonies
> >Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
> >Server.
> >Upscene Productions
> >
>Martin and Nando,
>Using nando sugestion to store a number inside the exception is a good
>workaround to make the exception error more user friendly but the major
>exceptions generated from the server are FK violations, nulls, violations,
>Martin sugestion to link and exception to a FK violation would solve this,
>but if FB permits to link exceptions to various Server exceptions (nulls,
>checks, uniques, PK constraints, FK violations on parent and client
>tables) will be really good. each one linked to a user defined exception,
>with a unique and constant number.
>I don't know if is possible to the engine (in a future version of course)
>return an exception with detailed information like this:
>Example 1) When try to insert a child without a parent
>Exception Code: 1234
>Exception Text: FK Violation
>Sub-Type Code: 01
>Sub-Type Text: Parent does not exists
>Child Table: Invoice_Item
>Child PK: (Invoice_ID, Item)
>Parent Table: Invoice
>Parent PK: (Invoice_ID)
>Table: Invoice_Item
>Table PK: (Invoice_ID, Item)
>Fields: null
>User Exception: 10001
>Example 2) When try to delete/update a parent with childs
>Exception Code: 1235
>Exception Text: FK Violation
>Sub-Type Code: 02
>Sub-Type Text: Child exists
>Child Table: Invoice_Item
>Child PK: (Invoice_ID, Item)
>Parent Table: Invoice
>Parent PK: (Invoice_ID)
>Table: Invoice
>Table PK: (Invoice_ID)
>Fields: null
>User Exception: 10002

I made a Typo.... Exception code for Example 1 and 2 should be the same
(1234) since both are FK violations sub-type code should differs....

Example 2 should look like this:

Example 2) When try to delete/update a parent with childs
Exception Code: 1234
Exception Text: FK Violation
Sub-Type Code: 02
Sub-Type Text: Child exists
Child Table: Invoice_Item
Child PK: (Invoice_ID, Item)
Parent Table: Invoice
Parent PK: (Invoice_ID)
Table: Invoice
Table PK: (Invoice_ID)
Fields: null
User Exception: 10002

Alexandre Benson Smith
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil


Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
Version: 6.0.568 / Virus Database: 359 - Release Date: 26/01/2004

[Non-text portions of this message have been removed]