Edit report at http://bugs.php.net/bug.php?id=51354&edit=1
ID: 51354
Updated by: felipe@php.net
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