Thread: Re: [BUGS] BUG #11767: ODBC driver bug when fetching constant string columns
Re: [BUGS] BUG #11767: ODBC driver bug when fetching constant string columns
From
"Faith, Jeremy"
Date:
> On Fri, Oct 24, 2014 at 9:36 AM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> > wrote: > > > On Wed, Oct 22, 2014 at 11:22 PM, <keyurgovande(at)gmail(dot)com> wrote: > > > The following bug has been logged on the website: > > > > > > Bug reference: 11767 > > > Logged by: Keyur Govande > > > Email address: keyurgovande(at)gmail(dot)com > > > PostgreSQL version: 9.0.3 > > You should update to 9.0.18, you are 3.5 years of bug fixes. > > > > Ah I apologize, I chose the wrong version in the bug report dropdown. This > bug was observed with the latest driver: psqlodbc-09.03.0300 connecting to > a Postgres server 9.1.14. > I can confirm that this bug exists in psqlodbc-09.03.0300 connecting to PostgreSQL 9.3.5. > > > > > > If we run a SELECT statement like: SELECT id, varchar_col, date_col, > > 'random > > > string' as random; > > > via ODBC, then the last column comes back as junk into PHP. > > > > > > In PHP we use the SQL_DESC_OCTET_LENGTH to figure out how many bytes to > > > allocate. But for that column, SQL_DESC_OCTET_LENGTH returns 0 and we > > don't > > > allocate enough space. > > > > > > The driver issue seems to be that when SQLColAttributes() is called with > > > SQL_COLUMN_TYPE for column 'random', it returns SQL_VARCHAR even though > > the > > > PG type is PG_TYPE_UNKNOWN. The promotion is happening here: > > > > > http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=pgtypes.c;h=4ce7636b69d371d9f511d492c68013f070e3b32a;hb=HEAD#l665 > > > > > > I think the same promotion needs to happen when fetching octet-length > > here: > > > > > http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=pgtypes.c;h=4ce7636b69d371d9f511d492c68013f070e3b32a;hb=HEAD#l1275 > > > > > > Thoughts? This is not a problem when using the Vertica ODBC driver for > > > example. > > > > > > A temporary workaround is to use a cast thusly: SELECT id, varchar_col, > > > date_col, varchar 'random string' as random; > > > This was reported to the PHP project ( > > https://bugs.php.net/bug.php?id=68087) > > > but it seems like a Postgres ODBC driver bug. > > pgsql-odbc is more adapted for a bug report on the ODBC driver. I am > > moving the thread there. > > -- > > Michael > > Jeremy Faith Tyco Safety Products/CEM Systems Ltd. ________________________________ This e-mail contains privileged and confidential information intended for the use of the addressees named above. If you arenot the intended recipient of this e-mail, you are hereby notified that you must not disseminate, copy or take any actionin respect of any information contained in it. If you have received this e-mail in error, please notify the senderimmediately by e-mail and immediately destroy this e-mail and its attachments.
Re: Re: [BUGS] BUG #11767: ODBC driver bug when fetching constant string columns
From
Heikki Linnakangas
Date:
On 12/02/2014 02:28 PM, Faith, Jeremy wrote: >> On Fri, Oct 24, 2014 at 9:36 AM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> >> wrote: >> >>> On Wed, Oct 22, 2014 at 11:22 PM, <keyurgovande(at)gmail(dot)com> wrote: >>>> The following bug has been logged on the website: >>>> >>>> Bug reference: 11767 >>>> Logged by: Keyur Govande >>>> Email address: keyurgovande(at)gmail(dot)com >>>> PostgreSQL version: 9.0.3 >>> You should update to 9.0.18, you are 3.5 years of bug fixes. >>> >> >> Ah I apologize, I chose the wrong version in the bug report dropdown. This >> bug was observed with the latest driver: psqlodbc-09.03.0300 connecting to >> a Postgres server 9.1.14. >> > I can confirm that this bug exists in psqlodbc-09.03.0300 connecting to PostgreSQL 9.3.5. Ok, thanks for the testing. >>>> If we run a SELECT statement like: SELECT id, varchar_col, date_col, >>> 'random >>>> string' as random; >>>> via ODBC, then the last column comes back as junk into PHP. >>>> >>>> In PHP we use the SQL_DESC_OCTET_LENGTH to figure out how many bytes to >>>> allocate. But for that column, SQL_DESC_OCTET_LENGTH returns 0 and we >>> don't >>>> allocate enough space. (https://bugs.php.net/bug.php?id=68087) Hmm. Why does that cause junk data to be returned, though? Even if you allocate a single byte of storage, that's enough for the null-terminator, and you should just end up with an empty string, not junk. >>>> The driver issue seems to be that when SQLColAttributes() is called with >>>> SQL_COLUMN_TYPE for column 'random', it returns SQL_VARCHAR even though >>> the >>>> PG type is PG_TYPE_UNKNOWN. The promotion is happening here: >>>> >>> http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=pgtypes.c;h=4ce7636b69d371d9f511d492c68013f070e3b32a;hb=HEAD#l665 >>>> >>>> I think the same promotion needs to happen when fetching octet-length >>> here: >>>> >>> http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=pgtypes.c;h=4ce7636b69d371d9f511d492c68013f070e3b32a;hb=HEAD#l1275 >>>> >>>> Thoughts? This is not a problem when using the Vertica ODBC driver for >>>> example. Yeah, we should fix that in psqlodbc. We can't really determine a sane max size for the column, but we should do the same for unknown that we do for text or varchar columns. I believe the attached patch would fix this. The fix itself is a single line, but it includes a test case. Note that a text (or unknown) column can hold up to 1 GB of text. By the letter of the spec, I think SQL_DESC_OCTET_LENGTH should return 1GB for a text column (or an unknown-type column), but that would be impractical. So the driver just makes a guess. The data will be truncated, if the value in the column is long enough. It would be good to check for SQLSTATE=01004 result from SQLFetch, and perhaps resize the buffers or give a warning or error, if truncation happens. - Heikki