Creole on PHP 5.2.4 with MSSQL

Working in a University or any organization generally leaves you supporting a lot of old code, on even older systems. As part of our web services infrastructure virtualisation project I’ve been bringing a lot of old code on to new systems. As can be seen when I got PHP 5.2.4 running on Ubuntu 12.04 LTS Precise.

As it turns out, our code wasn’t running so smoothly on PHP 5.2.4 as Arthur Koziel points out there is a problem with Creole and PHP 5.2.4. While we didn’t have the exact same problem, his post highlighted where it could be fixed.

Our problem was that we have a third party product using a Microsoft SQL database. It either always had, or just started to spit out date time values in the following format: Nov 14 2012 07:30:00:000PM note the non standard specification of microseconds. (Not sure which way around the problem occurred because we upgraded a month or so ago, but only just got a report of the problem.)

PHP’s strtotime really doesn’t like that format, and I can see why, horrible MSSQL!

I saw that I could fix this in creole/drivers/mssql/MSSQLResultSet.php by providing an alternate getTimestamp function and using a bit of strptime and sprintf magic. Below is the code I used to fix it. This might not be the most elegant solution, but it works!


/**
* @see ResultSet::getTimestamp()
*/
public function getTimestamp($column, $format = 'Y-m-d H:i:s')
{
$idx = (is_int($column) ? $column - 1 : $column);
if (!array_key_exists($idx, $this->fields)) { throw new SQLException("Invalid resultset column: " . $column); }
if ($this->fields[$idx] === null) { return null; }

$ts = strtotime($this->fields[$idx]);
if ($ts === -1 || $ts === false) { // in PHP 5.1 return value changes to FALSE
// in PHP 5.2.4 this no longer works, MSSQL provides date time as
// Nov 14 2012 07:30:00:000PM
$tsUgly = strptime($this->fields[$idx], '%b %d %Y %I:%M:%S:000%p');
$tsNicer = sprintf('%04d-%02d-%02d %02d:%02d:%02d',
$tsUgly['tm_year'] + 1900, // This will be years since 1900, so we need to add 1900.
$tsUgly['tm_mon'] + 1, // This will be the month 0-11, so we add one.
$tsUgly['tm_mday'],
$tsUgly['tm_hour'],
$tsUgly['tm_min'],
$tsUgly['tm_sec']);
$ts = strtotime($tsNicer);
if ($ts === -1 || $ts === false) { // in PHP 5.1 return value changes to FALSE
throw new SQLException("Unable to convert value at column " . $column . " to timestamp: " . $this->fields[$idx]);
}
}
if ($format === null) {
return $ts;
}
if (strpos($format, '%') !== false) {
return strftime($format, $ts);
} else {
return date($format, $ts);
}
}

I hope that helps anyone else out there maintaining old code on newer systems. If anything it’ll help me if I come across a similar problem again!

Steve Daniels

One thought on “Creole on PHP 5.2.4 with MSSQL”

Comments are closed.