Page 1 of 2
sdac 4.0.0.6 update failed (found 0 records)
Posted: Tue 24 Apr 2007 15:02
by Ludek
After update from sdac 3 to sdac 4 I'm getting this error very often, although some records really have to be updated - i'm getting this error even with UpdateKind = ukInsert!
scenario is following:
"q: TMSQuery" with CachedUpdates active, "u: TMSUpdateSQL" component assigned to this query, and in OnUpdateRecord of the TMSQuery following statement:
u.Apply(UpdateKind);
There must be something wrong with SDAC 4. Please, could you have a look at it? THX!
Posted: Wed 25 Apr 2007 07:23
by Jackson
We couldn't reproduce the problem.
Please send us (evgeniym*crlab*com) a complete small test project to reproduce the problem;
include definition of your own database objects; don't use third party components.
Also supply us the following information
- Exact version of Delphi or C++ Builder
- Exact version of SDAC. You can see it in the About sheet of TMSConnection Editor
- Exact version of Microsoft SQL Server and OLE DB provider you use. You can see it the Info sheet of TMSConnection Editor
Posted: Thu 26 Apr 2007 13:05
by apillon
I'm having the same problem with sdac 4.0.0.6.
Posted: Fri 27 Apr 2007 08:24
by Jackson
Can you specify a sequence of operations to reproduce the problem?
Posted: Fri 27 Apr 2007 15:14
by tonisanta
Do you have triggers attached to the table (after or before triggers)? Please see "Deleting all records from a MsQuery in CachedUpdate" in this forum, could be it helps. Try to set MsQuery.Options.StrictUpdate to false and check, if all your records in cacheupdate are posted / deleted correctly. Please, let me know since I would like to avoid running into the same problems.
best regards
Toni
Posted: Mon 07 May 2007 08:38
by Ludek
Yes, I have triggers (AFTER-triggers), but - as I've already written - it happens even with updatekind ukInsert! can you imagine, that an "insert into (...) values (...)' does not change any record? Me not!
now I have some time to work on this problem again and I hope to have some testing exe soon...
Posted: Mon 07 May 2007 09:51
by tonisanta
Hi Ludek,
to figure out the problem (or pseudoproblem, since it could be only a return-status-problem and not a real update or insert problem) check with the profiler the queries your ApplyUpdates launches. Try to launch them from QueryAnalyzer (or SQL management studio) and have a look on the output-messages. Could be still inserting records a trigger invokes other SQLs returning a "0 records found". Seems SDAC now checks the last returned status. It's hard to say if it would be better to check the first returned status. With Before-triggers the problem could be the same. Perhaps they should check the 'sum' of all returned messages (?) and raize the exception only if the sum=0.
Toni
Posted: Mon 07 May 2007 09:59
by Ludek
Hi toni,
It's really a trigger problem. It has nothing to do with cached updates, only the triggers. It's easily reproducable using following database schema:
Code: Select all
CREATE TABLE [eankopf](
[nr] [int] NOT NULL,
[bez] char(35),
[info] text,
PRIMARY KEY CLUSTERED
(
[nr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TRIGGER [T_EVENTI_eankopf] ON [eankopf] FOR INSERT AS
begin
raiserror ('eankopfEventI', 0, 1)
end
if I place a TMSQuery component on the form with simple select:
insert in code one record
Code: Select all
msquery1.Append;
msquery1nr.asinteger := round(random() * maxint);
msquery1bez.asString := 'nr. ' + IntToStr(msquery1nr.asinteger);
msquery1.Post;
the Post command raises this exception. without trigger, everything runs fine. With SDAC3 is also everything without problems.
to CoreLab: will this be corrected in next SDAC4 release? SDAC 4.0.0.6 is still unusable for me
(each table in my DB has such trigger)
Posted: Mon 07 May 2007 10:06
by tonisanta
set Query.Options.StrictUpdate := false
Toni
Posted: Mon 07 May 2007 11:28
by Ludek
No, that's really no solution for me. I have thousands of such queries. And the StrictUpdate options set to true helps VERY much in finding bugs in the program, so I need it set to true... I would never buy SDAC 4, if i know, that there is such big bug... I hope the 4.0.0.7 helps me
Posted: Tue 08 May 2007 10:32
by Jackson
When you execute an update statement for a table which has triggers, server sends the number of rows affected by your UPDATE or INSERT statement and by all statements executed in the trigger. So SDAC receives several numbers of affected rows after one UPDATE or INSERT statement is executed. There is no possibility to determine what number concerns to the UPDATE or INSERT statement and what concerns to other statements executed in the trigger because there are several types of triggers in SQL Server: BEFORE, INSTEAD OF and AFTER triggers. So we do not recommend you to use StrictUpdate = True mode with triggers.
Posted: Wed 09 May 2007 09:06
by Ludek
It's very bad, it makes the SDAC 4 nearly unusable for me. How is it possible, that SDAC 3 has no such problems and can apply updates for tables with triggers without any problems, with StrictUpdate set to true?!
Even old good BDE did this rowcount-test at ApplyUpdates correctly
Posted: Fri 11 May 2007 14:28
by Jackson
SDAC 3 receives only the first number of affected rows and checks it.
In SDAC 4 we have performed some optimization, and now SDAC receives the last number of affected rows.
The optimization concerns performing of UPDATE, INSERT, and DELETE operations and gives high performance increase.
Posted: Mon 14 May 2007 14:47
by Ludek
Bad, bad, bad, bad, bad... I can afford a little "worse" performance, but I can't live with this problem. Could you please make for me an option in the TMSConnection component, that the first number of affected rows will be checked? Even if it should be a bit slower than now. I find it very incorrect, that the new version is so MASSIVELY incompatible with the previous version, so that it requires very many changes in program sources to be made (thousands...). Converting BDE-SDAC 3 was really easy, but converting SDAC3->SDAC4 would be a nightmare. And the number of hard-to-find bugs, that ommitting of this check could produce
Posted: Tue 15 May 2007 13:48
by Jackson
Does it suit you to modify your triggers?
For example, if you place the following at the begining of the trigger body:
SET NOCOUNT ON
and the following at its end:
SET NOCOUNT OFF
This will solve the problem.
In such case server won't send the number of rows affected by the SQL statements, executed in the trigger body.