Resolving a bug in the TDatasetProvider (the hard way) in Delphi 2010 or Delphi 2009

Question

Hello Folks,

Like every year I receive a new Delphi version as SA owner and this is what I do :

1. Install delphi version XX.
2. Run delphi version XX
3. Insert a TSQLConnection component.
4. Connect it to a Microsoft SQL Server 2000 who will be with us at least for six more years.
5. Insert a TSQLQuery with a 'select top 1 * from myTable' which is a table with some money fields.
6. insert more components TDatasetProvider, TClientdataset, TDatasource, TDBGrid and a TButton.
7. Link everything.
8. Create An event handler in the DatasetProvider.OnUpdateError with a Raise exception.
9. A clientdataset1.applyupdates(0); in the button1click.
10. make some changes in the current record.
11. Press the button1 and get the same exception provided by delphi 2005 and above.
12. Renew my corelab or devArt dbx driver for MsSQLServer.

Seriously now. I found some years ago that TDatasetprovider is generating a very simple error: The TFMTBCDField (representation of money field in Delphi) produces incompatible insert/update sql commands for SQL Server 2000 and previous versions. The problem is simple the dataset provider produces quoted values for money fields which are not accepted for those sql servers.

Example: a table with a varchar field and a money field. You can use an update like this:

UPDATE testtable set MYMONEYFIELD = '1' where MYVARCHARFIELD = 'A'

for previous versions of SQLServer you need to use:

UPDATE testtable set MYMONEYFIELD = 1 where MYVARCHARFIELD = 'A'

Someone has any workaround for this issue?

Answer 1

This is frowned upon by Borland/CodeGear/Embarcadero, but if this is only for your own apps, you can trace down into the VCL source and make the necessary change to the source to ensure you get the correct SQL Generated. You would need to make this change each time you got a new version or until it was fixed, but it would solve your issue.

You could submit your change to codegear and see if they would plug it in.