SQL Generator doesn't want to update VIEW

[FILLrate]

Hi.
Delphi XE 15.0.3953.35171
sDAC 6.1.6

There is a View, which contains a calculated field with trigger Instead of (upd, ins, del).

TMsStoreProc selects data from the View. For example,

Code: Select all

Select id, a, b, c from View where id = 12. 

Field "c" is calculated field.

I want to edit all three fields (a,b,c). But the SQL generator in TMsStoreProc ignores calculated field "c", as if it didn’t change (doesn't see this field) ..
Option UpdateALLFields makes update all fields ... but not calculated field! At design time Sql generator doesn't see this field in the key fields list.
If I create all fields at design time (FieldEditor->Create all fields) then the calculated field is created with the option ReadOnly = true. And changing field property ReadOnly to false or TMsStoreProc option SetFieldsReadOnly to false also doesn’t help.
I found only one solution. I have to set valid sql queries to the SQLInsert/SQLUpdate properties. Everything is OK. Calculated field is updated in right way. But it's not convenient to generate queries manually...

[AndreyZ]

Hello,

Please specify the script to create your view and tables that are used by the view.

[FILLrate]

Please specify the script to create your view and tables that are used by the view.

That is DDl script for View. I cut some code that doesn't affect the heart of the problem (...)

Code: Select all

CREATE VIEW dbo.VInsurances
WITH SCHEMABINDING
AS

select 
(...) 
   ,0 as Transport
   ,i.FVehicle TransportGUID   
   ,CAST(v.FGarageNumber as nvarchar(128))+ ' '+  m.FName + ' ' + vn.FNumber as TransportInfo
from 
	dbo.TVehicleInsurances i join dbo.TInsuranceAgents a on i.FInsuranceAgent=a.FGUID
		  join dbo.TInsuranceTypes t on i.FInsuranceType=t.FGUID
                          join dbo.TVehicles v on i.FVehicle=v.FGUID
                          join dbo.TVehicleModels m on v.FVehicleModel=m.FGUID
                          join dbo.VVehicleIDNumbers vn on vn.FVehicle=v.FGUID
                               
union all   

select 
(...)
   ,1 as Transport
   ,i.FTrackTrailer TransportGUID 
   ,m.FName + ' ' + vn.FNumber as TransportInfo
from 
	dbo.TTrackTrailerInsurances i join dbo.TInsuranceAgents a on i.FInsuranceAgent=a.FGUID
    		       join dbo.TInsuranceTypes t on i.FInsuranceType=t.FGUID
                               join dbo.TTruckTrailers tt on i.FTrackTrailer=tt.FGUID
                               join dbo.TTruckTrailerModels m on tt.FTruckTrailerModel=m.FGUID
                               join dbo.VTruckTrailerIDNumbers vn on vn.FTrackTrailer=tt.FGUID
                               
union all                               

(...)
GO

As you can see, field "Transport" is calculated.

DDl script for Trigger Instead of:

Code: Select all

CREATE TRIGGER [dbo].[VInsurances_tri] ON [dbo].[VInsurances]
WITH EXECUTE AS CALLER
INSTEAD OF INSERT
AS
declare @code int

BEGIN
	set @code= (select i.Transport  from Inserted i) 
if @code=0
  Insert into TVehicleInsurances(FGUID,FNumber,FStart,FEnd,FInsuranceType,FInsuranceAgent,FVehicle) 
    select    
      i.FGUID
     ,i.FNumber 
     ,i.FStart
     ,i.FEnd
     ,i.FInsuranceType 
     ,i.FInsuranceAgent      
     ,i.TransportGUID
  from 
  	Inserted i 
    
 else    
  if @code=1
    Insert into TTrackTrailerInsurances(FGUID,FNumber,FStart,FEnd,FInsuranceType,FInsuranceAgent,FTrackTrailer) 
      select    
        i.FGUID
       ,i.FNumber 
       ,i.FStart
       ,i.FEnd
       ,i.FInsuranceType
       ,i.FInsuranceAgent       
       ,i.TransportGUID
    from 
      Inserted i 
else
(...)

Field “Transport” helps to determine table for inserting.

TMsStroreproc update sql scripts that I set manually (and it’s works!)

Code: Select all

INSERT INTO VInsurances
  (FGUID, FNumber, FStart, FEnd, FInsuranceAgent, FInsuranceType, Transport, TransportGUID)
VALUES
  (:FGUID, :FNumber, :FStart, :FEnd, :FInsuranceAgent, :FInsuranceType, :Transport, :TransportGUID)

And finally, SQL script, that automatically generated by component (cut from SQL Server Profiler):

Code: Select all

exec sp_executesql N'INSERT INTO VInsurances
  (FGUID, FNumber, FStart, FEnd, FInsuranceAgent, FInsuranceType, TransportGUID)
VALUES
  (@P1, @P2, @P3, @P4, @P5, @P6, @P7)',N'@P1 uniqueidentifier,@P2 nvarchar(4000),@P3 datetime2(7),@P4 datetime2(7),@P5 uniqueidentifier,@P6 uniqueidentifier,@P7 uniqueidentifier','8A9FEC8E-4D8F-4ABE-A154-3B5494EA0941',N'909090','2012-05-17 00:00:00','2013-05-18 00:00:00','A0A3C8D9-A1C9-4C7F-BBC1-664D6C563C9E','0A742324-2C9F-4DFA-8204-CC1CABA57F3B','D3C579AD-22FF-4E1D-BDC1-09E93325BFEE'

Field "Transport" doesn't exists!

There are several tables including into the View. That’s why I don’t post their code here. But if it’s necessary, I can send code by e-mail.

[AndreyZ]

SDAC doesn't include calculated fields into auto-generated SQL statements because there are no correspondent to them fields in the tables on the server. In the case you described, you should use your own SQL statements in the SQLUpdate, SQLInsert, SQLDelete, etc. properties.

[FILLrate]

SDAC doesn't include calculated fields into auto-generated SQL statements because there are no correspondent to them fields in the tables on the server. In the case you described, you should use your own SQL statements in the SQLUpdate, SQLInsert, SQLDelete, etc. properties.

Thanks.. But it's not clear for me why does sdac need any information about the actual field name from the server, if I set UpdatingTable property of TMsStoreproc and Orign property of each field?
It is a pity
I'll have to write my own generator, because static script in SQLUpdate/SQLInsert/SQLDelete is unacceptable for a large field numbers (I need to update only modified fields).

Maybe in the near future Sdac will have the option of forced field updating even it's calculated?..

The problem is connected with http://forums.devart.com/viewtopic.php? ... ble#p48246

[AndreyZ]

For calculated fields SQL Server returns that they cannot be updated. That's why SDAC doesn't include them into auto-generated SQL statements. We cannot influence such SQL Server behaviour.

[FILLrate]

For calculated fields SQL Server returns that they cannot be updated. That's why SDAC doesn't include them into auto-generated SQL statements. We cannot influence such SQL Server behaviour.

I post example of updating calculated field in my previous post. Trigger “instead of” works fine! So it will be nice to include all fields into sdac auto-generated SQL statements.

Sorry, but I don’t understand yet why sdak needs to get information about tables and fields from the server when a developer sets everything manually. Simply determine which fields were changed, get their names from Tfield classes, and table name from UpdatingTable property… Just build script! What information is needed any more?

[AndreyZ]

SDAC needs information about fields and tables for automatic SQL statements generating. If auto-generated SQL statements cannot provide the functionality you need, you should use your own statements. For such cases SDAC has the SQLDelete, SQLInsert, SQLUpdate, SQLRefresh, and SQLLock properties.

[FILLrate]

SDAC needs information about fields and tables for automatic SQL statements generating. If auto-generated SQL statements cannot provide the functionality you need, you should use your own statements. For such cases SDAC has the SQLDelete, SQLInsert, SQLUpdate, SQLRefresh, and SQLLock properties.

Ok, thx.