TMSQuery and Edit, Post

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
laegteskov
Posts: 15
Joined: Tue 21 Aug 2007 22:14

TMSQuery and Edit, Post

Post by laegteskov » Tue 21 Aug 2007 22:46

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

Antaeus
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Wed 22 Aug 2007 07:48

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.

laegteskov
Posts: 15
Joined: Tue 21 Aug 2007 22:14

Thanks

Post by laegteskov » Thu 23 Aug 2007 13:15

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?

Antaeus
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Post by Antaeus » Thu 23 Aug 2007 14:56

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.

laegteskov
Posts: 15
Joined: Tue 21 Aug 2007 22:14

Thanks

Post by laegteskov » Thu 23 Aug 2007 15:13

Thanks I'm back on track :D

dpoulin
Posts: 13
Joined: Fri 14 Jan 2011 16:41

having a similar behavior

Post by dpoulin » Fri 14 Jan 2011 16:59

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

Post Reply