Greenhalgh David wrote:
> 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";
The fact that you don't have single quotes around $player is highly
suspicious. I don't see any error checking. Make sure you have RaiseError
turned on.
I strongly suggest you use placeholders rather than interpolating values
directly into the query.
Have you done what I suggested and constructed a minimal example to make
sure the database is working correctly?
> 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]
