Unique index / onEditError / onPostError

Discussion of open issues, suggestions and bugs regarding MyDAC (Data Access Components for MySQL) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
info@eventsoft.nl
Posts: 1
Joined: Sat 06 Jan 2018 15:37

Unique index / onEditError / onPostError

Post by info@eventsoft.nl » Sat 06 Jan 2018 15:46

Hi

I use a onEditError and onPostError procedure to see if a NAME already exist.
This is done by creating a unique index on this field. I use the code below to check if
the name is unique.

Normaly there is only 1 unique field called NAME. Now i also have to check if the DATE is
also unique. So i need the following checks :
- field NAME unique OR
- field DATE unique

how can that be done and show the user WHAT is wrong...the NAME or the DATE!

Code: Select all

procedure TdmProduction.ProductionDayEditError(DataSet: TDataSet;
  E: EDatabaseError; var Action: TDataAction);
begin
 {
  if (E is EMyError) then begin
     if (EMyError(E).ErrorCode=ER_DUP_KEY) then begin
       Application.MessageBox(PCHAR('The name '+ UpperCase(ProductionDay.fieldByName('Name').AsString)+' already exists!.'), 'Message', MB_OK+MB_ICONEXCLAMATION+MB_DEFBUTTON1+MB_APPLMODAL);
       ProductionDay.cancel;
       Abort;
    end;
  end;
  }

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Unique index / onEditError / onPostError

Post by ViktorV » Wed 10 Jan 2018 07:52

MySQL server does not return the required information about a certain field present in a unique index and which caused an error, therefore MyDAC does not get this information either.

Peter Rosenfeld
Posts: 1
Joined: Thu 22 Mar 2018 15:33

Re: Unique index / onEditError / onPostError

Post by Peter Rosenfeld » Thu 22 Mar 2018 17:16

This is the first time I use MySQL + FireDAC. The database is great and the components too.
But I'm having a problem with a table where the primary key (PK) is also unique (UN) called 'Name' part of a table named 'Item'.
Example: '7750 SR-12' in an item in another Item '7750 SR 12' (note that the second has no '-' dash). FireDAC and Delphi let the table go through POST. When you try to insert a next record into the database it returns that there is a duplicate error and crashes the program.

When that happens I have to go to MySQL WorkBench and manually change from '7750 SR 12' to '7750 SR-12b' i.e., hence the program works again. Interesting that if I had registered another Item exactly the same it would have given a warning in the POST and would not let record POST. In fact the data is different one has '-' and the other a space in the same position, but somehow for MySQL (perhaps for lack of configuring something) is considering equal '7750 SR 12' and '7750 SR-12' . If Delphi considered equal would not let record POST and they are not equal in fact, but after it writes everything POST it messes up to the table, Items move and appear blank items in the table within Delphi (total mess). Only after fixing the field on MySQL Workbench or if I'm lucky to manage to edit it again before fatal error the table works fine again.

The character set is UTF8 and collate is utf8_general_ci (I do not know if this is the problem). I've even tried to undo the composite primary key and use only the Item name but the problem persists.

CREATE TABLE IF NOT EXISTS `RFnsa`.`tem` (
`Name` VARCHAR (40) NOT NULL,
`UF` VARCHAR (2) NOT NULL,
`UN` VARCHAR (2) NULL,
`Value` DECIMAL (8,2) NULL,
`APN` VARCHAR (15) NULL,
`Product` VARCHAR (15) NULL,
PRIMARY KEY (`Name`,` UF`),
UNIQUE INDEX `UNIQUE` (` Name` ASC, `UF` ASC))
ENGINE = InnoDB

Any help will be very appreciated,

Best regards,

Peter Rosenfeld

ViktorV
Devart Team
Posts: 2299
Joined: Wed 30 Jul 2014 07:16

Re: Unique index / onEditError / onPostError

Post by ViktorV » Mon 26 Mar 2018 07:38

We provide support only for users of our products. To solve the issue, please contact FireDAC technical support.

Post Reply