UpdatingTable not functioning properly

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
rept
Posts: 20
Joined: Mon 30 Oct 2006 09:15
Location: Belgium

UpdatingTable not functioning properly

Post by rept » Wed 26 Aug 2009 15:32

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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 27 Aug 2009 14:05

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
Posts: 20
Joined: Mon 30 Oct 2006 09:15
Location: Belgium

Post by rept » Thu 27 Aug 2009 14:08

Great news thanks!

rept
Posts: 20
Joined: Mon 30 Oct 2006 09:15
Location: Belgium

Re: UpdatingTable not functioning properly

Post by rept » Mon 20 Jan 2014 13:28

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
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: UpdatingTable not functioning properly

Post by AlexP » Tue 21 Jan 2014 10:20

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;

Post Reply