Problem with UpdatingTable in Msquery

[danilo.abranches]

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]

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]

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]

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]

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]

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]

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

Thank you