On Tuesday, July 8, 2003, at 01:19 pm, Bob Showalter wrote:
Greenhalgh David wrote:... In other words, my SELECT block is returning the value of available as it was before the UPDATE, even though I have AutoCommit set to 1, the UPDATE is called before the SELECT and manual investigation of the table clearly shows that the UPDATE was successful. This suggests to me that even though AutoCommit is on, the commit does not actually happen until the $dbh is released and the connection to the database cut.
The setting of AutoCommit shouldn't matter. Your session should see all
updates immediately. Commits only affect the visisbility of updates to other
sessions. Something else is going on.
Start by constructing a very simple, stripped down example that updates a
row and then selects that row again so we can make sure the database is
behaving properly. Verify that that example works regardless of the
AutoCommit setting.
I suspect something is amiss in the logic of your program that you just
aren't seeing. Try stripping it down to the minimum code that still exhibits
the problem and post the complete code.
OK, as requested, I have stripped out most of the script and produced the bare minimum. The effect is the same, $before gives today's date, not a date 8 days in the future. However, when the DB is interogated after the script has run the available field is correct and really does give a date 8 days in the future. (Note - the available column is set to todays date in another script that is called before this one runs. In the full version of the code, the first script is re-called after this one runs and correctly displays the expected result. It is only when calling the SELECT inside the same script as the update that i get problems.)
#!/usr/local/bin/perl -wT
use strict; use CGI ':standard'; use DBI; use CGI::Carp qw(fatalsToBrowser);
# Simulate a param call in CGI
my $player="HarryPotter";
# Connect to the database
my $dbh = DBI->connect("DBI:mysql:ladderDB", "xxxx", "xxxxxx");
# Make an update that is representative of the full script
my $offset=8;
my $updatehandle1 = $dbh->do("UPDATE league SET status = 1, available = DATE_ADD(NOW(), INTERVAL '$offset' DAY) WHERE user_name = '$player'");
# Try to select the new value of available
my $availquery = "SELECT available FROM league WHERE user_name=$player";
my $availhandle=$dbh->prepare($availquery);
$availhandle->execute;
my($before) = $availhandle->fetchrow_array; # Full version selects email addresses etc in this block
$availhandle->finish;
# See what came out into $before
print "Content-Type: text/html\n\n";
print "Query return is $before";# Always prints todays date. Expected return is today + 8
-- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
