Edit report at https://bugs.php.net/bug.php?id=23611&edit=1
ID: 23611
Comment by: matthew at artofsimplicity dot co dot uk
Reported by: janko at dupoint dot com
Summary: mssql.datetimeconvert should not be on by default
Status: Open
Type: Feature/Change Request
Package: MSSQL related
Operating System: Windows 2000 Server
PHP Version: 4.3.1
Block user comment: N
Private report: N
New Comment:
I've managed to encounter this quirk with our development team running
identical
hardware / operating systems (Windows XP) and identical versions of PHP 5.4.9
and
MSSQL extensions.
And yet, on one machine the php.ini had the option commented out (defaults to
On)
and on the other it was uncommented and set to Off.
Previous Comments:
------------------------------------------------------------------------
[2003-05-26 04:38:58] mats at dupoint dot com
We've noticed that under some circumstances the php_mssql extensions seems to
switch the mssql.datetimeconvert flag setting between On and Off. Most of the
time we get the date in the Off format (cause we've set the flag to Off in the
ini file), i.e. YYYY-MM-DD HH:MM:SS, but sometimes it switch to On wich will
give us the format "14 maj 2003 9:03" and the db inseret query failes.
Is there any patches for php_mssql module?
Anyone seen this problem before? Solution?
Platform is Win2k Server, MSSQL 2000, PHP 4.3.1.
------------------------------------------------------------------------
[2003-05-14 19:15:06] f...@php.net
Testing without PEAR does not give me any errors. This is my code
<?php
dl("php_mssql.dll");
$con = mssql_connect("local", "user", "pass");
$result=mssql_query("SELECT {fn NOW()}");
$row = mssql_fetch_row($result);
print_r($row);
$date = $row[0];
$result1=mssql_query("SELECT CAST('$date' AS DATETIME)");
$row1 = mssql_fetch_row($result1);
print_r($row1);
?>
and the output
Array
(
[0] => May 14 2003 5:15PM
)
Array
(
[0] => May 14 2003 5:15PM
)
I would say this is a server configuration problem. specific to your local
settings. Wow did you create the alias (Client Network Utility) and what is the
value of the different options you can configure ?
------------------------------------------------------------------------
[2003-05-14 02:31:32] janko at dupoint dot com
The default behavior also breaks a lot of code, I dare say including PEAR.
Consider the following example:
<?php
require_once "DB.php";
$db = DB::connect("mssql://user:pass@localhost/db");
$sql = "SELECT {fn NOW()}";
$date = $db->getOne($sql);
echo "NOW() is $date<br />";
$sql = "SELECT CAST('$date' AS DATETIME)";
$date = $db->getOne($sql);
if (DB::isError($date)) {
echo $date->toString();
}
else {
echo "The date is $date";
}
?>
Now, the only reasonable outcome of this code would be to print the same date
twice, regardless of which format it was initially... right?
Well, no. Here's what I get from running this code:
NOW() is 14 maj 2003 9:03
[db_error: message="DB Error: " code=-1 mode=return level=notice prefix=""
info="SELECT CAST('14 maj 2003 9:03' AS DATETIME) [nativecode=Syntax error
converting datetime from character string.]"]
Switch the SELECT CAST() for an INSERT statement and you'll realize why this is
dangerous. It works for ten months of the year, and breaks in May (maj) and
October (oktober). Different host languages will cause the code to work or fail
in different months.
Yes, I realize that changing this behaviour would also affect a lot of existing
code. But this would mostly be a cosmetical change, unless the existing code is
completely dependent on parsing the date as a string. But as I can tell you
first-hand, trying to explain to a customer why their code suddenly stopped
working by the turn of May without us changing anything is _not_ my idea of fun.
------------------------------------------------------------------------
[2003-05-13 14:03:29] f...@php.net
Before this ini parameter was introduced the default behavior of the extension
was to convvert the dates. If we changed this we would break a lot of code.
------------------------------------------------------------------------
[2003-05-13 10:22:14] janko at dupoint dot com
One thing that puzzled me and my co-workers for the
longest of time is that MSSQL flat out refused to
return dates in any sensible form, but insisted on
returning them 'formatted' - in Swedish (the server's
locale).
Now, on certain months (Januari - jan; februari - feb)
this would at least work, although we lost precision as
it would only return minutes, not seconds.
On other months (Maj - maj, Oktober - okt), we suddenly
couldn't enter into the database what it had given us,
because it didn't understand what it seemingly just
gave us.
We were just about to go through a major overhaul of
our application due to this problem, which would have
cost us insane amounts of time and money, when we
almost accidentally stumbled over the ini setting
mssql.datetimeconvert. Turn it off, and hey - over a
year of frustration ends.
I think it goes without saying that this ini variable
should _not_ be turned on in a default installation.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=23611&edit=1
