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