MS SQL updatable Views

[FILLrate]

MS SQL Server supports updatable views.
For example, we have a table TCustomers (Fid, FName) and view VCustomers (select Fid, FName from TCustomers)
So, we can write

Code: Select all

update VCustomers set FName = "Igor" where FId = 18 

and it will work. It’s very useful feature when user have restricted access to the tables (deny select / insert / update / delete for table TCustomers, but allow to do it with view VCustomers).

So, the problem is SDAC replaces the name of the view with appropriate table. If I write query like

Code: Select all

select * from VCustomers,

and try to modify data, SDAC will generate code like

Code: Select all

update TCustomers set FName = "Igor" where FId = 18. 

VCustomers was replased with TCustomers
Of course the server will reject the transaction, because user doesn’t have permission to edit table TCustomers.
I wrote about a similar problem before (SQL Generator doesn't want to update VIEW http://forums.devart.com/viewtopic.php?f=6&t=24136)

I have to reading the forum and a little digging in the Sdac source code. So I found solution.
When query is opened, I manually replace table TCustomers with view VCustomers

Code: Select all

TDBAccessUtils.GetTablesInfo (TCustomDADataSet (DataSet)). Items [0]. TableName: = 'VCustomers'; 

After that sdac generated code is true:

Code: Select all

update VCustomers set FName = "Igor" where FId = 18 and. 

The question in the following. Is it possible to disable replacing view with table? If not, how can I get origin view name (VCustomers) for a query instead of manually replacing it (TDBAccessUtils...TableName: = 'VCustomers'). May be sdac has a function like GetOrignUpdateTable?

[AndreyZ]

You can avoid this problem by providing your own SQL statements in the SQLUpdate, SQLInsert, SQLDelete, etc. properties. For example:

Code: Select all

MSQuery.SQL.Text := 'select * from VCustomers';
MSQuery.SQLUpdate.Text := 'update VCustomers set FName = :FName where FId = :FId';

For more information, please read the SDAC documentation.

[FILLrate]

We discussed your suggestion in my previous request. I don’t want to set SQLUpdate, SQLInsert, SQLDelete because:
1. It’s require to write down a lot of sql code inside of client application.
2. SQLUpdate, SQLInsert require to define all column. It’s unacceptable because user change only small part of them and it’s not necessary to send to server all column values (especially long string/blobs).
3. Writing my own dynamic sql generator does not make sense, because it was already done in sdac.

I need only SUPPORT OF EDITABLE MS SQL VIEW BY SDAC without writing down tons of code. I found one of the possible solutions, but as I described above it’s not so convenient for me.

[AndreyZ]

To generate SQL statements automatically, SDAC uses the meta information that is returned by SQL Server. When you use the query

Code: Select all

select * from VCustomers

, SQL Server returns the table name TCustomers. That is why auto-generated SQL statements use the TCustomers table name instead of VCustomers.
We will investigate this problem and ways to avoid it.

[FILLrate]

Ok, thx.

[AndreyZ]

If any other questions come up, please contact us.