Knowing if an Operation is Successful in MySql

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;
[download]

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;}
[download]

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.

Comment on Knowing if an Operation is Successful in MySql Select or Download Code

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.` [download] 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)	[reply] [d/l]
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; }` [download] This will tell you if you actually inserted anything: `if ( 0 + $dbh->do( $command ) ) { &success; } else { &failure; }` [download]	[reply] [d/l] [select]
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.	[reply] [d/l] [select]