sdac 4.0.0.6 update failed (found 0 records)

Ludek · Post by **Ludek** » Tue 24 Apr 2007 15:02

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!

Jackson · Post by **Jackson** » Wed 25 Apr 2007 07:23

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

apillon · Post by **apillon** » Thu 26 Apr 2007 13:05

I'm having the same problem with sdac 4.0.0.6.

Jackson · Post by **Jackson** » Fri 27 Apr 2007 08:24

Can you specify a sequence of operations to reproduce the problem?

tonisanta · Post by **tonisanta** » Fri 27 Apr 2007 15:14

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

Ludek · Post by **Ludek** » Mon 07 May 2007 08:38

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...

tonisanta · Post by **tonisanta** » Mon 07 May 2007 09:51

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

Ludek · Post by **Ludek** » Mon 07 May 2007 09:59

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:

Code: Select all

select * from eankopf

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)

tonisanta · Post by **tonisanta** » Mon 07 May 2007 10:06

set Query.Options.StrictUpdate := false
Toni

Ludek · Post by **Ludek** » Mon 07 May 2007 11:28

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

Jackson · Post by **Jackson** » Tue 08 May 2007 10:32

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.

Ludek · Post by **Ludek** » Wed 09 May 2007 09:06

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

Jackson · Post by **Jackson** » Fri 11 May 2007 14:28

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.

Ludek · Post by **Ludek** » Mon 14 May 2007 14:47

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

Jackson · Post by **Jackson** » Tue 15 May 2007 13:48

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.