Bug #51354 [Opn]: Random string data returned for timestamp odbc field

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