Re: [Omaha.pm] Pick your brain?

[Jay Hannah <jay@jays.net>]

Michael D. Maynard wrote:
> REPLACE is a MySQL function that does a DELETE/INSERT when it finds a 
> record with a conflicting unique key rather than throwing an error.  It 
> comes in very handy when you are wanting to update records.
>
> http://dev.mysql.com/doc/refman/5.0/en/replace.html

Ahh... My software usually knows whether to INSERT or UPDATE based on a SELECT statement (typically executed some time earlier). I guess REPLACE would be handy if you don't care whether or not data already exists. 

I often find myself tweaking a few columns in a table with many columns. In those cases REPLACE doesn't strike me as very useful, since I wouldn't want to blank out all the other columns accidentally... :)

Thanks for the tip!

> The error is:  DBD::mysql::st execute failed: Column 'CampaignID' cannot 
> be null at nph-import.cgi line 395.  The column name is different in a 
> couple tables, but the error is the same.  The field is defined as not 
> null and there is a Unique index for it.
>
> RaiseError=>1, PrintError=>1.  I haven't written anything for the error 
> handling yet, but I am planning on doing that this weekend.  When I 
> print the data to STDOUT rather than doing the REPLACE, all the data is 
> there.

That's the next thing I would try. You can do very verbose error handling on that insert that fails one time out of a thousand (or whatever).

(oh... I just remembered placeholders don't work through freetds. bummer... source code would be a lot cleaner with them...)

Something like... (not tested):

my $dbh1 = ...({RaiseError => 0, PrintError => 1});   # Source connection
my $dbh2 = ...({RaiseError => 0, PrintError => 1});   # Target connection

my $sth1 = $dbh1->prepare("select col1, col2, col3 from x");
$sth1->execute;
my @row;   # ... bind variables might be cleaner here
while (@row = $sth1->fetchrow) {
  my sth2 = $dbh2->prepare("REPLACE into x (col1, col2, col3) values ('$row[0]', '$row[1]', '$row[2]')");
  $sth2->execute;
  if ($DBI::err) {
     my $error = "[" . $DBI::err . "|" . $DBI::errstr . "]";
     print "Ack! Here's our error: [$error]\n";
     print "Our source data record was: [";
     print join "|", @row;
     print "]\n";
  }
}

? Does that help? I hate errors that don't happen EVERY TIME. Good luck! :)

j
PHX in June. yee ha!