Knowing if an Operation is Successful in MySql

[drewboy]

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.

[jeffa]

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 ...

[ehdonhon]

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

[Taulmarill]

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.