Bug #68087 [Com]: ODBC not reading DATE columns correctly

Edit report at https://bugs.php.net/bug.php?id=68087&edit=1 ID: 68087 Comment by: keyur@php.net Reported by: keyur@php.net Summary: ODBC not reading DATE columns correctly Status: Closed Type: Bug Package: ODBC related Operating System: Linux PHP Version: 5.4.33 Assigned To: keyur Block user comment: N Private report: N New Comment: Hi, I am unable to reproduce that with Vertica. But the problem exists with Postgres. One workaround is to explicitly set the type as varchar like so: SELECT id, varchar_col, date_col, varchar 'random string' The bug seems to be in the driver like you alluded to. When no cast is given, Postgres sets the type as PG_TYPE_UNKNOWN but when SQLColAttributes() is called with SQL_COLUMN_TYPE, it returns SQL_VARCHAR (http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=pgtypes.c;h=4ce7636b69d371d9f511d492c68013f070e3b32a;hb=HEAD#l665) The same "type-promotion" behavior is not found when fetching the octet length though. See here: http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=pgtypes.c;h=4ce7636b69d371d9f511d492c68013f070e3b32a;hb=HEAD#l1275 Let me email the pgsql maintainers and see if they agree. Previous Comments: ------------------------------------------------------------------------ [2014-10-22 16:58:37] j dot faithw at yahoo dot com I have discovered an issue with postgres and SQL_DESC_OCTET_LENGTH for constant string values. e.g. given your 'foo' table used in the test CREATE TABLE FOO(ID INT, VARCHAR_COL VARCHAR(100), DATE_COL DATE)'); select id,varchar_col,date_col,'string constant' from foo; The postgres ODBC driver returns the 'string constant' column as coltype=SQL_VARCHAR but the SQLColAttributes(result->stmt, (SQLUSMALLINT)(i+1), colfieldid, NULL, 0, NULL, &displaysize); call sets displaysize=0 when colfieldid=SQL_DESC_OCTET_LENGTH. As displaysize=0 this causes an allocation of 1 byte of data storage for the field and junk data is returned. If SQL_COLUMN_DISPLAY_SIZE is used then it returns The value set for MaxVarcharSize in the odbc.ini file(4000 in many examples). Or if MaxVarcharSize is not set it defaults to 255 unless the string is more than 255 bytes long in which case it returns the number of bytes in the constant string(I tried this with 300 euro symbols, 3 bytes each in UTF8, and got 900 as a result). But for data from char/varchar columns SQL_COLUMN_DISPLAY_SIZE returns the number of characters(not bytes) which was the original multibyte character problem. This may be more a postgres ODBC driver issue than a php bug but prior to the change to use SQL_DESC_OCTET_LENGTH this worked ok. One option could be to check for displaysize==0 and try SQL_COLUMN_DISPLAY_SIZE instead. ------------------------------------------------------------------------ [2014-10-20 16:37:53] keyur@php.net 5.4 is closed for everything but security fixes, so I didn't push it there. The patch is queued for the next 5.5 and 5.6 release: 5.5.19 (https://github.com/php/php-src/blob/PHP-5.5/NEWS) and 5.6.3 (https://github.com/php/php-src/blob/PHP-5.6/NEWS) ------------------------------------------------------------------------ [2014-10-20 16:13:58] j dot faitw at yahoo dot com I notice that this fix did not make it into the recent 5.6.2 or 5.5.18 releases(and not committed to 5.4). Is this intentional? If so is it likely to make it into 5.6.3? ------------------------------------------------------------------------ [2014-10-07 21:24:31] keyur@php.net The fix for this bug has been committed. Snapshots of the sources are packaged every three hours; this change will be in the next snapshot. You can grab the snapshot at http://snaps.php.net/. For Windows: http://windows.php.net/snapshots/ Thank you for the report, and for helping us make PHP better. ------------------------------------------------------------------------ [2014-09-23 21:25:17] keyur@php.net Proposed patch: https://gist.github.com/keyurdg/60e9fb2f97c45c725458 ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at https://bugs.php.net/bug.php?id=68087 -- Edit this bug report at https://bugs.php.net/bug.php?id=68087&edit=1