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
 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