At 17:47 27/01/2004 +0100, you wrote:

>Hi Alexandre,

...snip...

> > 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
>http://www.upscene.com

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, etc.

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

Example 3) When try to insert a duplicate PK
Exception Code: 1236
Exception Text: PK Violation
Sub-Type Code: 01
Sub-Type Text: PK Duplicated
Child Table: null
Child PK: null
Parent Table: null
Parent PK: null
Table: Invoice
Table PK: (Invoice_ID)
Fields: null
User Exception: 10003

Example 4) When try to insert a null in a non null field
Exception Code: 1237
Exception Text: Check Null Constraint
Sub-Type Code: 01
Sub-Type Text: Field does not allow null
Child Table: null
Child PK: null
Parent Table: null
Parent PK: null
Table: Invoice_Item
Table PK: (Invoice_ID, Item)
Fields: (Quantity)
User Exception: 10004

Example 5) When try to insert a null in multiples non null fields
Exception Code: 1238
Exception Text: Check Null Constraint
Sub-Type Code: 01
Sub-Type Text: Field does not allow null
Child Table: null
Child PK: null
Parent Table: null
Parent PK: null
Table: Invoice_Item
Table PK: (Invoice_ID, Item)
Fields: (Quantity, Value, Ship_Date)
User Exception: 10005

Example 6) When try to insert/update a field with a check constraint that fails
Exception Code: 1239
Exception Text: Check Constraint Failed
Sub-Type Code: 01
Sub-Type Text: (Quantity > 0)
Child Table: null
Child PK: null
Parent Table: null
Parent PK: null
Table: Invoice_Item
Table PK: (Invoice_ID, Item)
Fields: (Quantity)
User Exception: 10006

and so on...

Of course this is just brain storming, I think that should be more values
and kinds of exceptions... This is feasible ? Or I need more cafeine ? Or I
need a working brain ??? :)

See you !


Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

----------


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


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