Using Mysql AutoInc Fields and DBX throws exeception whe inserting new record

fduenas · Post by **fduenas** » Tue 26 Sep 2006 00:23

Hy maybe this is not matter of CrxLab, but whe i insert a new record and having and auntoInc field, the server throws an error of duplicate entry '0' for key '1', it seems that The ClientDataset takes the actual AutoInc value and if it is null it sends a '0', so when posting it it will throw a duplicate entry error.
Any one knows how to prevent this? I still want to see the AutoInc field, but obviously i want the server resolve the value.

Antaeus · Post by **Antaeus** » Tue 26 Sep 2006 08:54

Please check if this problem can be reproduced with standard MySQL driver. If not then send us (evgeniyD*crlab*com) a complete small sample to demonstrate the problem, including script to create and fill table. Also supply us with exact version of Delphi, C++ Builder or Kylix and exact version of DbxMda.
If this problem will be reproduced with standard driver then refer to BDN for more information on how to work with auto increment fields. There are some peculiarities with this field kind.

fduenas · Post by **fduenas** » Fri 29 Sep 2006 07:33

Hi, yes i have been cheked with an older version and seems to do the work.

I think the problem comes because the DBX does pass correctly the Metadata That identifies the specified field as AutoIncrement, so when the TSQLQuery reads the data, it will only detect it as Integer and not as Autoinc. in BDS 2006 i have the same proble with original DBX driver. In previous delphi version it is not.
I'm using:
BDS Delphi 2006 Update 2
Crlab DBX 3.10.4 por MySql (latest)
MySQL 5.0
Windows XP

Antaeus · Post by **Antaeus** » Fri 29 Sep 2006 14:03

As you can see this problem appears using both drivers, DbxMda and standard. So this problem should be solved on the ClientDataset level. Try to change ProviderFlags of the key field.

fduenas · Post by **fduenas** » Mon 02 Oct 2006 20:08

Hi, well now that i have tested both in BDS 2006, bot give erros, you're right. BUt i have tried all things, including chaging provider flags of the field object at the Client DataSet and at the TSQLQuery component and nothing seems to work.

I have checked BDN for something like this but nothing have been posted to solve this special issue. very stange. I have read that ClientDataSet have to pass a value for field that have Null values, and this seems to be the cause of the problem, but there is no way i can force ClientDataSet to pass a null value to the AutoInc field, at least in Delphi 2006 update 2.

I wil try to do some other tests to see how can it be solved.