refreshing client data set

[peat]

I am having a problem updating float fields in a mysql database using Delphi CE and new dbexpress drivers. The following code worked under Dephi 2005 (with old dbexpress). If I comment out updating the float field, it works fine.

The connection is set up as:
SQLConnection -> SQLDataSet -> DataSetProvirer -> ClientDataSet

The SQLDataSet has the float fields provider flags are set to pfinUpdate only and it does not matter what update method I use for the DataSetProvder updade mode upwhereall, upwhereupdate, upwherekey, nothing works.

The error is
"Clientdataset1: Must apply updates before refreshing data"

any help would be appreciated,
Thanks, Peter

Code: Select all

unit float_test;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DbxDevartMySql, FMTBcd, DB, Grids, DBGrids, DBClient, Provider,
  SqlExpr, StdCtrls;

type
  TForm1 = class(TForm)
    SQLConnection1: TSQLConnection;
    SQLDataSet1: TSQLDataSet;
    DataSetProvider1: TDataSetProvider;
    ClientDataSet1: TClientDataSet;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    ClientDataSet1Recnum: TIntegerField;
    ClientDataSet1Instrument_code: TSmallintField;
    ClientDataSet1Date: TDateField;
    ClientDataSet1Time: TTimeField;
    ClientDataSet1Start_levelpercent: TFloatField;
    ClientDataSet1Comments: TWideStringField;
    Button1: TButton;
    SQLDataSet1Recnum: TIntegerField;
    SQLDataSet1Instrument_code: TSmallintField;
    SQLDataSet1Date: TDateField;
    SQLDataSet1Time: TTimeField;
    SQLDataSet1Start_levelpercent: TFloatField;
    SQLDataSet1Comments: TWideStringField;
    SQLMonitor1: TSQLMonitor;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
      clientdataset1.append;
      clientdataset1.edit;
      clientdataset1.FindField('Date').AsString := DatetoStr(Date);
      clientdataset1.FindField('Time').AsString := TimetoStr(Time);
      clientdataset1.FindField('Instrument_Code').AsInteger :=22;
      clientdataset1.FindField('Comments').AswideString := 'test' ;
      clientdataset1.FindField('Start_level[percent]').AsFloat:=23.45;
      clientdataset1.ApplyUpdates(-1);
      clientdataset1.Refresh;
      clientdataset1.refresh;
      clientdataset1.Last;
end;

end.

[peat]

Ok, so the problem is not with the float fields. The issue seems to be with the field name, 'Start_level[percent]'. The sql is failing because of the square brackets. If I change the name to 'Start_level_percent_' all is fine.

However, I can run a sql (say via navicat) with the field name as 'Start_level[percent]' so it seems the problem is with how the sql is getting built, presumably by the ClientDataSet?

This may not not be appropriate for this forum, but if anyone has any guidance, I would appreciate it. I would prefer to find a different solution to changing the field names, as that would require quite a bit of re-coding, even with search and replace!

Thanks,
Peter

[AndreyZ]

Hello,

Add UseQuoteChar=True option to SQLConnection.Params. This option, when turned on, makes the driver quote all names of objects.

[peat]

AndreyZ, Thanks for the reply.

That seems like it should do the trick, however, it does not seem to do anything. If I send the query by updating the command text, it will only run if I explicitly insert the quotes (`) around the offending field names. If I leave them out, the query fails even with UseQuoteChar=True in SQLConnection.Params. (Not sure if I am missing something here, but it will only accept the parameter if it is written as UseQuoteCharacter=True).

UseQuoteChar will throw DBX Error: Error Code 65535.

There is also a DataSetProvider option of UseQuoteChar, but that does not seem to work either....

By The way, this problem came up when we first evaluated Delphi 2007. So somewhere between Delphi 2005 and 2007 something changed.....???

Thanks for your help on this.

Peter

[peat]

by the way, I am using hte newest dbexpress drivers:
dbexpress 4.70.0.26
Mysql 5.051
Delphi XE

[AndreyZ]

UseQuoteChar will throw DBX Error: Error Code 65535.

Thank you for information. We have reproduced this problem and fixed it. This fix will be included in the next DbxMda build.

[peat]

Thanks!
Any idea when this will be?

Peter

[AndreyZ]

We have just released DbxMda version 4.70.26 and the next build won't be very soon. But if you need this fix right now we can build the dll and send it to you. Please, send me an email to andreyz*devart*com.