ApplyUppdates fail tclientdataset (Mysql)

[kaffeburk]

Hello,

I have been working with this problem for a few days and i cant isolate the problem. Identical code and propertys sometimes work and sometimes not. What does work is if i enter this code in an Edit/post before i do a Applyupdates(0);

"FieldByName('id').AsInteger:=FieldByName('id').AsInteger-1;

Im using MYISAM table with id as a primary key. The problem i get is that no update happens, the DB monitor shows no traffic, and no exemption is raised. ApplyUpdates returns 0. But it is related to the "id" (witch is the primary key).

Is there some recommended settings for the tclientdataset and the provider when working with unidac?

Regards, Pär

[kaffeburk]

Can also add, no UpdateError from the provider and no ReconcileError in the clientdataset. Also BeforeApplyUpdates is never fired.

[kaffeburk]

I tried to use Firedac instead, then it works.

[ertank]

Hello,

Would you provide a small sample project to demonstrate the problem?

Thanks.

[kaffeburk]

Hello,

Would you provide a small sample project to demonstrate the problem?

Thanks.

Can You provide a small sample project to demonstrate the correct way to hook up a clientdataset to a provider and a uniquery? I have been trying to do that for the past 36 hours, i cant pin down the problem. Thats why i asked for small sample project. I'm still trying to create the sample, but can you please get me a small sample with the correct way to hook up a clientdataset to a provider and a uniquery? Maybe thats all i need and maybe i cant isolate the problem so please (but i keep trying).

[kaffeburk]

Hello again, i got it!

i get a "key violation error". Here is the project:

Code: Select all

unit UAppErr;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Data.DB, DBAccess, Uni,
  MemDS, Datasnap.Provider, Datasnap.DBClient, UniProvider, MySQLUniProvider;

type
  TForm6 = class(TForm)
    cCust_iaddress: TClientDataSet;
    cCust_iaddressid: TIntegerField;
    cCust_iaddressid_customer: TIntegerField;
    cCust_iaddresstypnummer: TStringField;
    cCust_iaddressnummer: TStringField;
    cCust_iaddressmycreated: TDateTimeField;
    cCust_iaddressmyupdated: TDateTimeField;
    pCust_iaddress: TDataSetProvider;
    qCust_iaddress: TUniQuery;
    qCust_iaddressid: TIntegerField;
    qCust_iaddressid_customer: TIntegerField;
    qCust_iaddresstypnummer: TStringField;
    qCust_iaddressnummer: TStringField;
    qCust_iaddressmycreated: TDateTimeField;
    qCust_iaddressmyupdated: TDateTimeField;
    MainCon: TUniConnection;
    Button1: TButton;
    Memo1: TMemo;
    MySQLUniProvider1: TMySQLUniProvider;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form6: TForm6;

implementation

{$R *.dfm}

procedure TForm6.Button1Click(Sender: TObject);
  Var
    id : integer;
    f:integer;

begin
  id:=1;

        With cCust_iaddress Do BEgin
              Open;
               for f := 0 to 50 do Begin
                Append;
                  FieldByName('id_customer').AsInteger:=id;
                  FieldByName('typnummer').AsString:='email';
                  FieldByName('nummer').AsString:='[email protected]';
                Post;

                Append;
                  FieldByName('id_customer').AsInteger:=id;
                  FieldByName('typnummer').AsString:='email';
                  FieldByName('nummer').AsString:='[email protected]';
                Post;

                Append;
                  FieldByName('id_customer').AsInteger:=id;
                  FieldByName('typnummer').AsString:='email';
                  FieldByName('nummer').AsString:='[email protected]';
                Post;
              End;
        End;
      cCust_iaddress.ApplyUpdates(0);
      Beep;
end;

end.


//USE demo;
//
//DROP TABLE cust_iaddress;
//CREATE TABLE cust_iaddress (
//  id int(11) NOT NULL AUTO_INCREMENT,
//  id_customer int(11) DEFAULT NULL,
//  typnummer varchar(255) DEFAULT NULL,
//  nummer varchar(255) DEFAULT NULL,
//  mycreated datetime DEFAULT NULL,
//  myupdated datetime DEFAULT NULL,
//  PRIMARY KEY (id)
//)
//ENGINE = MYISAM
//AUTO_INCREMENT = 1
//CHARACTER SET utf8
//COLLATE utf8_general_ci;

object Form6: TForm6
  Left = 0
  Top = 0
  Caption = 'Form6'
  ClientHeight = 299
  ClientWidth = 635
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object Button1: TButton
    Left = 223
    Top = 266
    Width = 75
    Height = 25
    Caption = 'Button1'
    TabOrder = 0
    OnClick = Button1Click
  end
  object Memo1: TMemo
    Left = -8
    Top = -6
    Width = 225
    Height = 297
    Lines.Strings = (
      'Database also in the PAS file'#11
      #11
      'USE demo;'
      ''
      'DROP TABLE cust_iaddress;'
      'CREATE TABLE cust_iaddress ('
      '  id int(11) NOT NULL AUTO_INCREMENT,'
      '  id_customer int(11) DEFAULT NULL,'
      '  typnummer varchar(255) DEFAULT NULL,'
      '  nummer varchar(255) DEFAULT NULL,'
      '  mycreated datetime DEFAULT NULL,'
      '  myupdated datetime DEFAULT NULL,'
      '  PRIMARY KEY (id)'
      ')'
      'ENGINE = MYISAM'
      'AUTO_INCREMENT = 1'
      'CHARACTER SET utf8'
      'COLLATE utf8_general_ci;')
    TabOrder = 1
  end
  object cCust_iaddress: TClientDataSet
    Aggregates = <>
    IndexFieldNames = 'id_customer'
    Params = <>
    ProviderName = 'pCust_iaddress'
    Left = 496
    Top = 216
    object cCust_iaddressid: TIntegerField
      FieldName = 'id'
    end
    object cCust_iaddressid_customer: TIntegerField
      FieldName = 'id_customer'
    end
    object cCust_iaddresstypnummer: TStringField
      FieldName = 'typnummer'
      Size = 255
    end
    object cCust_iaddressnummer: TStringField
      FieldName = 'nummer'
      Size = 255
    end
    object cCust_iaddressmycreated: TDateTimeField
      FieldName = 'mycreated'
    end
    object cCust_iaddressmyupdated: TDateTimeField
      FieldName = 'myupdated'
    end
  end
  object pCust_iaddress: TDataSetProvider
    DataSet = qCust_iaddress
    Options = [poAllowMultiRecordUpdates, poAllowCommandText, poUseQuoteChar]
    UpdateMode = upWhereKeyOnly
    Left = 496
    Top = 160
  end
  object qCust_iaddress: TUniQuery
    KeyFields = 'id'
    SQLInsert.Strings = (
      'INSERT INTO cust_iaddress'
      '  (id_customer, typnummer, nummer, mycreated, myupdated)'
      'VALUES'
      '  (:id_customer, :typnummer, :nummer, :mycreated, :myupdated)')
    SQLDelete.Strings = (
      'DELETE FROM cust_iaddress'
      'WHERE'
      '  id = :Old_id')
    SQLUpdate.Strings = (
      'UPDATE cust_iaddress'
      'SET'
      
        '  id_customer = :id_customer, typnummer = :typnummer, nummer = :' +
        'nummer, mycreated = :mycreated, myupdated = :myupdated'
      'WHERE'
      '  id = :Old_id')
    SQLLock.Strings = (
      'SELECT * FROM cust_iaddress'
      'WHERE'
      '  id = :Old_id'
      'FOR UPDATE')
    SQLRefresh.Strings = (
      
        'SELECT id_customer, typnummer, nummer, mycreated, myupdated FROM' +
        ' cust_iaddress'
      'WHERE'
      '  id = :id')
    SQLRecCount.Strings = (
      'SELECT COUNT(*) FROM cust_iaddress')
    Connection = MainCon
    SQL.Strings = (
      'select * from cust_iaddress')
    IndexFieldNames = 'id_customer'
    Left = 352
    Top = 40
    object qCust_iaddressid: TIntegerField
      AutoGenerateValue = arAutoInc
      FieldName = 'id'
      ProviderFlags = [pfInUpdate, pfInWhere, pfInKey]
    end
    object qCust_iaddressid_customer: TIntegerField
      FieldName = 'id_customer'
    end
    object qCust_iaddresstypnummer: TStringField
      FieldName = 'typnummer'
      Size = 255
    end
    object qCust_iaddressnummer: TStringField
      FieldName = 'nummer'
      Size = 255
    end
    object qCust_iaddressmycreated: TDateTimeField
      FieldName = 'mycreated'
    end
    object qCust_iaddressmyupdated: TDateTimeField
      FieldName = 'myupdated'
    end
  end
  object MainCon: TUniConnection
    AutoCommit = False
    ProviderName = 'MySQL'
    Port = 3306
    Database = 'Demo'
    Options.KeepDesignConnected = False
    LoginPrompt = False
    Left = 496
    Top = 24
  end
  object MySQLUniProvider1: TMySQLUniProvider
    Left = 304
    Top = 136
  end
end

Hello,

Would you provide a small sample project to demonstrate the problem?

Thanks.

[kaffeburk]

Hello again. I have a workaround, but not exactly nice. Is there some way to tell the Client Dataset to ignore the Key?

Code: Select all

procedure TForm6.Button1Click(Sender: TObject);
  Var
    id : integer;
    f:integer;
    nr:Integer;
    FalseKey : integer;

begin
  id:=1;

        With cCust_iaddress Do Begin
          Open;
          FalseKey:=999999;


               for f := 0 to 50 do Begin
                Append;
                  Inc(FalseKey);FieldByName('id').AsInteger:=FalseKey;
//                  nr:=FieldByName('id').AsInteger;Memo1.Lines.Add(IntToStr(id));
                  FieldByName('id_customer').AsInteger:=id;
                  FieldByName('typnummer').AsString:='email';
                  FieldByName('nummer').AsString:='[email protected]';
                  Post;


              End;
              ApplyUpdates(0);
              Refresh;
        End;
      Beep;
end;

[kaffeburk]

Perhaps check this out viewtopic.php?f=28&t=25620

BUT as You can se I already have "PacketRecords to -1."

[ViktorV]

This issue is related to TClentDataSet behavior specificity, but not UniDAC. You can verify this by using UniDAC components instead of standard FireDAC components.
To solve the issue you can change your sample into the following one:

Code: Select all

unit UAppErr;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, Data.DB, DBAccess, Uni,
  MemDS, Datasnap.Provider, Datasnap.DBClient, UniProvider, MySQLUniProvider;

type
  TForm6 = class(TForm)
    cCust_iaddress: TClientDataSet;
    cCust_iaddressid: TIntegerField;
    cCust_iaddressid_customer: TIntegerField;
    cCust_iaddresstypnummer: TStringField;
    cCust_iaddressnummer: TStringField;
    cCust_iaddressmycreated: TDateTimeField;
    cCust_iaddressmyupdated: TDateTimeField;
    pCust_iaddress: TDataSetProvider;
    qCust_iaddress: TUniQuery;
    qCust_iaddressid: TIntegerField;
    qCust_iaddressid_customer: TIntegerField;
    qCust_iaddresstypnummer: TStringField;
    qCust_iaddressnummer: TStringField;
    qCust_iaddressmycreated: TDateTimeField;
    qCust_iaddressmyupdated: TDateTimeField;
    MainCon: TUniConnection;
    Button1: TButton;
    Memo1: TMemo;
    MySQLUniProvider1: TMySQLUniProvider;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure cCust_iaddressNewRecord(DataSet: TDataSet);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form6: TForm6;
  FID: Integer;

implementation

{$R *.dfm}

procedure TForm6.Button1Click(Sender: TObject);
  Var
    id : integer;
    f:integer;

begin
  id:=1;

        With cCust_iaddress Do BEgin
              Open;
               for f := 0 to 50 do Begin
                Append;
                  FieldByName('id_customer').AsInteger:=id;
                  FieldByName('typnummer').AsString:='email';
                  FieldByName('nummer').AsString:='[email protected]';
                Post;

                Append;
                  FieldByName('id_customer').AsInteger:=id;
                  FieldByName('typnummer').AsString:='email';
                  FieldByName('nummer').AsString:='[email protected]';
                Post;

                Append;
                  FieldByName('id_customer').AsInteger:=id;
                  FieldByName('typnummer').AsString:='email';
                  FieldByName('nummer').AsString:='[email protected]';
                Post;
              End;
        End;
      cCust_iaddress.ApplyUpdates(0);
      Beep;
end;

procedure TForm6.cCust_iaddressNewRecord(DataSet: TDataSet);
begin
  cCust_iaddressID.AsInteger := FID;
  Dec(FID);
end;

procedure TForm6.FormCreate(Sender: TObject);
begin
  FID := -1;
end;

end.

You can get more details about the error cause and its solution by the link: http://edn.embarcadero.com/article/20847

[kaffeburk]

Hello and thanx,

Looks nice, i will look into it ASAP. Do You have any version of Unidac with cloned cursor and the rest of the features of CDS ? Then i don't need the clientdatasets att all.

Regards, Pär

[kaffeburk]

Works perfect, thanx a lot !

[ViktorV]

Thank you for being interested in our products.
Feel free to contact us if you have any further questions about our products.