Page 1 of 1
TMSQuery and Edit, Post
Posted: Tue 21 Aug 2007 22:46
by laegteskov
I'm a little but worried about using SELECT-queries to update data in my application after this experience. My code is this:
Code: Select all
procedure AssignCampaignAddressesToPhonerPool(ImportListID, Count : integer);
var
QueryMS : TMSQuery;
P : integer;
begin
QueryMS:=TMSQuery.Create(nil);
QueryMS.Connection:=ClientDB;
QueryMS.SQL.Add('SELECT CampaignAddressStatusID FROM CampaignCustomer WHERE ImportListID = ' + IntToStr(ImportListID) + ' AND CampaignAddressStatusID = 1');
QueryMS.ReadOnly:=False;
QueryMS.Open;
P:=0;
while (Not QueryMS.Eof) And (P1 is changed and this destroys data in the table.
I'm using SDAC ver 4.10.0.9 and Delphi 2007
Hope to get an answer - in the meantime I'm firing 1000+ single SQL-statements because I'm a bit scared to use Queries to edit data directly.
FYI a user named dragan posted a similary post Fri Aug 12, 2005 11:50 am with the subject "What is this????" but I don't think there was an answer to this.
Thanks,
Lasse
Posted: Wed 22 Aug 2007 07:48
by Antaeus
When you open a table for edit, you should make sure that it has a primary key, and all the fields included in the primary key appear in your SELECT statement.
Regarding structure of the CampaignCustomer table, you should replace this piece of you query:
Code: Select all
SELECT CampaignAddressStatusID FROM
with this one:
Code: Select all
SELECT CampaignAddressStatusID, CampaignID, CustomerID FROM
Another way to avoid this problem in the future is to set the TMSQuery.Options.UniqueRecords to True.
Thanks
Posted: Thu 23 Aug 2007 13:15
by laegteskov
Ok, thank you for the answer - I was getting a bit worried. Shouldn't the default behaviour for this be that it doesn't change data when an error occours?
Posted: Thu 23 Aug 2007 14:56
by Antaeus
SDAC cannot determine how many records will be modified before an update query has been performed. After an update command has been performed, SDAC gets from the server count of affected rows, and if the value differs from one, raises an exception. To avoid this problem, you can wrap Update/Post in a transaction.
Thanks
Posted: Thu 23 Aug 2007 15:13
by laegteskov
Thanks I'm back on track
having a similar behavior
Posted: Fri 14 Jan 2011 16:59
by dpoulin
Personnally, I do not think this is right. I use a select with only few fields I need, and because a field in my where is not in the select statement. After editing my with msquery.edit I post, and it update 2 or multiple rows.
Have a look
Select F01,F1000,F126,F1001,F2129,F2130,F2131,F30,F31,F33
from price_TAB where
(F01=:F01) and
(F126=:F126) and
(F1000=:F1000) and
(F113=:F113) and
(F2296=0) and
((F1001 IS NULL) or (F1001 < 6))
if my F113 isn't in the select statement (not the where part) (f113 is part of my key)
it can update more than 1 row even if my where point only on one row.
my query has a recordcount of 1 but it can update 2-3-4-5...
Then, it gives an error, something like update failed, 2 records found.
But it actually did updated 2 row. anyways that is the same behavior has the other post.
This is wrong, I don't see why I need to had a field if I don't use it...
Otherwise, very good product, it is mostly the only thing I see which has no sense