Edit report at http://bugs.php.net/bug.php?id=51354&edit=1
ID: 51354 Updated by: [email protected] Reported by: andrew dot coulton at proscenia dot co dot uk Summary: Random string data returned for timestamp odbc field Status: Open Type: Bug Package: ODBC related Operating System: Windows Vista/Apache 2.2.11 PHP Version: 5.2.13 New Comment: Hi. I can't reproduce it using MySQL Driver. So, probably this is a Sage driver issue. Previous Comments: ------------------------------------------------------------------------ [2010-03-23 02:03:16] andrew dot coulton at proscenia dot co dot uk Description: ------------ Using the Sage Line 50 ODBC driver (v15 or v16), when selecting a table containing timestamp column values odbc returns a string type variable containing random data - consistent for all rows in the resultset and occasionally between requests but changing at random. This applies whether using PDO or odbc_ functions. Including an ISO 8601 timestamp value as a WHERE condition performs as expected, so this appears to be a problem with the retrieval of a resultset. Additionally, calling odbc_field_type() on the column crashes php (valid results are returned for other columns, including date values but not timestamps, in the dataset). It appears this may be a result of the data type not appearing in the table returned by odbc_gettypeinfo? PHP is running as an apache module. Extensions enabled: -php_mssql.dll -php_mysql.dll -php_apc.dll -php_apd.dll -php_gd2.dll -php_uploadprogress.dll -php_pdo.dll -php_pdo_mysql.dll -php_pdo_odbc.dll ODBC settings: odbc.allow_persistent = On odbc.check_persistent = On odbc.max_persistent = -1 odbc.max_links = -1 odbc.defaultlrl = 4096 odbc.defaultbinmode = 1 I can access the tables with the correct timestamps showing through ODBC in Access and through ADO in a Delphi application. Test script: --------------- $conn= new PDO("odbc:DSN=SageLine50v16;Uid=manager;Pwd="); $result=$conn->query("SELECT DATE, RECORD_CREATE_DATE FROM AUDIT_JOURNAL WHERE RECORD_CREATE_DATE>='2010-03-22 12:19:00'"); echo "Query Result\n"; if ($result) { var_dump($result->fetch()); } $connection = odbc_connect('SageLine50v16','manager',''); $result=odbc_columns($connection, "%", "%", "AUDIT_JOURNAL", "RECORD_CREATE_DATE"); echo "RECORD_CREATE_DATE "; print_r(odbc_fetch_array($result)); echo "DATE "; $result=odbc_columns($connection, "%", "%", "AUDIT_JOURNAL", "DATE"); print_r(odbc_fetch_array($result)); $result= odbc_gettypeinfo($connection); echo "TypeInfo\n"; odbc_result_all($result); $result=odbc_exec($connection, "SELECT DATE, RECORD_CREATE_DATE FROM AUDIT_JOURNAL;"); echo "Field Name\tType\n"; echo odbc_field_name($result, 1),"\t\t",odbc_field_type($result, 1); /* The following causes php to terminate unexpectedly */ //echo odbc_field_name($result, 2),"\t\t\t",odbc_field_type($result, 2); Expected result: ---------------- Expect to see: - result array with a valid timestamp in RECORD_CREATE_DATE rather than mangled string. - A non-null TYPE_NAME key in the odbc_columns() return value for the RECORD_CREATE_DATE field. - Presumably, a value in the data type table returned by odbc_gettypeinfo corresponding to data type #11 - there is no row for this though notably #9 maps to the DATE type and #10 to TIME, so #11 would fit as Timestamp? - A return value for the odbc_field_type for the RECORD_CREATE_DATE column. Actual result: -------------- Query Result array() { /*numeric keys removed*/ ["DATE"]=> string(10) "2010-03-22" ["RECORD_CREATE_DATE"]=> string(19) "�p�y��9������wW" } RECORD_CREATE_DATE Array ( [TABLE_QUALIFIER] => [TABLE_OWNER] => [TABLE_NAME] => AUDIT_JOURNAL [COLUMN_NAME] => RECORD_CREATE_DATE [DATA_TYPE] => 11 [TYPE_NAME] => [PRECISION] => 0 [LENGTH] => 8 [SCALE] => 0 [RADIX] => 10 [NULLABLE] => 0 [REMARKS] => Date and time when the record was created. ) DATE Array ( [TABLE_QUALIFIER] => [TABLE_OWNER] => [TABLE_NAME] => AUDIT_JOURNAL [COLUMN_NAME] => DATE [DATA_TYPE] => 9 [TYPE_NAME] => DATE [PRECISION] => 10 [LENGTH] => 2 [SCALE] => 0 [RADIX] => 10 [NULLABLE] => 0 [REMARKS] => Transaction date ) TypeInfo TYPE_NAME DATA_TYPE PRECISION LITERAL_PREFIX LITERAL_SUFFIX CREATE_PARAMS NULLABLE CASE_SENSITIVE SEARCHABLE UNSIGNED_ATTRIBUTE MONEY AUTO_INCREMENT LOCAL_TYPE_NAME CHAR 1 0 " " NULL 1 1 3 1 0 0 NULL VARCHAR 12 0 " " NULL 1 1 3 1 0 0 NULL LONG VARCHAR -1 0 " " NULL 1 1 3 1 0 0 NULL DECIMAL 3 0 NULL NULL precision, scale 1 0 2 0 0 0 NULL NUMERIC 2 0 NULL NULL precision, scale 1 0 2 0 0 0 NULL TINYINT -6 3 NULL NULL NULL 1 0 2 0 0 0 NULL SMALLINT 5 5 NULL NULL NULL 1 0 2 0 0 0 NULL INTEGER 4 10 NULL NULL NULL 1 0 2 0 0 0 NULL REAL 7 7 NULL NULL NULL 1 0 2 0 0 0 NULL FLOAT 6 15 NULL NULL NULL 1 0 2 0 0 0 NULL DOUBLE 8 15 NULL NULL NULL 1 0 2 0 0 0 NULL DATE 9 10 NULL NULL NULL 1 0 2 0 0 0 NULL TIME 10 8 NULL NULL NULL 1 0 2 0 0 0 NULL Field Name Type DATE DATE ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/bug.php?id=51354&edit=1
