How do I make DBX know that fields from joins shouldn't be updated during ApplyUpdates?

Question

I've got some code that builds a map (graph) of rooms on a grid with links between them. It's stored in a Firebird database with rooms in one table and links in another. The data is managed through DB Express TSimpleDataset datasets.

The query for the Exits (links) table looks like this:

select
  EXITS.*,
  r1.x as x,
  r1.y as y,
  r2.x as x2,
  r2.y as y2 
from EXITS
inner join ROOMS r1 on r1.ROOM_ID = EXITS.ROOM1
inner join ROOMS r2 on r2.ROOM_ID = EXITS.ROOM2

Problem is, when I add a new exit and call ApplyUpdates, DBX's SQL parser doesn't seem to understand that the fields from the ROOMS table are just there for convenience and not part of the original table. It generates the following:

insert into "EXITS"
  ("EXIT_ID", "AORDER", "AEXIT", "PREACTION", "POSTACTION", "COLOR", "ROOM1",
   "ROOM2", "MAP1", "MAP2", "X", "Y", "X2", "Y2")
values
  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

plus the appropriate params, with predictable results. Does anyone know how I can make it understand that the Xs and Ys aren't supposed to be inserted or updated?

Answer 1

The SQL for that Query is, well, a query intented for returning data, and is not appropiate for and Insert or Update. Eventhough SimpleDataSets allow for bidirectional usage, not all situations can be handled with it. I would create two different unidirectional ClientDataSet one for the Query and the other with a direct access to the Table instead of using the Query.

Quoting from Getting Started with dbExpress by Martin Rudy:

A more appropriate approach is to use a ClientDataSet (CDS) and DataSetProvider. Connecting to data using a CDS and DSP Both the CDS and DSP components are located on the Data Access component palette. A SQLDataSet component from the dbExpress palette is used to define the result set. The SQLConnection property of the SQLDataSet is assigned to the SQLConnection component name. The SQLDataSet CommandText property defines the SQL statement. The DataSet property of the DSP is set to the name of the SQLDataSet component. The CDS component has a ProviderName property which is set to the DSP name. The last property value change is to the DataSource DataSet property which should be assigned to the CDS component name. Setting the CDS Active property to True (or using the Open method) retrieves the data. At first glance you might feel this is too much work compared to the SimpleClientDataSet. As we cover more of the CDS and DSP features you gain a better understanding of why you will use a DSP and CDS instead of the SimpleClientDataSet

Also, read the chapter on Setting UpdateMode and ProviderFlags properties

There is a lot of interesting information regarding the control of the fields in the dataset used to find and update a record.

Using ClientDataSets, the UpdateMode property is on the DataSetProvider. If you use one of the combination CDS/DSP components (e.g. SimpleClientDataset), those components have an UpdateMode property because of the internal DataSetProvider.

Answer 2

Use provider flags to tell which fileds should be updated, and tell the provider which table shoud be updated (there's an OnGetTableName event or something alike). Another options is to use the OnBeforeUpdateRecord event and write some code to perform the update without using TDatasetProvider built in SQL generator.