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

i am new in perl, met a simple question and still have not solved by searching on internet. 
$sql = "INSERT INTO t1 (lsname) VALUES ('$lsname')";
$sth = $dbh->prepare( $sql );
$sth->execute;
# here i want to print out a error message when the $sql could not be 
+execute or met problem.

[download]
i am doing a inporting act from excel for sql database by perlscript, some cells could not be inported in on occation. there are thousands lines need inport, i want to know which line met problem when inporting.
thanks.

update (broquaint): added <code> tags

Replies are listed 'Best First'.
Re: simple question about printing sql 2000 error message
by gmax (Abbot) on Oct 07, 2003 at 06:59 UTC

    The best way of catching errors in a DBI operation is through eval.

    my $dbh = DBI->connect("dbi:driver:database",
    "user","password", 
    {RaiseError=>1, PrintError=>0})
      or die "can't connect ($DBI::errstr)\n";

my $sth;
eval {
    $sth = $dbh->prepare( $sql );
    $sth->execute;
};

if ($@) {
     print "There was an error ($DBI::errstr)\n";
     # do something appropriate
}

    [download]

    Notice that RaiseError must be on, to be sure it will raise an exception in case of error. The DBI docs have all the details of this procedure.

    I believe you would benefit from a walk through our Tutorials about database programming. Start with this one.

    As a side note, instead of using "INSERT INTO t1 (lsname) VALUES ('$lsname')"; consider the placeholder mechanism, which will make sure that your field is properly quoted.

    my $sql = "INSERT INTO t1 (lsname) VALUES ( ? )";
my $sth;
eval {
    $sth = $dbh->prepare( $sql );
    $sth->execute ($lsname);
};

if ($@) {
     print "There was an error ($DBI::errstr)\n";
     # do something appropriate
}

    [download]
     _  _ _  _  
(_|| | |(_|><
 _|

    [download]
[reply]
[d/l]
[select]
Re: simple question about printing sql 2000 error message
by DrHyde (Prior) on Oct 07, 2003 at 07:59 UTC
    execute() returns false if there is an error. You can catch that like so:

    $sth->execute() || die("Divide By Cucumber Error\n");

    On another note, you really should be using placeholders rather than interpolating $lsname directly into your SQL. Consider what would happen if the variable contained an apostrophe. Or if it contained foo');update t1 set lsname='you are owned

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom