PHP 5.4 DateTime parameterisation regression?

Question

I have found some odd behaviour which I suspect may be a regression from the previous version of the drivers under PHP 5.3. I am running PHP 5.4.0 with the SQLSRV drivers 3.0.1.

When running a simple query (below), using a PHP DateTime object as a parameter, the parameter type myst be explicitly provided, unlike in prior versions of the driver.

This query returns no results:

<?php

$datetime	= new DateTime('2010-10-01');

$query		= "SELECT * FROM table WHERE datetime_column = ?";
$params		= array($datetime);

// sqlsrv_query, sqlsrv_fetch_object iteration omitted

?>

Whereas the same query with a specified (or string-casted) parameter returns results:

<?php

$datetime	= new DateTime('2010-10-01');

$query		= "SELECT * FROM table WHERE datetime_column = ?";
$params		= array(array($datetime, null, null, SQLSRV_SQLTYPE_DATETIME));

// Or, $datetime->format('Y-m-d') works too

// sqlsrv_query, sqlsrv_fetch_object iteration omitted

?>

Can you confirm if this is an intended change in the default SQL:PHP type mapping, or if this is a regression for either PHP 5.4 or the SQLSRV drivers 3.0?

Thanks!

Answer 1

Hi Will

This works for me, are you sure there isn't another reason for your result?

---

Rob

Answer 2

Many thanks Rob - I don't think so, my full test code with specific tables is below. Can you spot anything obvious? Ditto the above case where this works with explicit parameter type casting.

<pre>
<?php

$id 	= 58;
$date	= new DateTime('2010-10-01');

$query	=<<<SQL
		SELECT	f.id from_id,
				f.iso_code from_iso_code,
				t.id to_id,
				t.iso_code to_iso_code,
				fx.date,
				fx.value

		FROM	currency_rates fx INNER JOIN
				currencies f ON fx.from_currency_id = f.id INNER JOIN
				currencies t ON fx.to_currency_id = t.id

		WHERE	t.id IN (1,2,3,73) AND
				f.id = ? AND
				fx.date = ?

		ORDER BY t.id
SQL;
$params	= array($id, $date);
$result	= sqlsrv_query($this->database->connection, $query, $params);

do
{
	while ($row = sqlsrv_fetch_object($result))
	{
		print_r($row);
	}
} while (sqlsrv_next_result($result));

?>
</pre>

The connection to the database is using a SQL account, not a Windows account and the only other option specified is to use a UTF-8 CharacterSet. Of course currency_rates.date is a datetime field, I can provide the table schema if required.

I should also specify that this is for SQL Server 2008 on Windows Server 2008 with the 2012 Native Client.

Many thanks,
-Will

Answer 3

I've just tested this same code with PHP 5.3.8 and the prior version of the drivers as well (pre 3.0) and see the same regression. Perhaps now an issue with the 2012 Native Client as this is the only other intermediary software that has changed?

Thanks,
-Will

Answer 4

You could be right there - I ran it with driver version 3.0.0 on Native Client 10.0 (2008 R2).  I have not tested it with Native Client 11.0 (2012). 

I don't like the sound of this. 

If you want to test your theory that the new Native Client is the problem:

1. Uninstall Native Client 2012.
2. Make sure you have Native Client 10.0 (2008 R2) installed.
3. Install the driver from here - it's my own build of the latest PHP driver that works with any version of the Native Client: http://www.rmjohnson.demon.co.uk/PHP/sqlsrv/

---

Rob

Answer 5

Hi Rob,

Unfortunately for me (or fortunately from the point of 2012 compatability), that doesn't seem to be the issue.

The following combinations had no success:

• 5.4.0/Native Client 2008/your 3.0 driver
• 5.3.8/Native Client 2008/your 3.0 driver
• 5.3.8/Native Client 2008/SQLSRV 2.0 driver

This is particularly odd as the 5.3.8/SQLSRV 2.0/Native Client 2008 R2 combination had previously worked. Would I need to reinstall Native Client 2008 after uninstalling 2012?

Thanks,
-Will

Answer 6

Hi Will

Sorry for the late reply, I missed your post.

You wouldn't need to reinstall the 2008 client -  it is strange that your earlier test worked.

Anyway, it doesn't matter - my first test was not good enough, apologies.

Here is a script that reproduces the bug:

<?php
header('Content-type: text/plain; charset=utf-8');

if ( ($conn = sqlsrv_connect('.\SQLEXPRESS')) )
{
	$datestring = '2010-10-01 15:00';
	$datetime = new DateTime($datestring);
	$params = array( $datetime, $datetime, array($datetime,null,null,SQLSRV_SQLTYPE_DATETIME2) );
	
	$tsql = <<<SQL
	declare @test table(id int not null , dt datetime not null)
	declare @arg datetime
	set @arg = ?
	insert into @test values (1,getdate())
	insert into @test values (1,getdate())
	insert into @test values (1,getdate())
	insert into @test values (1,getdate())
	insert into @test values (58,'$datestring')
	insert into @test values (58,getdate())
	insert into @test values (59,@arg)
	insert into @test values (59,getdate())
	select 'Result using @arg (copy of plain DateTime parameter): ' [Result], * from @test where dt=@arg
	/* Bug: Next ? parameter on the next line is passed in as a string, not a datetime... */
	select 'Result using param 1 (plain DateTime parameter): ' [Result], * from @test where dt=?
	select 'Result using param 2 (explicitly-typed DateTime parameter): ' [Result], * from @test where dt=?
	 
SQL;
	
	$stmt = sqlsrv_query($conn, $tsql, $params);
	if ( !$stmt )
	{
		echo 'Query error: ' . print_r(sqlsrv_errors(), true);
	}
	else
	{
		$highlight = array(array("\r\n!!! SOMETHING IS WRONG WITH THIS RESULT !!! ----------> ", "\r\n"));
		$res=0;
		do 
		{
			echo 'Result ' . ++$res . ": ";
			$ct = 0;
			if ( sqlsrv_num_fields($stmt) > 0  )
			{
				echo "has rows: \r\n";
				while ( ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) ) 
				{
					echo 'Row ' . ++$ct . ': ' . print_r($row,true);
				}
				
				echo $highlight[$ct][0] . $ct . ' row' . ($row==1 ? '' : 's') . ' received.' . "\r\n" . $highlight[$ct][1];
			}
			else
			{
				echo "executed.\r\n";
			}
		} while ( sqlsrv_next_result($stmt) ) ;
		 
	}
}
else
	echo 'Connection error: ' . print_r(sqlsrv_errors(), true);

Here is the result - the 2nd parameter was not recognised as a DateTime:

Result 1: executed.
Result 2: executed.
Result 3: executed.
Result 4: executed.
Result 5: executed.
Result 6: executed.
Result 7: executed.
Result 8: executed.
Result 9: has rows: 
Row 1: Array
(
    [Result] => Result using @arg (copy of plain DateTime parameter): 
    [id] => 58
    [dt] => DateTime Object
        (
            [date] => 2010-10-01 15:00:00
            [timezone_type] => 3
            [timezone] => Europe/London
        )

)
Row 2: Array
(
    [Result] => Result using @arg (copy of plain DateTime parameter): 
    [id] => 59
    [dt] => DateTime Object
        (
            [date] => 2010-10-01 15:00:00
            [timezone_type] => 3
            [timezone] => Europe/London
        )

)
2 rows received.
Result 10: has rows: 

!!! SOMETHING IS WRONG WITH THIS RESULT !!! ----------> 0 rows received.

Result 11: has rows: 
Row 1: Array
(
    [Result] => Result using param 2 (explicitly-typed DateTime parameter): 
    [id] => 58
    [dt] => DateTime Object
        (
            [date] => 2010-10-01 15:00:00
            [timezone_type] => 3
            [timezone] => Europe/London
        )

)
Row 2: Array
(
    [Result] => Result using param 2 (explicitly-typed DateTime parameter): 
    [id] => 59
    [dt] => DateTime Object
        (
            [date] => 2010-10-01 15:00:00
            [timezone_type] => 3
            [timezone] => Europe/London
        )

)
2 rows received.

---

Rob

Answer 7

Hi Rob,

Thanks for producing a repro! Did you run this with your modified 3.0 driver with SNAC10, or with the 2.0 Driver with SNAC10?

Cheers,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 8

Hi Jonathan

I ran this with my build of your 3.0 driver, on Native Client 10.0 (2008 R2), against SQL Server version 2008 R2.

I also tried:

• a similar* script with driver 2.0 + SNAC 10.0 against a 2005 server and it succeeded.
• then again with driver 2.0 + SNAC 10.0 against a 2008 R2 server, and it failed (same result as posted above).

I had a look at the code and couldn't spot anything, but there are a few places that fork when the target server is 2005 or lower.

* added date_default_timezone_set('  ') near the top of the script, and changed SQLSRV_SQLTYPE_DATETIME2 to SQLSRV_SQLTYPE_DATETIME for 3rd parameter.

---

Rob

Answer 9

Thanks both for your efforts! Is there any further data or tests I can provide, or shall I leave this to be filed as a bug?

For now I have overriden my abstraction class to deal with this edge case until the driver can be updated.

Cheers,
-Will

Answer 10

I've pushed this to our support staff - we will investigate it further.

Thanks!

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.

Answer 11

Hi Will,

That is not a regression or a product issue in PHP Dirver for SQL Server or PHP. I can reproduce the issue with PHP 5.3.10 and PHP Driver 2.0 for SQL Server.

The reason for the issue is that:
1. PHP outputs the DateTime in this format: 'Y-m-d H:i:sP'. The P is the difference to Greenwich time (GMT) with colon between hours and minutes (added in PHP 5.1.3). e.g. 2010-10-01 00:00:00 +08:00
2. Without explictly format the DateTime, the datetime in format 'Y-m-d H:i:sP'(e.g. 2010-10-01 00:00:00 +08:00) will be sent to SQL Server.
3. However, this format is not supported in SQL Server, so no result is returned

If we enforce the datetime conversion from PHP Driver for SQL Server, incorrect results may be returned as the input datetime might be incorrect due to different timezone.

It is recommended to explictly convert the datetime from PHP code.

If you have any more questions, please freel free to ask.

Thanks,
Jinchun Chen

---

Jinchun Chen
Forum Support
Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff AT microsoft.com(Please replace AT with @)

Answer 12

Interesting, thanks Jinchun. I wonder why I hadn't seen this before if the behaviour was added in 5.1.3.

How might this explain the (correct) behaviour when explicitly specifying SQLSRV_SQLTYPE_DATETIME - is this because it forces the driver to use this type and truncation of the formatted DateTime is performed?

I have two servers to which I deploy our application: the older is running SQL2005 with PHP 5.3.6 and I can't reproduce the issue. The newer is running SQL2008 with PHP 5.4.0 and this is where I first noticed the change in behaviour. Might this be a combination of both the change in PHP as of 5.1.3 and a change in SQL2008?

Many thanks,
-Will

Answer 13

I've found the answer - here in the UK we moved to British Summer Time (GMT+1) on Sunday before last. Thanks Jinchun for the clue!

Of course I never saw this issue before as our server's PHP time zone (Europe/London) is GMT for most of the year, and hence this produced no issue for SQL Server. When we switched to BST, the formatted strings added +01:00.

A simple addition of date_default_timezone_set('GMT') to the scripts listed here reinstates the correct behaviour.

I'm not sure this is a "fix" per-se, but certainly not a regression issue suspected earlier.

I plan to revise my PHP code in certain areas where the DateTime values used are time zone agnostic, but I'd be interested to hear other approaches to this problem.

Thanks to everyone for all the help!

-Will

Answer 14

So it's not about the formatting of the date, but the fact that the driver handles dates differently depending on the Server version.

Is this a bug? : An identical database on SQL Server 2005 with an identical PHP application will work differently when copied to SQL 2008+.

Note to MSFT - to preserve backward compatibility you should not have changed this...  SQL DateTimeOffsets *should* be supported - but to force PHP to the *new* type should require an explicit parameter definition, not the other way round.

Perhaps you could add another connection option - 'DateTime'=>'DateTimeOffset' or something.

Either way, it shouldn't happen on its own.

As it stands, the documentation should be updated to state that a PHP DateTime object is treated as an SQL Server DateTimeOffset type - for SQL Server 2008 and higher.  For 2005 (and as stated in previous documentation) it is treated as an SQL Server DateTime type.  Date and time comparisons will behave differently.

In the following example, I fully understand why SQL Server does this, but it demonstrates the issue that DateTimeOffset should not be banded together with the non-timezone aware objects :

declare @str varchar(40) = '2012-04-04 12:00 +09:00',
		@DateTime datetime,
		@DateTimeOffset datetimeoffset

set @DateTimeOffset = @str

print '@DateTimeOffset is ' + cast(@DateTimeOffset as varchar(40))

print 'I''m copying @DateTimeOffset to @DateTime...'

set @DateTime = @DateTimeOffset

print '@DateTime is ' + cast(@DateTime as varchar(40))

print 'Let''s see if they are the same...'

if @DateTimeOffset = @DateTime
	print 'That''s good - dates are the same!'
else
	print 'That''s not good - dates are different!'

Result is:

@DateTimeOffset is 2012-04-04 12:00:00.0000000 +09:00
I'm copying @DateTimeOffset to @DateTime...
@DateTime is Apr  4 2012 12:00PM
Let's see if they are the same...
That's not good - dates are different!

@Will - personally because of the timezone unawareness of previous SQL Servers I have always stored all dates as UTC, so I have not noticed this problem, even though it has existed since version 2.0 of the driver.  That was a great find.

---

Rob

Answer 15

Interesting, thanks for the clarification Rob (particuarly on why the cast to SQLSRV_SQLTYPE_DATETIME works). I had forgotten about the new DateTimeOffset types when migrating 2005 > 2008.

I had the same ideal of storing all datetime content locally to the server, but UTC is of course the way to go to avoid this.

Thanks,
-Will

Answer 16

That's magnanimous of you Will, because Microsoft is wrong here.  You shouldn't need to modify your code when upgrading from 2005 to 2008.

To make my point clearer:

For SQL Server 2005 with all versions of the driver, a PHP DateTime parameter is passed to SQL Server as DateTime type, with the *local* PHP time.

For 2008 server, a PHP DateTime is passed to SQL Server as a DateTimeOffset.  To maintain server and backward compatibility this should have been DateTime.  Even DateTime2 could cause incompatibilities, but fewer than DTOffset.

DateTimeOffset is not backwardly compatible unless you happen to have stored every DateTime in UTC - no real reason why that's necessary as long as your timezone is known, so not a good assumption made by MSFT.

DateTimeOffset is compared using its UTC base.  Date comparisons appear to be scaled upwards similar to other data types, so DateTime is converted to DateTime +00:00 when comparing with DateTimeOffset.

IF we want to pass a parameter as DateTimeOffset THEN we must explicitly specify the type SQLSRV_SQLTYPE_DATETIMEOFFSET, otherwise it should never be so (even though it is most naturally aligned with PHP's DateTime, it breaks backward compatibility with server versions and code).

Ideally we would like to convert all of our old DateTime columns to DateTime2 or DateTimeOffset, but in the real world it costs a lot of time, hours, and money to do so, and most importantly, increases risk.  Why do it?

---

Rob

Answer 17

Thanks for the feedback from both of you. I will investigate this issue.

Cheers,

Jonathan

---

This posting is provided 'AS IS' with no warranties, and confers no rights.