Page 1 of 1
Problem with UpdatingTable in Msquery
Posted: Mon 08 Sep 2014 17:03
by 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
Re: Problem with UpdatingTable in Msquery
Posted: Tue 09 Sep 2014 08:38
by 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.
Re: Problem with UpdatingTable in Msquery
Posted: Tue 09 Sep 2014 14:25
by 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?
Re: Problem with UpdatingTable in Msquery
Posted: Mon 15 Sep 2014 06:51
by 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;
Re: Problem with UpdatingTable in Msquery
Posted: Wed 17 Sep 2014 22:10
by 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?
Re: Problem with UpdatingTable in Msquery
Posted: Mon 22 Sep 2014 07:54
by 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
Re: Problem with UpdatingTable in Msquery
Posted: Mon 22 Sep 2014 19:53
by danilo.abranches
Yes, i read that. I do not use timestamp fields in my database. I will do so.
Thank you