Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello

I have a loop to create a database record from each line read in from a text file. I wanted to make sure the code will continue with the rest of the inserts if there is an error in one of the inserts but I'm not sure if I have done this right. I've just replaced the code of the outer loop with pseudocode. 

my $dbh = DBI->connect("DBI:mysql:database=$sql_database;host=$sql_hos
+t;port=$sql_port", $username, $password,{'RaiseError' => 1});
    
loop {


                
unless ($dbh->do($insertstr, undef, $slice->seq_region_name(), $vf->st
+art $vf->variation_name(),  $vf->allele_string() )) {
    print INSERTLOG "failed to insert", , "\n";

} #end unless
}#end loop

[download]
thanks for your help

Replies are listed 'Best First'.
Re: beginner - looping to insert a set of database records
by kennethk (Abbot) on Nov 12, 2010 at 16:29 UTC
    Your pseudocode above suffers from a problem: in creating your database handle, you set RaiseError to true. This means that your script will die on any database error, which is exactly what you don't want. At a bare minimum, you would likely want something closer to: 

    my $dbh = DBI->connect("DBI:mysql:database=$sql_database;host=$sql_hos
+t;port=$sql_port",
                       $username,
                       $password,
                       {
                            RaiseError => 0,
                        }
                       )
    or die "DB connect failed: $DBI::errstr";
    
for my $record (@records) {
    unless ($dbh->do($insertstr,
                     undef,
                     $slice->seq_region_name(),
                     $vf->start $vf->variation_name(),
                     $vf->allele_string()
            )) {
        print INSERTLOG "failed to insert", , "\n";

    } #end unless
}#end loop

    [download]

    However, if you are doing multiple inserts, I would suggest preparing a single statement outside the loop and then binding parameters and executing inside the loop. You'll also likely want to use $DBI::errstr to output more useful diagnostics. This is all discussed in DBI. See RaiseError, Placeholders_and_Bind_Values, $DBI::errstr, and PrintError.

[reply]
[d/l]
[select]
      thanks
[reply]
Re: beginner - looping to insert a set of database records
by Anonymous Monk on Nov 12, 2010 at 16:22 UTC
    from the author, i now know that doesn't work - it exits if there is an error, but I don't know how to get it to do what i want.
[reply]

Back to Seekers of Perl Wisdom