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

1
votes

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?

1
sql-serverdelphisql-server-2000dbexpress
Have you registered this error on QC? – Fabricio Araujo
I thought about suggesting that, but this only affects users of MS SQL 2000, which has been deprecated by the vendor. Is that a bug? – Argalatyr
Yes, because it's a numeric field and the value should not be quoted (as the value is NOT a string). The fact that newer versions of RDBMS tolerate this does not make it a feature - it's a bug. And SQL Server 2000 is widely used, since it simply works and most houses does not want to migrate until the last minute. – Fabricio Araujo
Yes Fabricio, QC #69580 and an older one #49628. Some time ago, Codegear staff told me that they can reproduce the case and will made a automated test. But no more about it. – Francis Lee
Is there any reason you aren't using SQL server money (or smallmoney) combined with Delphi's Currency type? – Gerry Coll

1 Answers

0
votes

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.