Page 1 of 1
ReadOnly=true and UpdatingTable is not empty
Posted: Thu 17 May 2012 14:25
by 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).
Re: ReadOnly=true and UpdatingTable is not empty
Posted: Thu 17 May 2012 15:25
by 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.
Re: ReadOnly=true and UpdatingTable is not empty
Posted: Fri 18 May 2012 06:28
by FILLrate
AndreyZ wrote: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
Re: ReadOnly=true and UpdatingTable is not empty
Posted: Fri 18 May 2012 09:33
by AndreyZ
FILLrate wrote: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.
FILLrate wrote: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;
Re: ReadOnly=true and UpdatingTable is not empty
Posted: Fri 18 May 2012 13:57
by 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.
Re: ReadOnly=true and UpdatingTable is not empty
Posted: Mon 21 May 2012 10:27
by AndreyZ
FILLrate wrote: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.
Re: ReadOnly=true and UpdatingTable is not empty
Posted: Mon 21 May 2012 12:36
by FILLrate
ok, thx.