Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 10 total
Thread Which field violates a constraint?
Sat, Jan 16 2021 12:13 AMPermanent Link

Ian Branch

Avatar

Hi Team,
If I have two unique fields in a table and a user duplicates an entry in one of the fields and a 1004 (EDB_ERROR_CONSTRAINT) error is generated, how do I tell which field the constraint violation applies to??

Regards & TIA,
Ian
Sat, Jan 16 2021 8:57 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Ian

Just testing in EDBManager with your Users table I get an error

ElevateDB Error #1004 The unique constraint USERID for the table Users has been violated (Duplicate key ADMIN found)

Its a bit convoluted but using the above you can execute a query

select * from information.constraintcolumns where TableName = 'Users' AND ConstraintName = 'USERID'

which should give you the column name.

Roy Lambert
Sat, Jan 16 2021 2:54 PMPermanent Link

Ian Branch

Avatar

Hi Roy,
That works fine for Users where there is only one field with a constraint.
Try it again with Suppliers, which has two constraint fields, SupplierNo & SupplierCode.  Cause a constraint error on one of them, how do I tell which one the User has compromised?

Ian
Sun, Jan 17 2021 4:34 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Ian


Try in EDBManager

update suppliers set supplierno = 23 where supplierno = 1

and you are told


ElevateDB Error #1004 The unique constraint SUPPLIERNO for the table Suppliers has been violated (Duplicate key 23 found)

ie the constraint violated is SUPPLIERNO

I've attached a titchy project to show how to use it - it does use my EDB subclassed components but that's all of my customised set



Roy Lambert







Attachments: Project1.dpr Unit1.dfm Unit1.pas
Sun, Jan 17 2021 5:06 AMPermanent Link

Ian Branch

Avatar

Ahhh.  Now I see what you were getting at.
I was too fixated on one type of solution and not considering what was already available.  My Bad.

Tks Roy,

Ian
Sun, Jan 17 2021 7:18 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Ian


I'll bite - what was the "one type of solution"?

Roy Lambert
Sun, Jan 17 2021 5:02 PMPermanent Link

Ian Branch

Avatar

All,
Just to close this one off.  This is what I ended up with, using Delphi's StrUtils ContainsText function..
Remember - The original requirement was to cover two constraints in the table.
{code}
procedure TSuppliersForm.SuppliersPostError(DataSet: TDataSet; E: EDatabaseError; var Action: TDataAction);
var
 sField, sData: string;
begin
 //
 MessageBeep(MB_ICONERROR);
 //
 if (E is EDatabaseError) and (E is EEDBError) then
 begin
   if (EEDBError(E).ErrorCode = EDB_ERROR_CONSTRAINT) then
   begin
     if ContainsText(E.Message, 'SUPPLIERCODE') then
     begin
       sField := 'Supplier Code';
       sData := Suppliers.FieldByName('SupplierCode').AsString;
     end
     else
     begin
       sField := 'Supplier #';
       sData := Suppliers.FieldByName('SupplierNo').AsString;
     end;
     //
     MessageBeep(MB_ICONERROR);
     TaskMessageDlg('Data entry error!', 'The ' + sField + ' "' + sData + '" has already been used in the Suppliers table!', mtError, [mbOK], 0);
     Abort;
   end
   else
     TaskMessageDlg('Unknown Database Error!', 'Unknown or unexpected database engine error # ' + IntToStr(EEDBError(E).ErrorCode), mtError,
       [mbOK], 0);
 end;
 //
 Action := daAbort;
 //
end;
{code}

Roy,  I was focussed on being able to extract directly the Field in question rather than indirectly from the error message.   Silly me.

Regards,
Ian
Mon, Jan 18 2021 5:06 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Ian



<<Roy,  I was focussed on being able to extract directly the Field in question rather than indirectly from the error message.   Silly me.>>


Tim obviously has the information to incorporate into the error message so if you ask him nicely he may surface them as properties.

Roy Lambert
Wed, Jan 20 2021 1:04 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< Tim obviously has the information to incorporate into the error message so if you ask him nicely he may surface them as properties. >>

EDB does not have the ability to track this information within exception objects because not all constraints are single-column key constraints. Check constraints are just expressions, primary/unique key constraints can have multiple columns, and foreign key constraint errors can contain two sets of possible column names.  So, in the interest of keeping the error messages fairly succinct, the constraint identifier is the information that is presented with constraint violations.

But, because constraints have specific unique identifiers, it's pretty easy to just use those instead of the column names in terms of telling the user where the issue is.  From there, it's a matter of simply querying the system information tables to find out the actual column names involved.

Tim Young
Elevate Software
www.elevatesoft.com
Thu, Jan 21 2021 7:36 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim

What I was thinking was a couple of additional fields showing the constraint (not the actual column(s)) and the table but considering a bit further when its not a constraint viiolatiopn this wouldn't work.


The error message as delivered is

ElevateDB Error #1004 The unique constraint SUPPLIERNO for the table Suppliers has been violated (Duplicate key 23 found)

Looking in edbcomps you're overriding  the Delphi standard database exception  EEDBError = class(EDatabaseError) and (this may be where I go totally wrong) I assume you build the error message. Would it be possible to have "special" delimiters around the key bits of information to make extraction easier? eg

ElevateDB Error #1004 The unique constraint {SUPPLIERNO} for the table {Suppliers} has been violated ({Duplicate key 23 found})




Roy Lambert
Image