CachedUpdates and Key Violation

John Bell · Post by **John Bell** » Sun 26 Mar 2006 12:46

I am doing a bulk insert from a large CSV file which is painfully slow
unless i use starttransaction and group the inserts into 100 rows at
a time. Unfortunately, the data has duplicate keys so i get a violation
of primary key constraint occassionally. The documentation implies I
can use uaskip to skip that record and leave it in the cache. how do i
"find" that record so i can put it into a problems table and deal with
it later. How do I access just the records left in the cache? Thanks

Jackson · Post by **Jackson** » Mon 27 Mar 2006 09:17

TDataSet.UpdateStatus indicates what change, if any, is cached for the current record.
Or you can call TMemDataSet.UpdateResult to read the status of the latest call to ApplyUpdates method while cached updates are enabled
To improve performance you can insert your file into temporary table and then update with some query on server side.

John Bell · Post by **John Bell** » Mon 27 Mar 2006 15:25

Is there any way to easily identify which records are
left in the cache after I do the applyupdates. the current
record does not seem to be the one that caused the
problem (i am inserting groups of 100 and the current
record seems to be the last one in that group). the
documentation implies we can use uaskip and then
process those problem records. How do i find these
records? Is there some sort of property or method
which allows access to just the records left in the cache?
thanks.

Jackson · Post by **Jackson** » Tue 28 Mar 2006 08:37

You can use construction like this

Code: Select all

  while not MSQuery.EOF do begin
    if MSQuery.UpdateResult in [uaFail,uaSkip] then begin
      // your code here
    end;
    MSQuery.Next;
  end;

For more detailed example please see CachedUpdates demo.

John Bell · Post by **John Bell** » Tue 28 Mar 2006 15:47

That doesn't work and I HAVE looked at your demo. To be honest, your demos are not usually very helpful to me.

I am trying to import 500,000+ records from a text file with about 111 duplicate keys. I can do it stepping through the file one record at a time but it takes forever. Using StartTransaction and using AppyUpdates every 100 records speeds it up considerably but when I run into a key violation, the entire transaction gets cancelled. At this point, I would like to step through just the 100 records in the cache and somehow remove the offending record (and preferably, put it into a problems table) and then move the remaining records into the table on the server.

1) Can you access the records that are in the cache w/o accessing the ones that are on the server (in other words, can i step through the 100 records that are in the cache without stepping through the thousands that are on the server)? Please answer yes or no.

2) If Yes, how do you do it?

3) If No, how are you supposed to deal with records which supposedly remain in the cache when you use uaSkip?

Thank You.

Jackson · Post by **Jackson** » Thu 30 Mar 2006 11:56

TCustomDADataSet doesn't support such functionality. You can use another
DataSet to store unapplied changes.

John Bell · Post by **John Bell** » Sat 01 Apr 2006 19:13

May I request that you add such functionality. it would be
very useful. thanks.

However, can you give me an example of how to store
the unapplied changes. is there a clean way to do that?
thanks.

Jackson · Post by **Jackson** » Tue 04 Apr 2006 11:26

We will analyze the possibility of adding such functionality in the next SDAC version.
We don't have samples other than included in the installation package.

Guest · Post by **Guest** » Fri 07 Apr 2006 18:43

May I also suggest you change the following in the help file
which implies some of the functionality i was looking for.
thanks.

procedure CommitUpdates;

Description

Calls CommitUpdates to clear the cached updates buffer after both a successful call to ApplyUpdates and a database component's Commit method. Clearing the cache after applying updates ensures that the cache is empty except for records that could not be processed and were skipped by the OnUpdateRecord or OnUpdateError event handlers. An application can attempt to modify the records still in the cache.

Record modifications made after a call to CommitUpdates repopulate the cached update buffer and require a subsequent call to ApplyUpdates to move them to the database.