From: Paul Lutus <nospam@...>
Date: 2006-09-27T02:55:10+09:00
Subject: Re: Commit when error

Jose Montero wrote:

> Ok, this is my problem:
> Im loading around 900,000 records from a file to my database.
> So, i commit after it has processed 10,000 with something like this:
> 
> if (count % 10000) == 0
>     @dbh.commit
> end
> 
> (Where the variable count is the number of lines loaded.)
> 
> And now i have this code that process each line:
> 
> begin
>     executeQuery(query)
> rescue
>     $stderr.puts "err: " + $!.to_s
>     @dbh.commit
> end
> 
> Here is the problem:
> For example, if the programm is loading the 12,000 line and this
> executeQuery method produces an error, the lines 10,000 to 12,000 DONT
> load to the database. Because i commit after 10,000 lines, so those
> 2,000 lines havent got commit. So, what i want is that when the
> executeQuery produces an error, ok, dont load that line, but i want to
> commit the rest of lines that were loaded before.
> I putted the "@dbh.commit" in the rescue block, but doesnt work. What
> can i do? Where should i put a @dbh.commit in my code?

Why not solve the problem instead of trying to work around it? Why not
commit on each read record, then commit on groups of 100 read records,
gradually increasing the commit interval until you come up with some clues
to the problem?

Obviously, once an error condition comes up, trying to commit then is too
late -- the prevailing error condition prevents the commit from being
carried out.

-- 
Paul Lutus
http://www.arachnoid.com