If you specify the IGNORE keyword in an INSERT statement, errors that occur while executing the statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the error is ignored and the row is not inserted. Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest value values and inserted; warnings are produced but the statement does not abort. You can determine with the mysql_info() C API function how many rows were inserted into the table.

my $q   = 'INSERT INTO some_table (data) VALUES (?)';
my $sth = $dbh->prepare($q);

while(my $data = $csv->get_next_line()) {
    eval{
        $sth->execute($data);
    }

    if($@) {
        next;
    }

    # More code here...
}
[download]

You'll need to set local $dbh->{RaiseError} = 1; above your while-loop for that to guarantee working in all situations. And, since you're using local, I'd recommend a bare-block.

my $q   = 'INSERT INTO some_table (data) VALUES (?)';
my $sth = $dbh->prepare($q);
{
    local $dbh->{RaiseError} = 1;
    while(my $data = $csv->get_next_line()) {
        eval{
            $sth->execute($data);
        }

        if($@) {
            next;
        }

        # More code here...
    }
}
[download]

Of course, with RaiseError set to false, you can also do the more readable:

while ( my $data = ... ) {
    $sth->execute( $data )
        or next;
}
[download]

It's up to you and the style you choose to work with. Either way, be consistent. (You wrap your execute(), but not your prepare() ... tsktsk!)

---

My criteria for good software:

1. Does it work?
2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?

On my Microsoft Access database the following works:

#Check for duplicate insert into database.
if ($dbh->errstr()=~/SQL-23000/)
    {
    $template->param(duplicate => 1);
    }
[download]

If during debug, you set $dbh->{RaiseError} =1, your logs will show what errstr MySQL is throwing... checking for that (instead of SQL-23000 as in my case) should fix this issue...

2005-10-01 Retitled by Chady, as per Monastery guidelines

Instead of doing "if ($@)", it is better to do what r_rajesh suggested. Although you want to ignore the duplicate key situation, you still want to deal with other errors.

jdtoronto