Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Knowing if an Operation is Successful in MySql

by drewboy (Sexton)
on Nov 14, 2003 at 12:28 UTC ( #307030=perlquestion: print w/replies, xml ) Need Help??

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

Greetings, Perl Monks.

I have a perl script that connects to an SQL database and adds a new row to it:

$dbh = DBI->connect("DBI:$driver:$database:$server:$port",$db_user,$db +_pass); die "DBI error from connect:", $DBI::errstr unless $dbh; $sth = $dbh->prepare($command); die "DBI error with prepare:", $sth->errstr unless $sth; $command = "insert into cardbase (id, picture) values ('".$id."', '".$ +picture."')"; $result = $sth->execute; die "DBI error with execute:", $sth->errstr unless $result; $sth->finish; $dbh->disconnect; &success;
For this script to work smoothly, I need this particular operation to succeed all the time. However, on some occassions, I run the script, get the &success page but nothing's been added to the sql table. I wonder if there's a way in Perl to find out if an insertion of data was successful, such that (hypothetically):
if ($insert_ok) { &success;} else {&failure;}
Thank you for your time and help:-)

Note of THANKS:

Thanks Taulmarill, ehdonhon and jeffa for your help! I looked into your suggestions and they really helped me solve my problem.

Replies are listed 'Best First'.
Re: Knowing if an Operation is Successful in MySql
by jeffa (Bishop) on Nov 14, 2003 at 13:31 UTC
    You really should just connect with RaiseError set to true and you won't have to constantly keep checking for errors every step of the way:
    my $dbh = DBI->connect( "DBI:$driver:$database:$host", $user, $pass, {RaiseError => 1}, ); my $sth = $dbh->prepare(' INSERT INTO cardbase (id,picture) VALUES (?,?) '); my $result = $sth->execute($id,$picture); # etc.
    As for whether or not the row was actually inserted or not, i usually set up my database appropriately. You really should set up your cardbase table to use an auto incremented id as a primary key. That way, you don't insert the id yourself, and you won't have to worry about duplicates, which is usually the only reason why the row would not be insertable in the first place. Also note that i am not interpolating $id and $picture inside the SQL query string, but instead i am declaring them as placeholders and i pass the actual variables to execute() which will quote them for me. This is another potential spot where a query could fail.

    Do yourself a big favor and read DBI recipes today. :)

    UPDATE: and Use placeholders. For SECURITY! as well ...

    jeffa

    L-LL-L--L-LL-L--L-LL-L--
    -R--R-RR-R--R-RR-R--R-RR
    B--B--B--B--B--B--B--B--
    H---H---H---H---H---H---
    (the triplet paradiddle with high-hat)
    
Re: Knowing if an Operation is Successful in MySql
by ehdonhon (Curate) on Nov 14, 2003 at 12:59 UTC

    This will tell you if your command ran successfully.

    if ( $dbh->do( $command ) ) { &success; } else { &failure; }

    This will tell you if you actually inserted anything:

    if ( 0 + $dbh->do( $command ) ) { &success; } else { &failure; }
Re: Knowing if an Operation is Successful in MySql
by Taulmarill (Deacon) on Nov 14, 2003 at 12:50 UTC
    as taken from DBI module description:

    $str = $h->state;
    Returns an error code in the standard SQLSTATE five character format. Note that the specific success code 00000 is translated to '' (false). If the driver does not support SQLSTATE (and most don't), then state will return S1000 (General Error) for all errors.

    you could also try $h->err; or $h->errstr;. just see the DBI documentation for more information.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (4)
As of 2021-03-07 16:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favorite kind of desktop background is:











    Results (122 votes). Check out past polls.

    Notices?