MSQuery on Views

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Aufhauser
Posts: 56
Joined: Mon 21 Nov 2005 09:44
Location: Herzogenburg, Austria

MSQuery on Views

Post by Aufhauser » Wed 24 Dec 2014 09:25

Is there a way, to force MSQuery to use the view in an update statement, not the underlying table?
My view has instead of triggers so it is important, to use the view and not the underlying table in update-statements. Using dbmonitor I see, that the statements of MSQuery always use the underlying table even if I set the UpdatingTable property.
I think using the underlying table is bad design because in most cases there is a good reason for using a view, not a table.
Thanks for help
stefan

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: MSQuery on Views

Post by azyk » Fri 26 Dec 2014 11:45

Yes, there is a way to make TMSQuery use the view name instead of underlying table name when auto-generating an UPDATE SQL query. For this, it is required that the view is created using WITH VIEW_METADATA. For example:

Code: Select all

CREATE VIEW HumanResources.EmployeeHireDate
WITH VIEW_METADATA
AS
SELECT p.FirstName, p.LastName, e.HireDate
FROM HumanResources.Employee AS e JOIN Person.Person AS  p
ON e.BusinessEntityID = p.BusinessEntityID ;

GO
To generate an UPDATE SQL query, we use the meta-information about the database object, which possesses each field. If the view was created without using WITH VIEW_METADATA, then SQL Server returnes just the name of the table, which possesses the field, and doesn't return the view name. Therefore, in such cases, we are unable to use the view name when auto-generating SQL queries.

Aufhauser
Posts: 56
Joined: Mon 21 Nov 2005 09:44
Location: Herzogenburg, Austria

Re: MSQuery on Views

Post by Aufhauser » Mon 29 Dec 2014 10:05

Thank you for answer, changing the views works.
One question: SDAC don't use UpdatingTable property, why?

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: MSQuery on Views

Post by azyk » Tue 30 Dec 2014 09:23

SDAC uses the UpdatingTable property. If in your case SDAC doesn't use this property correctly, please describe in details the steps to reproduce the case using the SDAC demo project. If the problem can't be reproduced on our demo, try to compose a small sample to demonstrate the problem, including a script to create test tables and send it to andreyz*devart*com .

Note: SDACDemo project is in the "Demos\SDACDemo\" folder relatively to the SDAC demo projects installation path.

Post Reply