Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 10 total |
Which field violates a constraint? |
Sat, Jan 16 2021 12:13 AM | Permanent Link |
Ian Branch | 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 PM | Permanent Link |
Ian Branch | 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent Link |
Ian Branch | 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 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
I'll bite - what was the "one type of solution"? Roy Lambert |
Sun, Jan 17 2021 5:02 PM | Permanent Link |
Ian Branch | 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 |
This web page was last updated on Wednesday, April 17, 2024 at 10:35 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |