ReadOnly=true and UpdatingTable is not empty

[FILLrate]

Hi.
Delphi XE 15.0.3953.35171
sDAC 6.1.6

1. Set ReadOnly property of TMSQuery to true
2. Set UpdatingTable property of TMSQuery to valid table name (a do it from automatically generated list).
3. When query is opened the error is occured:

Code: Select all

Exception class EDatabaseError with message 'Wrong UpdatingTable value - table dbo.Tpermits is unknown'.

SQL script inside TMSQuery:

Code: Select all

select 
     p.FGUID    
    ,p.PermitGroup
    ,p.FSN
    ,p.FStartDate
    ,p.FExpiryDate
    ,p.FGetDate    
    ,p.FMultiUse   
    ,p.Vehicle  
    ,p.FDiscardingDate
    ,p.FComments
    ,p.AlreadyUsed  
    ,p.PermitType
from
     VPermits p

VPermits is a View (simple selection from table TPermits with several joins).

[AndreyZ]

Hello,

SQL Server does not return table names for fields in views, it returns view names. That's why you cannot use the correct table names in the UpdatingTable property. In case of using views, you can use only view names in UpdatingTable.

[FILLrate]

Hello,

SQL Server does not return table names for fields in views, it returns view names. That's why you cannot use the correct table names in the UpdatingTable property. In case of using views, you can use only view names in UpdatingTable.

But the error only occurs when Readonly=true. If readonly=false nothing happens.

As I understand using UpdatingTable doesn’t allow me to determine updating table/view manually. Am I correct? And in this way what is the reason of using UpdatingTable property?

Please, see similar problems:
http://forums.devart.com/viewtopic.php?f=6&t=24136
http://forums.devart.com/viewtopic.php? ... ble#p48246

[AndreyZ]

But the error only occurs when Readonly=true. If readonly=false nothing happens.

It is opposite. If TMSQuery.ReadOnly is True, it means that it will be impossible to update TMSQuery. In this case the TMSQuery.UpdatingTable has no meaning, and that's why SDAC doesn't check it. But if TMSQuery.ReadOnly is False, SDAC checks TMSQuery.UpdatingTable and it must be set to one of the table names returned by SQL Server. Because SQL Server returns view names instead of table names for such queries as you posted above, you can use only view names in the TMSQuery.UpdatingTable property.

As I understand using UpdatingTable doesn’t allow me to determine updating table/view manually. Am I correct? And in this way what is the reason of using UpdatingTable property?

TMSQuery.UpdatingTable lets you choose a table to update if you have several tables in a query. Here is a code example:

Code: Select all

MSQuery.SQL.Text := 'select t1.id, t1.fld1, t2.id, t2.fld2 from table1 t1, table2 t2 where t1.id=t2.id';
MSQuery.UpdatingTable := 'table2';
MSQuery.Open;

[FILLrate]

TMSQuery.UpdatingTable lets you choose a table to update if you have several tables in a query.

From help files:

Code: Select all

If UpdatingTable is not set then the first table used in a query is assumed to be the target

So, instead of using this property you can simply put editing table in the first place (as a rule editing table goes first when joins are used).

Sorry, I thought this property would be more useful.
Thank you for reply.

[AndreyZ]

So, instead of using this property you can simply put editing table in the first place (as a rule editing table goes first when joins are used).

Yes, it is correct.

[FILLrate]

ok, thx.