Skip site navigation (1) Skip section navigation (2)

[ psqlodbc-Bugs-1010731 ] Incorrect info for varchar(255), text columns

From: <noreply(at)pgfoundry(dot)org>
To: noreply(at)pgfoundry(dot)org
Subject: [ psqlodbc-Bugs-1010731 ] Incorrect info for varchar(255), text columns
Date: 2009-11-18 22:08:45
Message-ID: 20091118220845.30CFF1072084@pgfoundry.org (view raw or flat)
Thread:
Lists: pgsql-odbc
Bugs item #1010731, was opened at 2009-11-18 15:55
You can respond by visiting: 
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1010731&group_id=1000125

Category: None
Group: None
Status: Open
Resolution: None
Priority: 3
Submitted By: Farid Zidan (faridz)
Assigned to: Nobody (None)
Summary: Incorrect info for varchar(255), text columns

Initial Comment:
PostgreSQL ANSI ODBC driver 8.04.01 returns incorrect info for
SQLColAttribute SQL_DESC_TYPE and/or SQL_DESC_OCTET_LENGTH

Example,
create table test_varchar(col1 varchar(20), col2 varchar(255), col3 text);

I get the following information from SQLColAttribute:
column  SQL_DESC_TYPE	SQL_DESC_OCTET_LENGTH
col1	SQL_VARCHAR	20
col2	SQL_LONGVARCHAR	255
col3	SQL_LONGVARCHAR	8190

col1 info is correct. 
col2 SQL_DESC_TYPE  should be SQL_VARCHAR not SQL_LONGVARCHAR.
col3 SQL_DESC_OCTET_LENGTH should be something large such as 2147483647 (2**31 -1)

Attached is screenshot of the Firebird vs PostgreSQL ODBC driver results for the test table above (MS SQL Server ODBC driver returns the same info as the Firebird ODBC driver)

----------------------------------------------------------------------

>Comment By: Farid Zidan (faridz)
Date: 2009-11-18 17:08

Message:
Thanks. That is very related. I updated the driver page 1 options Max Varchar to 2000 
and Max LongVarChar to 2147483647 and now I get identical results to other ODBC 
drivers.

I understand the need to set a limit to SQL_VARCHAR buffers, the default value 254 is 
very small a more reasonable value is 2000 or 8190.

However, setting a limit on SQL_LONGVARCHAR by default to 8190 seems to be very odd. 
No other driver does that. If I define a column at the Server as TEXT then I want to 
get all its data and not the first 8190 characters (by default).

I suspect the limit on the long varchar size was done as I workaround for something, 
in that case only people that need the workaround should get it and everybody who does 
not need it or want it by default.

This issue is similar to http://pgfoundry.org/tracker/?
func=detail&aid=1010516&group_id=1000125&atid=538
where the driver does LF <-> LF/CR by default.

I believe both are the wrong default behavior by the driver and by default the max on 
SQL_LONGVARCHAR column should be the same as the server supported max for the datatype 
TEXT (2**31 - 1) so user will not have to change anything to use the driver.

OK, I need to update this issue title to say:
Driver Max LongVarChar value should be the same as Server max for TEXT datatype by 
default, and update driver Max Varchar option to something bigger such as 2000 (like 
Oracle) or 8000 (like MS SQL Server), otherwise every user who wants to use the driver 
to handle TEXT columns bigger than 8190 will have to fiddle with the driver options 
which should be not required nor necessary if the driver used the server max size for 
the TEXT column like you expect to do

----------------------------------------------------------------------

Comment By: Richard Broersma (rabroersma)
Date: 2009-11-18 16:21

Message:
I have one thought that might be unrelated to your problem:

Notice the ODBC driver setting for Max LongVarChar.

http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/psqlodbc/psqlodbc/docs/config.html?rev=1.10&content-type=text/html

I wonder if the ODBC driver is masking the actual size that a TEXT column could be?

----------------------------------------------------------------------

You can respond by visiting: 
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=1010731&group_id=1000125

pgsql-odbc by date

Next:From: noreplyDate: 2009-11-18 22:22:21
Subject: [ psqlodbc-Bugs-1010731 ] Incorrect info for varchar(255), text columns
Previous:From: noreplyDate: 2009-11-18 22:02:09
Subject: [ psqlodbc-Bugs-1010397 ] SQLForeignKeys no rows returned

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group