php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #57618 DBDATE - wrong Format of Date - Bug with Insert/Update of Dates
Submitted: 2007-04-10 05:32 UTC Modified: 2007-06-01 18:18 UTC
From: edv at leha dot at Assigned:
Status: Not a bug Package: PDO_INFORMIX (PECL)
PHP Version: 5.2.1 OS: ZEND Core 2.0
Private report: No CVE-ID: None
 [2007-04-10 05:32 UTC] edv at leha dot at
Description:
------------
We use 
ZendCore 2.0.0 with bundled apache2
 pdo 1.0.3 (installed from PECL)
 pdo_informix 1.1.0 (installed from PECL)
 IBM Informix CSDK Version 2.90 UC1
 on RHEL_FC4 32bit
 
DBDATE is exported to Environment with the Apache Startup-Script (/usr/local/Zend/apache2/bin/envvars).

Bug 1:
------
setting and exporting the environment var DBDATE="DMY4." in /usr/local/Zend/apache2/bin/envvars only works for insert,update statements - but not for selecting. It also works on Select with a character conversion date::char(10). 
Within selects the Date-Values are generally shown as YYYY-MM-DD.

Bug 2:
------
Direct update/insert using exec with Date-Format as supplied by previous select doesn't work. Supplying the DMY4. format is working.
Prepared update/insert using prepare/execute with Date-Format as supplied by previous works - but supplying the DMY4. format is not working.

See example below..

Reproduce code:
---------------
<?
$pObj=new PDO("informix:host=devdb.leha.at;service=1525;database=stores_demo;server=devdb_net;");

echo "<pre>Environment (DBDATE):".getenv('DBDATE')."<br/><hr/>";

$pStmt=$pObj->prepare("select SHIP_DATE::char(10) as SHIP_DATE_As_Char,* from orders where customer_num=?");
$pStmt->execute(array(104));
$aAll=$pStmt->fetch(PDO::FETCH_ASSOC);
print_r($aAll);

/*
# Test for same result with direct query
$pStmt=$pObj->query("select * from orders where customer_num=104");
$aAll=$pStmt->fetch(PDO::FETCH_ASSOC);
print_r($aAll);
*/

$cDate="18.12.2006";
$nRows=$pObj->exec("update orders set SHIP_DATE='$cDate' where ORDER_NUM=1001");
printf ("<hr/>Executing direct statement 1 with %s (Rows: %0d)<br/>",$cDate,$nRows);
print_r($pObj->errorInfo());

$cDate="2006-12-19";
$nRows=$pObj->exec("update orders set SHIP_DATE='$cDate' where ORDER_NUM=1001");
printf ("<hr/>Executing direct statement 2 with %s (Rows: %0d)<br/>",$cDate,$nRows);
print_r($pObj->errorInfo());


$pStmt2=$pObj->prepare("update orders set SHIP_DATE=? where ORDER_NUM=?");
$cDate="20.12.2006";
printf ("<hr/>Executing prepared statement 3 with %s<br/>",$cDate);
if (! $pStmt2->execute(array($cDate,1001))) {
	print_r($pStmt2->errorInfo());
	echo "Error-Code:".$pStmt2->errorCode."<br/>";
}

$cDate="2006-12-21";
printf ("<hr/>Executing prepared statement 4 with %s<br/>",$cDate);
$pStmt2->execute(array($cDate,1001));
if (! $pStmt2->execute(array($cDate,1001))) {
	print_r($pStmt2->errorInfo());
	echo "Error-Code:".$pStmt2->errorCode."<br/>";
}

$pStmt = null;
$pObj = null;
?>

Actual result:
--------------
Environment (DBDATE):DMY4.
Array
(
    [SHIP_DATE_AS_CHAR] => 21.12.2006
    [ORDER_NUM] => 1001
    [ORDER_DATE] => 1998-05-20
    [CUSTOMER_NUM] => 104
    [SHIP_INSTRUCT] => express                                 
    [BACKLOG] => n
    [PO_NUM] => B77836    
    [SHIP_DATE] => 2006-12-21
    [SHIP_WEIGHT] => 20.40
    [SHIP_CHARGE] => 10.00
    [PAID_DATE] => 1998-07-22
)
Executing direct statement 1 with 18.12.2006 (Rows: 1)
Array
(
    [0] => 00000
    [1] => 0
    [2] =>  ((null)[0] at (null):0)
)
Executing direct statement 2 with 2006-12-19 (Rows: 0)
Array
(
    [0] => HY000
    [1] => -1204
    [2] => [Informix][Informix ODBC Driver][Informix]Invalid year in date (SQLExecDirect[-1204] at /usr/local/Zend/Core/share/PDO_INFORMIX-1.1.0/informix_driver.c:308)
)
Executing prepared statement 3 with 20.12.2006
Array
(
    [0] => 
    [1] => 0
    [2] =>  (SQLExecute[0] at /usr/local/Zend/Core/share/PDO_INFORMIX-1.1.0/informix_statement.c:720)
)
Error-Code:
Executing prepared statement 4 with 2006-12-21

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-04-12 23:20 UTC] kfbombar at us dot ibm dot com
We are looking into these defects.  While we are looking into them, can you please provide the expected results that you are looking for from your script given.  Thank you.
 [2007-04-17 03:52 UTC] edv at leha dot at
Expected Result (changes with preceeding *):
--------------
Environment (DBDATE):DMY4.
Array
(
    [SHIP_DATE_AS_CHAR] => 21.12.2006
    [ORDER_NUM] => 1001
*    [ORDER_DATE] => 20.05.1998
    [CUSTOMER_NUM] => 104
    [SHIP_INSTRUCT] => express                                 
    [BACKLOG] => n
    [PO_NUM] => B77836    
*    [SHIP_DATE] => 21.12.2006
    [SHIP_WEIGHT] => 20.40
    [SHIP_CHARGE] => 10.00
*    [PAID_DATE] => 22.07.1998
)
Executing direct statement 1 with 18.12.2006 (Rows: 1)
Array
(
    [0] => 00000
    [1] => 0
    [2] =>  ((null)[0] at (null):0)
)
Executing direct statement 2 with 2006-12-19 (Rows: 0)
* Direct Statement 2 should not give an error
Array
(
    [0] => 00000
    [1] => 0
    [2] =>  ((null)[0] at (null):0)
)
Executing prepared statement 3 with 20.12.2006
* Prepared Statement 3 should not give an error
Executing prepared statement 4 with 2006-12-21
 [2007-06-01 18:18 UTC] opendev at us dot ibm dot com
Thank you for taking the time to write to us, but this is not
a bug.

Bug 1:

We run this php script:
<?php

$conn = new PDO("DSN", "username", "password");

echo "Environment (DBDATE):" . getenv('DBDATE') . "\n";

$stmt = $conn->prepare("SELECT DATE1::char(10) as DATE_AS_CHAR, * FROM test_pdo WHERE id=?");
$stmt->execute( array(1) );
$res=$stmt->fetch(PDO::FETCH_ASSOC);
print_r($res);

?>

And we are getting the following output:

Environment (DBDATE):DMY4.
Array
(
    [DATE_AS_CHAR] => 02.02.2002
    [ID] => 1
    [DATE1] => 02.02.2002
    [DATE2] => 02.03.2003
)


We are not able to re-produce the error. Sorry. 

Bug 2:

We run this php script: 

<?php

$pObj = new new PDO("DSN", "username", "password");

echo "Environment (DBDATE):" . getenv('DBDATE') . "\n";

$cDate="18.12.2006";
$nRows=$pObj->exec("update test_pdo set DATE1='$cDate' where id=1");
printf ("Executing direct statement 1 with %s (Rows: %0d)\n",$cDate,$nRows);
print_r($pObj->errorInfo());

$cDate="2006-12-19";
$nRows=$pObj->exec("update test_pdo set DATE1='$cDate' where id=1");
printf ("Executing direct statement 2 with %s (Rows:%0d)\n",$cDate,$nRows);
print_r($pObj->errorInfo());

$pStmt2=$pObj->prepare("update test_pdo set DATE1=? where id=1");
$cDate="20.12.2006";
printf ("Executing prepared statement 3 with %s\n",$cDate);
if (! $pStmt2->execute(array($cDate))) {
        print_r($pStmt2->errorInfo());
        echo "Error-Code:" . $pStmt2->errorCode() . "\n";
}

$pStmt2=$pObj->prepare("update test_pdo set DATE1=? where id=1");
$cDate="2006-12-21";
printf ("Executing prepared statement 4 with %s\n",$cDate);
if (! $pStmt2->execute(array( $cDate))) {
        print_r($pStmt2->errorInfo());
        echo "Error-Code:" . $pStmt2->errorCode() . "\n";
}
printf( "Done\n" );

?>

And we are getting the following output:

Environment (DBDATE):DMY4.
Executing direct statement 1 with 18.12.2006 (Rows: 2)
Array
(
    [0] => 00000
    [1] => 0
    [2] =>  ((null)[0] at (null):0)
)
Executing direct statement 2 with 2006-12-19 (Rows:0)
Array
(
    [0] => HY000
    [1] => -1204
    [2] => [Informix][Informix ODBC Driver][Informix]Invalid year in date (SQLExecDirect[-1204] at /vbs/opendbad/php/pdo_ibm/pdo_informix/informix_driver.c:308)
)
Executing prepared statement 3 with 20.12.2006
Executing prepared statement 4 with 2006-12-21
Array
(
    [0] => HY010
    [1] => -11067
    [2] => [Informix][Informix ODBC Driver]Function sequence error. (SQLRowCount[-11067] at /vbs/opendbad/php/pdo_ibm/pdo_informix/informix_statement.c:808)
)
Error-Code:HY010

Statement 2 and 4 are giving error because the date format is invalid. This is expected behaviour. 

Thanks.
 [2008-06-26 02:28 UTC] ettl at leha dot at
We now found that the DATE Problem depends on the INFORMIX CSDK Version used.

We used 2.90UC1 which gives the wrong Date. 

With 2.90UC4 we get the correct Date according to the setting in DBDATE. This is also the Version bundled with ZEND. The ZEND Version works as it uses the .so delivered with ZEND - but as soon as you generate the pdo_informix from pecl the installed CSDK is used.

We also tried it with CSDK 3.50UC1 - and it does not work in this version - so the Date is wrong again.
 [2014-04-08 06:47 UTC] wdog666 at gmail dot com
the problem persists also with "IBM Informix CSDK Version 3.70"
 [2014-05-02 13:06 UTC] m dot djihangiroff at persona dot de
After CSDK 2.90, IBM switched to an ODBC compliant DATE Format: MDY4-
Every SELECT you are firing, the Dates will ALWAYS return in the Format MDY4- , because it is hardcoded.

There is no chance to alter this behaviour with DBDATE or GL_DATE Environment Variables.
 
PHP Copyright © 2001-2019 The PHP Group
All rights reserved.
Last updated: Sun Aug 18 05:01:28 2019 UTC