On Monday, July 7, 2003, at 01:12 pm, Craig Dean wrote:
On Sunday 06 July 2003 02:14 pm, Greenhalgh David wrote:A fairly complete breakdown of the development environment is:What module are you using to provide to provide the NOW() functionality?If you are assigning the value of the field "available" to a variable then you should kill or delete that variable (CGI::delete) before you are able to assign a new value to it.
Craig
No, I'm not assigning the value of available to a previously used variable. For safety's sake, I am reading back the value of available into a new variable declared with "my" just to hold this value. I'm wondering if I have the "connect" line wrong and the AutoCommit is not happening.
Dave
Perl - 5.6.1 (largely because installing 5.8 is a pain on OS X) mySQL - latest stable release, 4.0.13 Bundle::DBI - latest from CPAN DBD::mySQL - latest from CPAN UNIX - Mac OS X.2 Darwin flavour.
I am doing the date arithmetic in mySQL with a statement
$handle=$dbh->do(UPDATE league SET available=DATE_ADD(NOW(), INTERVAL $offset DAY)); #placeholders filled in for clarity
This all works and when I check the table, the date is available is correct. The next lines in the script are a basic create/prepare/execute/fetch sequence to execute the SQL command SELECT available FROM league and store the return in a new variable declared with "my" for the specific purpose. This new variable then gets printed into an email and sent to the user.
Again, the select is working properly, a value is returned and is duly e-mailed.
The problem I have is that the date returned by the select is always today's date. (I should explain that on log in the available field is updated to today's date in certain circumstances, but not in others. This functionality works beautifully!)
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. I understood from the literature that mySQL does not actually support commit prior to 3.23, but since I am working in 4 it should be OK. (Compatibility issue here since the target system runs 3.23 and is unlikely to change soon, so my script needs to be backwards compatible as well as forwards compatible should the sysadmin ever get round to updating.)
For now it has been fudged by replacing the SELECT with a SELECT that mimics the UPDATE:
$sql="SELECT(DATE_ADD(NOW(), INTERVAL $offset DAY))"; which is a cludge, but at least is easier than writing or importing a date arithmetic module since my client doesn't have date::calc on their system and seems to have a huge psychological block when it comes to installing new stuff.
Dave
-- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
