Re: DB Help Request
by jonadab (Parson) on Sep 14, 2003 at 12:40 UTC
|
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$/
| [reply] [d/l] [select] |
|
no .. sorry really tired.
-- paul
| [reply] |
|
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$/
| [reply] [d/l] [select] |
Re: DB Help Request
by tantarbobus (Hermit) on Sep 14, 2003 at 14:43 UTC
|
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=''
| [reply] [d/l] [select] |
|
WHERE unsubscribedby IS NULL OR unsubscribedby=''
Thanks a lot!!
-- paul | [reply] [d/l] |
Re: DB Help Request
by dws (Chancellor) on Sep 14, 2003 at 16:16 UTC
|
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.
| [reply] [d/l] [select] |
Re: DB Help Request
by Anonymous Monk on Sep 14, 2003 at 14:45 UTC
|
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 | [reply] [d/l] [select] |
Re: DB Help Request
by sgifford (Prior) on Sep 14, 2003 at 14:37 UTC
|
In the sample row you give, "unsubscribed by" is set to 1, and so won't match the unsubscribedby = '' clause of your UPDATE statement.
| [reply] |
|
Look again -- the 1 is the subscribed value.
| [reply] |
Re: DB Help Request
by LordWeber (Monk) on Sep 14, 2003 at 13:37 UTC
|
| [reply] |
|
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
| [reply] |
|
| [reply] |
|
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.
| [reply] |
Re: DB Help Request
by vbrtrmn (Pilgrim) on Sep 15, 2003 at 17:39 UTC
|
| [reply] |