ClientDataset.RefreshRecord no longer works in Delphi XE for joined tables - any workarounds?

Question

TClientDataset.RefreshRecord no longer generates the table join part of SQL when trying to refresh a record on a ClientDataset connected to a dataset with a joined table in the SQL statement.

As a result, calling this method results in SQL error "invalid column names" for each field not in the main table.

This was not a problem in Delphi 2010 and earlier.

The error occurs with both DBX4 or BDE components connected to the TClientDataset and thus it is highly likely the issue is a problem with changes to TClientDataset code.

To replicate this problem:

Create a new app in Delphi XE with only a single form and drop the required database components on it (TSQLMonitor, TSQLConnection, TSQLQuery, TDatasetProvider, TClientDataset, TDatasource, and TDBGrid) and bind them to each other.

Created a simple SQL statement with a table join and placed this in the TSQLDataset.SQL property.

The SQL statement included just two fields - the key field of the main table, and a field from a joined table - for example in pseudocode:

Select 
  MainTable.IntegerKeyField
  , JoinedTable.JoinField
FROM MainTable
LEFT OUTER JOIN JoinedTable ON MainTable.LookupFieldID = JoinedTable.JoinKeyField

Add both of these fields as persistent fields in both TSQLQuery and TClientDataset with Provider Flag for the key field including pfInKey (RefreshRecord will not work if it does not know which field is the key hence persistent fields is a must).

Add two buttons on the form - one just opens the Clientdataset and the 2nd button calls clientdataset.refreshrecord;

Run the app, press button to open dataset and data displays in the grid.

Press the Refresh Record button, and you will get SQL error "invalid column name" for the joined field.

Close the app, open the SQLMonitor log and in the refresh record SQL statement that Delphi generated, you will see it has not included the table join statement.

====

I would really appreciate any ideas on how to fix this.

Answer 1

Try using a view on the database to implement the required join. Then the delphi component can just select from view_name rather than have to handle the join itself.