Problem with UpdatingTable in Msquery

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
danilo.abranches
Posts: 8
Joined: Mon 08 Sep 2014 16:39

Problem with UpdatingTable in Msquery

Post by danilo.abranches » Mon 08 Sep 2014 17:03

Hi guys,

I'm upgrading using TClientDataset to TMSQuery in my project and I am with the following problem:

When I report UpdatingTable property for Msquery the component ceases to insert records.

Code: Select all

msQuery.Connection: = db; 
msQuery.Close; 
msQuery.Open; 
msQuery.Insert; 
msQuery.FindField ('FIELD') AsString: = 'Test';. 
msQuery.Post; 
Have you checked the flags and providers are correct, and the unique properties were changed SQL and UpdatingTable.

Has anyone experienced this or have any idea how to solve?

Thank you

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

Re: Problem with UpdatingTable in Msquery

Post by azyk » Tue 09 Sep 2014 08:38

We couldn't reproduce the problem. Please send us (andreyz*devart*com) a complete small test project to reproduce the problem; include definition of your own database objects.

danilo.abranches
Posts: 8
Joined: Mon 08 Sep 2014 16:39

Re: Problem with UpdatingTable in Msquery

Post by danilo.abranches » Tue 09 Sep 2014 14:25

I isolated the problem and it seems that it is related to insert done in view. I created an example to simulate.

SQL:

Code: Select all

create table tb_teste (
   campo1 varchar (10) 
) 

create v_teste view 
as select * from tb_teste 
Delphi:

Code: Select all

msQuery.Connection: = db; 
msQuery.Close; 
msQuery.UpdatingTable: = 'v_teste'; 
msQuery.SQL.Text: = 'select * from v_teste'; 
msQuery.Open;
msQuery.Insert;
msQuery.FindField('campo1').AsString: = 'Test';
msQuery.Post; 
In this example, the insert was done with the NULL value in the field and the update was not done.

I need the insert is done in the view, because I have some treatments that are on view in accordance with the customer.

The TMSQuery works well with inserts in views?

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

Re: Problem with UpdatingTable in Msquery

Post by azyk » Mon 15 Sep 2014 06:51

To generate INSERT SQL query, we use metainformation about the database object, which each field belongs to. SQL Server returns only the table name, that owns the field, and doesn't return the view name. Therefore we can't use view names when auto-generating SQL queries.

To solve the problem, you should manually generate an SQL query inserting data to the view for the TMSQuery.SQLInsert property. For example:

Code: Select all

  msQuery.Connection := db; 
  msQuery.Close; 
  msQuery.UpdatingTable := 'v_teste'; 
  msQuery.SQL.Text := 'select * from v_teste'; 

  msQuery.SQLInsert.Text :=
  ' INSERT INTO v_teste ' +
  ' (campo1) ' +
  ' VALUES ' +
  ' (:campo1) ';

  msQuery.Open;
  msQuery.Insert;
  msQuery.FindField('campo1').AsString := 'Test';
  msQuery.Post;

danilo.abranches
Posts: 8
Joined: Mon 08 Sep 2014 16:39

Re: Problem with UpdatingTable in Msquery

Post by danilo.abranches » Wed 17 Sep 2014 22:10

If i do not use the UpdatingTable and use WITH VIEW_MEGADATA in creating the view i can make it work. It generates the instructions correctly.

This can create a problem?

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

Re: Problem with UpdatingTable in Msquery

Post by azyk » Mon 22 Sep 2014 07:54

In this case, when editing views, only SQL Server limitations will be applied:
for views created with WITH VIEW_METADATA, all the fields will be editable, except timestamp fields, if a view has INSTEAD OF INSERT or INSTEAD OF UPDATE triggers. More details about this can be found in the SQL Server documentation: http://msdn.microsoft.com/en-us/library/ms187956.aspx

danilo.abranches
Posts: 8
Joined: Mon 08 Sep 2014 16:39

Re: Problem with UpdatingTable in Msquery

Post by danilo.abranches » Mon 22 Sep 2014 19:53

Yes, i read that. I do not use timestamp fields in my database. I will do so.

Thank you

Post Reply