php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #51354 Random string data returned for timestamp odbc field
Submitted: 2010-03-23 02:03 UTC Modified: 2020-10-18 04:22 UTC
Votes:2
Avg. Score:4.5 ± 0.5
Reproduced:2 of 2 (100.0%)
Same Version:1 (50.0%)
Same OS:0 (0.0%)
From: andrew dot coulton at proscenia dot co dot uk Assigned: cmb (profile)
Status: No Feedback Package: ODBC related
PHP Version: 5.2.13 OS: Windows Vista/Apache 2.2.11
Private report: No CVE-ID: None
 [2010-03-23 02:03 UTC] 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

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2010-03-25 12:15 UTC] felipe@php.net
Hi. I can't reproduce it using MySQL Driver. So, probably this is a Sage driver issue.
 [2010-03-28 19:41 UTC] andrew dot coulton at proscenia dot co dot uk
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.
 [2015-08-07 12:08 UTC] chris at barnesconsultants dot com
I have found that this bug can cause PHP to crash when using PHP 5.4.14 and the Sage Line 50 v19 Driver on Windows Server 2012 R2. 

In an Apache Windows Server 2012 R2 context the bug can cause some nasty side effects not least of which is the creation of a folder called %systemdrive% within the PHP folder containing a crash report. The existence of this folder named %systemdrive% can cause all kinds of subsequent problems for PHP.

A work-around tested with PHP 5.4.14 on Windows Server 2012 R2 using PHP COM to access the Sage Line 50 v19 Driver via an ADODB.Connection:

$strConnectionString="Driver={Sage Line 50 v19};DIR=C:/sage_data_folder/ACCDATA;UID=MANAGER;PWD=VERYVERYSECRET";
$sql="SELECT * FROM AUDIT_JOURNAL ORDER BY TRAN_NUMBER DESC";

try {
	$oADO = new COM("ADODB.Connection");
	$oADO->ConnectionString=$strConnectionString;
	$oADO->Open();
	$objRS=$oADO->execute($sql);
	
	while(!$objRS->eof){

		print "ROW: \n";

		for ($i=0;$i<$objRS->Fields->Count;$i++){
			print "  " . $objRS->fields->item($i)->name . ": " . $objRS->fields->item($i)->value . "\n";
		}
		
                /* exit here to check 1 row, 
                   or you may get a LOT of output 
                 in your test ! 
                 */
		exit;
		$objRS->MoveNext();
	}
	
	
	
} catch (com_exception $e) {;
	print $e;
	exit;
}

/* 

BTW: Note that RECORD_CREATE_DATE may well be completely different to DATE_ENTERED if the data was migrated from an earlier version of Sage 50.

*/
 [2020-10-06 14:55 UTC] cmb@php.net
-Status: Open +Status: Feedback -Assigned To: +Assigned To: cmb
 [2020-10-06 14:55 UTC] cmb@php.net
If this is still an issue with current Sage Line 50 Drivers under
any of the actively supported PHP versions[1], please provide a
minimal reproduce script, and an ODBC trace of running that
script.

[1] <https://www.php.net/supported-versions.php>
 [2020-10-18 04:22 UTC] php-bugs at lists dot php dot net
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Nov 02 11:01:29 2024 UTC