Edit report at http://bugs.php.net/bug.php?id=51354&edit=1
ID: 51354 User updated by: andrew dot coulton at proscenia dot co dot uk 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: I agree - my impression is this is specific to SAGE driver and php ODBC. I don't know enough of the internals to know which side the problem lies, but I did note in my report that the fields are correctly retrieved via ODBC in Access and ADO. Can anyone advise on how the gettypeinfo() data is populated and whether the problem is more likely to be the SAGE driver returning invalid typeinfo or the php driver not correctly recognising it? I'd imagined that if the problem was with the SAGE driver it would be consistent across all client connections. I realise devs may not have access to the SAGE driver, happy to do any further debugging if someone can point me in the right direction. Previous Comments: ------------------------------------------------------------------------ [2010-03-25 12:15:38] [email protected] Hi. I can't reproduce it using MySQL Driver. So, probably this is a Sage driver issue. ------------------------------------------------------------------------ [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
