RE: [Boston.pm] mysql oddity or Perl DBI?

[Ranga Nathan]

[forwarded submission from a non-member address -- rjk]


From: "Ranga Nathan" <[EMAIL PROTECTED]>
Date: Thu, 12 Apr 2001 11:22:26 -0700
Subject: RE: [Boston.pm] mysql oddity or Perl DBI?
To: "Ronald J Kimball" <[EMAIL PROTECTED]>,
   <[EMAIL PROTECTED]>
Cc: "Ranga Nathan" <[EMAIL PROTECTED]>

Thanks Ronald and others. Yes, I found that in the case of mySQL, a similar
thing happens.

Successful insert - row count 1, $DBI::errstr nothing;
Duplicate row - row count nothing, $DBI::errstr "Duplicate entry ....... for
key 1"
Successful update - row count <some value>, $DBI::errstr nothing
Update sets exactly same values - row count 0E0, $DBI::errstr nothing
Update unsuccessful - same as above.

Syntax and other errors : row count nothing, $DB::errstr  has error message.

So your logic works well, except the message is different for duplicate rows
in the case of mysql.
                     ---- good news ----
I also found that from MySQL 3.22.10 there is an IGNORE option (like INSERT
IGNORE INTO...)that removes duplicate rows and then inserts the rows. This
is exactly what I wanted...

Thanks all....!


When I want to catch this error, I generally do something like this:

  $rc = $insert_sth->execute();

  if (not $rc) {
    if ($DBI::errstr =~ /unique constraint/) {
      # special handling for unique constraint violation
    } else {
      # regular error handling
    }
  }

(RaiseError and PrintError should be off.)


As far as I know, DBI and DBD treat all database errors the same.


Ronald