AutoIncField type in Persistent Fields

[ecssdev]

D7, mySQL 5.xx (on Win2003 & FC5). myDAC latest build (updated on Wed).

Situation:

Create qry or tbl connected to mySQL 5.xx DB and then right click to add persistent fields.

The AutoInc fields in the DB are not correctly identified and created as type TAutoIncField, they appear as TIntegerField.

Also changed connection to use libmysql.dll (Windows v5.0.22) same problem.

This causes incorrect updates when I pass back the ApplyChanges from the ClientDataSet via the DataSetProvider linked to the myDAC component.

This has broken the briefcase model we need to deploy for this customer.

Also fails if I create the TAutoIncFields at runtime.

Any ideas ?

Cheers

Dave

[Antaeus]

Please provide us with small sample that shows difference in behaviour between MyDAC and ADO (since ADO creates TAutoIncFields).
Also provide us with exact version of MyDAC you use. You can see it in About sheet of TMyConnection Editor.

[ecssdev]

Here is a simple demo with a myDAC connection and an ADO connection to a live mySQL DB (secured only for the account given).

http://downloads.eurocomp.co.uk/myDACDemo.zip

The ADOTable shows idProduct as TAutoIncField in the persistent field defs, myDACTable shows it as TIntegerField

Hope this helps, we have hit a wall with this as the incorrect Field Type breaks D7 MIDAS applyupdates

myDAC is version 4.40.0.18 for Delphi 7 Windows

Cheers

[ecssdev]

Hi,

We did what was asked, any update please.

Regards

Dave

[Antaeus]

We have examined you application. This is not exactly the thing we meant. Please send us by e-mail (evgeniyD*crlab*com) the sample that shows difference between MyDAC and ADO when applying updates. Also include scripts to create and fill table to reproduce this issue on our local server.

[ecssdev]

Sorry, don't have an exact duplicate ADO application.

I have moved to alternate mySQL DAC which does correctly detect AutoInc fields so that our DSP code will fire correctly by testing the field type.

All the best for the future

Dave

[swierzbicki]

Sorry, don't have an exact duplicate ADO application.
I have moved to alternate mySQL DAC ... All the best for the future

???

[ecssdev]

In Delphi 7 DataSnap (MIDAS) when a ClientDataSet has ApplyUpdates called it passes the data delta across to the DataSetProvider. If that DSP is linked to a query or table which has fields of type TAutoIncField the DSP automatically handles any values in those fields within the delta to prevent KeyViolation exceptions when the DSP applies the UPDATE sql statement to the database. See Dan Miser's article on BDN for more details.

The myDAC query and table components do not seem to correctly identify mySQL 5.xx Integer fields with AutoInc set as TAutoIncField and therefore the essential default logic in the ApplyUpdates process at the DSP does not operate correctly.

I am on a tight deadline for a prototype and thus investigated other mySQL direct access components in the market and have now purchased one that does exhibit the correct identification of TAutoIncField.

I am sure that Core Lab would be able to resolve this but unfortunately I had to use another supplier due to time pressures. However this new component set is not dual platform, so I would like to see Core Lab fix this in the future so I can return to their component set for Kylix work.

I am sorry but I cannot assist beyond what I have already done, you should see the issue on any mySQL 5.xx table which has a primary key defined on an Integer field set to AutoInc and then generate persistent fields from a myDAC query or table component in the D7 IDE.

All the best, Dave

[Antaeus]

We have deeply examined this problem. Unfortunately we can not change behaviour of our components to make them be able to identify TAutoIncFields because this may lead to unforeseen consequences for other users.
We can suggest users that have encountered this problem, to create component that is inherited from TMyQuery and can identify TAutoIncFields. Here is the source code of such component:

Code: Select all

unit MyQueryAutoInc;

interface

uses
  Windows, Messages, SysUtils, Classes, DB, MemDS, DBAccess, MyAccess, MyClasses;

type
  TMyQueryAutoInc = class(TMyQuery)
  private
    { Private declarations }
  protected
    procedure CreateFieldDefs; override;
  public
    { Public declarations }
  published
    { Published declarations }
  end;

procedure Register;

implementation

procedure TMyQueryAutoInc.CreateFieldDefs;
var
  i: integer;
begin
  inherited;
  for i := 0 to FieldDefs.Count - 1 do
    if (FieldDefs[i].DataType = ftInteger) and
      (TMySQLFieldDesc(GetFieldDesc(FieldDefs[i].Name)).IsAutoIncrement) then begin
      FieldDefs[i].DataType := ftAutoInc;
      break;
  end;

end;

procedure Register;
begin
  RegisterComponents('MySQL Access', [TMyQueryAutoInc]);
end;

end.