Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Can I get the actual error for DBI->execute() ?

by SergioQ (Scribe)
on Jun 21, 2024 at 01:43 UTC ( [id://11160139]=perlquestion: print w/replies, xml ) Need Help??

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

I have the function below that I use in perl. I build the command elsewhere as it could be a SELECT, INSERT, et al.

In one instance I am using the function to insert new records into my MySQL table. And generally it works. But occasionally it will fail on an insert, and I was hoping I could find out what the actual reason an insert failed. To be clear I am inserting over a thousand records into this table, and most work.

I even had my perl script print out the actual command that returns a nil on the result, and then I copy and paste that exact command into MySQL's terminal where it works. So even more reason to get the exact reason it failed.

I should add that the table has no index, and no unique fields.

#from elsewhere in my code ... $db=DBI->connect('DBI:mysql:invoices',[username],[password], { RaiseEr +ror => 0, AutoCommit => 1 }); #$rawDB is set to the table name my $insertCommand = "INSERT INTO $rawDB (sheet_no, sheet_name, item, c +oll, roww, category) VALUES ('$currentSheet', '$sheetname', '$cellData', '$curcol', '$curro +w', '');"; my $result = insertItem($insertCommand); #.... sub insertItem { (my $insertCom) = @_; $dbHandle=$db->prepare($insertCom); my $result = $dbHandle->execute(); return $result; }

Replies are listed 'Best First'.
Re: Can I get the actual error for DBI->execute() ?
by hippo (Archbishop) on Jun 21, 2024 at 08:08 UTC
    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.


    🦛

Re: Can I get the actual error for DBI->execute() ?
by Marshall (Canon) on Jun 21, 2024 at 05:16 UTC
    You could put something like this:
    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; }
    Or this will accomplish basically the same thing:
    $db=DBI->connect('DBI:mysql:invoices',[username],[password], { RaiseEr +ror => 1, AutoCommit => 1 });

      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); }

      $ perl dietest.pl Bla at dietest.pl line 14.

      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); }

      $ 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

      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; }

      $dbInsert=$db->prepare($insertCom) or die "DBI prepare failed! : $sth- +>err : $sth->errstr";
      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}";

        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.

Re: Can I get the actual error for DBI->execute() ?
by soonix (Canon) on Jun 21, 2024 at 06:48 UTC

    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 bobby tables 😉

      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.
Re: Can I get the actual error for DBI->execute() ?
by clueless newbie (Curate) on Jun 21, 2024 at 11:40 UTC

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11160139]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others scrutinizing the Monastery: (4)
As of 2024-07-16 20:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?
    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.