On Jul 20, 2009, at 10:09 AM, Chuck Remes wrote:
>
> I have a newbie question. A search of the ML going back to late 2007
> didn't yield any help.
>
> I have a simple table that I am doing batch inserts into via the
> Dataset#insert_multiple method. The table has a unique key, so
> obviously if I try to insert a duplicate it throws an exception:
>
> "NativeException: org.h2.jdbc.JdbcSQLException: Unique index or
> primary key violation:<snip>
>
> I'm inserting in batches of 2000 for performance reasons. I am
> assuming that if I have a duplicate anywhere in my batch of 2000, any
> rows *after* it will not get inserted due to the exception. I suppose
> it is also possible that other successfully inserted rows in that
> batch might get rolled back too.
>
> How do you recommend I detect this issue and retry the insert minus
> the duplicate row?
Solved it though there may be a more elegant way. Performance isn't
half bad...
cr
def initialize cache_size
# snip....
@uniqueness_exception_matcher = Regexp.new("Unique index or
primary key violation", Regexp::IGNORECASE)
@cleanup1 = Regexp.new(/[ (]/)
@cleanup2 = Regexp.new(/\)[A-Za-z0-9\-\[\]]*/)
@cleanup3 = Regexp.new(/'/)
end
def insert_multiple the_ary
begin
the_table.insert_multiple the_ary
rescue Sequel::DatabaseError => e
# when an error about unique rows, parse the exception message
# and delete the hash matching those values from +the_ary+ then
# retry to insert until it succeeds or we run out of rows
unless @uniqueness_exception_matcher.match(e.message)
raise
else
failed_values = e.message.split('VALUES').last.split(',').map
do |element|
element.gsub(@cleanup1, '').gsub(@cleanup2,
'').gsub(@cleanup3, '')
end
hsh = {:a => failed_values[0].to_s,
:b => failed_values[1].to_s,
:c => failed_values[2].to_s,
:d => failed_values[3].to_s,
:e => failed_values[4].to_i,
:f => failed_values[5].to_f,
:g => failed_values[6].to_s}
the_ary.delete(a)
retry if the_ary.size > 0
end
rescue org.h2.jdbc.JdbcSQLException => e
puts "H2 driver exception!"
p e.message
rescue => e
puts "Got an unknown exception"
p e.message
p e.backtrace
raise
end
the_ary.size
end
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
