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] felipe@php.net
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