UpdatingTable not functioning properly

[rept]

I asked the following question to support:

We have the following select statement (simplified):

select EC_ID, EC_Name, EC_FirstName, EC_Type from employeeContracts union
select EC_ID, EC_Name, EC_FirstName, EC_Type from OldEmployeeContracts

We now want to enable updates. I thought we could just put EmployeeContracts in UpdatingTable and it should be fine. As soon as we put that in there, nothing gets updated. If we use a UpdateSQL script it works but MSQuery generates a huge statement update statement and not only the values changed.

How can we accomplish this? BTW Only EmployeeContracts needs to be updatable, the one of the other table is readonly.

==============================

I got this reply:

This problem is connected with the peculiarity of the SQL Server work. For UNION query SQL Server doesn't return information about actual field names and about the table, to which the fields belong. Therefore you can not modify such result sets.

==============================

I don't understand this reply. Why would you need information about actual field names and about the table?

When the developer explicitly tells SDAC which table to update (in the UpdatingTable property) and which fields SDAC needs to check for changes are specified in the pfInUpdate flag of those fields.

Isn't that what the UpdatingTable field is for? Anyone else have these kind of problems?

[Dimon]

We have reproduced the problem and will investigate the possibility of changing this behaviour in the near future. As soon as we solve the problem we will let you know.

[rept]

Great news thanks!

[rept]

Any news? I still have this problem. Even more, in another project I have this problem:

This is the query:

select * from fixedtextcq
UNION
select * from fixedtext

I let it generate the insert statement:

INSERT INTO fixedtextcq
(FT_TextNL, FT_TextFR, FT_TextUK, FT_TextES, FT_TextTR)
VALUES
(:FT_TextNL, :FT_TextFR, :FT_TextUK, :FT_TextES, :FT_TextTR)
SET :FT_ID = SCOPE_IDENTITY()

But I don't get any identity back? How can I solve this?

[AlexP]

In order for an inserted record ID to be returned, you should set the TMSQuery.Options.ReturnParams property to True, as well as in the BeforeUpdateExecute event handler - set the type for this parameter to ptOutput

Code: Select all

procedure TForm4.MSQuery1BeforeUpdateExecute(Sender: TCustomMSDataSet;
  StatementTypes: TStatementTypes; Params: TMSParams);
begin
  Params.ParamByName('FT_ID').ParamType := ptOutput;
end;