DB Not UPDATE-ing

[vbrtrmn]

I think I've been looking at this code for too long, I'm totally missing where my error is. The code executes, but none of the rows are updated. No error is returned either, I printed the data as it went through, all the id's match up in the database. I'm not truncating the strings anywhere. I've gotta be missing something or smoking too much crack.

for (@usercodelist) {
  my $SQL = "UPDATE mytable SET subscribed = '0', unsubscribedby = 'SY
+STEM' WHERE usercode = '$_' AND unsubscribedby = ''";
  my $sth = $DBH->prepare($SQL);
  $sth->execute || die "Could not execute SQL statement ... maybe inva
+lid?<BR>$!";
  $sth->finish();
}

The @usercodelist array is a list of MD5 hashes, the database is MySQL.

Field           DataType
-----           --------
subscribed      tinyint(1)
usercode        varchar(32) binary
subscribedby    varchar(32) binary
unsubscribedby  varchar(32) binary
unsubscribedby  varchar(32) binary

Sample Row:

subscribed  usercode                         subscribed by  unsubscrib
+ed by
1           7436692b91879380d9108739eb9865f5 SYSTEM

Thanks in advance!!

--
paul

[jonadab]

Do you really have two fields named unsubscribedby? I can see how that would confuse things. Also, does unsubscribedby really equal the empty string, or is it NULL? Did you try taking your WHERE clause and using it in a SELECT to see what rows you get returned?

---

$;=sub{$/};@;=map{my($a,$b)=($_,$;);$;=sub{$a.$b->()}}
split//,".rekcah lreP rehtona tsuJ";$\=$ ;->();print$/

[vbrtrmn]

no .. sorry really tired.

--
paul

[jonadab]

Try adding a SELECT, to see whether the WHERE clause is matching the records at all...

  my $SELECT = "SELECT * FROM mytable WHERE usercode=? AND unsubscribe
+dby = ''";
  my $UPDATE = "UPDATE mytable SET subscribed = '0', unsubscribedby = 
+'SYSTEM' WHERE usercode=? AND unsubscribedby = ''";
for (@usercodelist) {
  my $sth = $DBH->prepare($SELECT);
  my $q = $sth->execute($_);
  while (my $r = $q->fetchrow_hashref()) {
    print "Matched $$_{usercode}\n";
  }
  $sth = $DBH->prepare($UPDATE);
  $sth->execute($_) 
    or die "<div>Could not execute SQL statement ... maybe invalid?</d
+iv><div>$!</div>";
  $sth->finish();
}

If you don't get any "Matched" lines, then the WHERE clause is the problem. If all the correct records get "Matched" lines, then the WHERE clause is not the problem.

---

$;=sub{$/};@;=map{my($a,$b)=($_,$;);$;=sub{$a.$b->()}}
split//,".rekcah lreP rehtona tsuJ";$\=$ ;->();print$/

[tantarbobus]

Your WHERE clause is looking for unsubscribedby = ''. Is it possible that unsubscribed by is null? If so, you will want to use

WHERE unsubscribedby IS NULL

instead. Or even better to catch both cases use

WHERE unsubscribedby IS NULL OR unsubscribedby=''

[vbrtrmn]

Turned out to be the NULL issue, the code suggested by you works great!!

WHERE unsubscribedby IS NULL OR unsubscribedby=''

Thanks a lot!!

--
paul

[dws]

code executes, but none of the rows are updated.

As people have suggested individually, one of two things is going on, and I can't tell whether you've provided enough information for us to tell which.

If the schema (as show by describe mytable) shows that unsubscribedby can be NULL, and if that column holds NULL (as opposed to an empty string), then

    where unsubscribedby=''

won't match. You'll need to write

    where unsubscribed is null

instead.

But, if that column really is holding a blank string, then changes are good that $_ doesn't hold what you think it does. Perhaps leading or trailing whitespace, or a newline on the end.

[Anonymous Monk]

I've gotta be missing something or smoking too much crack.

Well, I don't really find that beaming up effects my coding adversely, so my guess is that you're missing something.

Just a hunch, but I bet you have newlines attached to the values in @usercodelist. FWIW, you ought to prepare that statement once with a placeholder, and repeatedly execute it.

Try this:


my $SQL = "UPDATE mytable SET subscribed = '0',
                          unsubscribedby = 'SYSTEM'
           WHERE usercode = ? AND unsubscribedby = ''";

my $sth = $DBH->prepare( $SQL );

foreach my $md5 ( @usercodelist ) {
    chomp $md5; # I'm assuming a newlines is bungling your update
    $sth->execute( $md5 ) or die "Can't execute $SQL";
}


-- am

[sgifford]

In the sample row you give, "unsubscribed by" is set to 1, and so won't match the unsubscribedby = '' clause of your UPDATE statement.

[menolly]

Look again -- the 1 is the subscribed value.

[LordWeber]

[tantarbobus]

Do you know which database drivers default to AutoCommit=0? I can't think of any off the top of my head, but I would like to know which ones default to AutoCommit off, if only, because the DBI docs say that it defaults to AutoCommit on:

The "AutoCommit" and "PrintError" attributes for each connection default to "on". (See "AutoCommit" and "PrintError" for more infor- mation.) However, it is strongly recommended that you explicitly define "AutoCommit" rather than rely on the default. Future ver- sions of the DBI may issue a warning if "AutoCommit" is not explic- itly defined.

And later goes on to say that the driver should default to AutoCommit on:

Drivers should always default to "AutoCommit" mode (an unfortunate choice largely forced on the DBI by ODBC and JDBC convention

[LordWeber]

[graff]

While "AutoCommit" defaults to "true", there are cases where this default is not properly supported, and the perl/DBI programmer has to explicitly set it to "false" and then explicitly use the "commit" method as appropriate.

I ran across one such case when using Oracle 8.1.3 -- I found that a "select ... for update" would cause oracle to generate an error (don't remember what it said, exactly), unless AutoCommit was turned off. This caused some nasty diversions when we were migrating an app from Oracle 7.x, where there was no such problem.

[vbrtrmn]

Thanks everyone!!

--
paul