I am inserting over a thousand records into this table

In that case you should really consider just calling prepare() once and then reuse the resulting statement handle multiple times. This will also get you the placeholder benefits to which soonix alluded.

If you don't want to set PrintError => 1 in the connection arguments then have a read of the METHODS COMMON TO ALL HANDLES section of the DBI POD which starts off by explaining how to obtain error codes and messages.

sub insertItem {
    (my $insertCom) = @_;
    
    $dbInsert=$db->prepare($insertCom) or die "DBI prepare failed! : $
+sth->err : $sth->errstr";
    my $rows_inserted = $dbInsert->execute();
    
    if ( $dbInsert->err )
    {
      die "DBI ERROR Insert Failed! : $sth->err : $sth->errstr";
    }
    
    return $rows_inserted;
}
[download]

$db=DBI->connect('DBI:mysql:invoices',[username],[password], { RaiseEr
+ror => 1, AutoCommit => 1 });
[download]

Just a quick note: It might be helpful to use croak() from the Carp module instead of die(). That way, OP would get a backtrace to where the error originated and what the function arguments were.

With die:


#!/usr/bin/env perl

use v5.40;

do_something();

sub do_something() {
    broken_function(10);
    broken_function(17);
    return true;
}

sub broken_function($val) {
    die("Bla") unless($val == 10);
}
[download]




$ perl dietest.pl 
Bla at dietest.pl line 14.
[download]

With croak:


#!/usr/bin/env perl

use v5.40;

use Carp qw(croak);

do_something();

sub do_something() {
    broken_function(10);
    broken_function(17);
    return true;
}

sub broken_function($val) {
    croak("Bla") unless($val == 10);
}
[download]




$ perl dietest.pl 
Bla at dietest.pl line 16.
    main::broken_function(17) called at dietest.pl line 11
    main::do_something() called at dietest.pl line 7
[download]

So, in your example, that would be:


...
use Carp qw(croak);
...

sub insertItem {
    (my $insertCom) = @_;
    
    $dbInsert=$db->prepare($insertCom) or die "DBI prepare failed! : $
+sth->err : $sth->errstr";
    my $rows_inserted = $dbInsert->execute();
    
    if ( $dbInsert->err )
    {
      croak("DBI ERROR Insert Failed! : $sth->err : $sth->errstr");
    }
    
    return $rows_inserted;
}
[download]

PerlMonks XP is useless? Not anymore: XPD - Do more with your PerlMonks XP
Also check out my sisters artwork and my rather simple sketches/one-panel comics

$dbInsert=$db->prepare($insertCom) or die "DBI prepare failed! : $sth-
+>err : $sth->errstr";
[download]

Be warned this will interpolate $sth, and not call err etc on it. Instead, do:

$dbInsert=$db->prepare($insertCom)
  or die "DBI prepare failed! : ${\$sth->err} : ${\$sth->errstr}";
[download]

Looks like $sth was an invention in Re: Can I get the actual error for DBI->execute() ?. Anyway, my suggestion would be

$dbInsert=$db->prepare($insertCom)
  or die join(" : ", "DBI prepare failed!",$db->err, $db->errstr);
[download]

which also would make it easier to extend the error message, if needed.

Unfortunately, not even that will work seeing as how $sth hasn't even been declared.

---

🦛

Yes quite correct. $sth came from some other code via copy and paste and is an error on my part. $DBI::err and $DBI::errstr would be the appropriate vars to use here.

Once again, I prefer RaiseError in most circumstances. In most cases, there is nothing that the program can do about a fatal DB error. It usually makes the most sense to just stop. The OP showed very little of his code and even less explanation. It should be noted that "prepare" can be an "expensive" operation and, therefore most code attempts to prepare a statement once and use that statement many times.

Perhaps it could be that whatever is driving the generation of multiple identical tables with different names, this can be simplified to a single table table with one extra column to represent that factor. That way you could have a single prepared statement that works with what were before multiple tables and now is a single table? The table name cannot be a variable in a prepared statement.

My (un)educated guess is the command immediately preceding the failing one.

Perhaps a stray quote or backslash in one of the $celldata or something like this -- think

A stray quote was exactly the issue, and I have taken care of that. But I would still like to catch any errors, without dying, reporting themm back to the enduser, and then exit the script gracefully.

I'm surprised nobody else came right out and said this yet: Your code is vulnerable to SQL injection. You should never interpolate Perl variables into SQL because DBI gives you a placeholder system that solves the problem the right way. Write your query with "?" for each value and then plug the values into the execute call.

You have RaiseError => 0. Set it to 1 and wrap it in an eval. I'd also recommend using placeholders instead of interpolating variables in strings.