Thread: Bug: attributes dynamically filled (e.g. xml) truncated

Hi,

I have found what I think might be a bug. I am using the ODBC driver
in conjunction with AnySQL. After submitting this bug report to them,
they said they think that it is a problem with the driver, because “it
just displays values returned by the driver.” So here we go! :-)

The text type in PostgreSQL is of unlimited length. When accessing a
view in PostgreSQL, some of the attributes contain data generated
dynamically that is longer than 255 characters, but it would seem that
the driver truncates this to 255 characters.

Steps to reproduce:

drop view if exists "public"."too_short_view";

drop table if exists "public"."too_short";

create table "public"."too_short" (id serial primary key, name text not null);

insert into "public"."too_short" (name) values ('
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nunc
tempor risus sit amet nibh venenatis sit amet vehicula augue
suscipit. Vivamus augue magna, lacinia vel dapibus nec,
tincidunt quis eros. Duis vehicula hendrerit dui, ut cursus ligula volutpat.
This is now at least 255 characters long, but this part will be truncated.');

create view "public"."too_short_view" as select id, xmlelement(name
xml, name) from "public"."too_short";

select * from "public"."too_short_view";

Thanks for your time!
Ben

On 18.03.2013 10:12, Ben Morgan wrote:
> The text type in PostgreSQL is of unlimited length. When accessing a
> view in PostgreSQL, some of the attributes contain data generated
> dynamically that is longer than 255 characters, but it would seem that
> the driver truncates this to 255 characters.
>
> Steps to reproduce:
>
> drop view if exists "public"."too_short_view";
>
> drop table if exists "public"."too_short";
>
> create table "public"."too_short" (id serial primary key, name text not null);
>
> insert into "public"."too_short" (name) values ('
> Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nunc
> tempor risus sit amet nibh venenatis sit amet vehicula augue
> suscipit. Vivamus augue magna, lacinia vel dapibus nec,
> tincidunt quis eros. Duis vehicula hendrerit dui, ut cursus ligula volutpat.
> This is now at least 255 characters long, but this part will be truncated.');
>
> create view "public"."too_short_view" as select id, xmlelement(name
> xml, name) from "public"."too_short";
>
> select * from "public"."too_short_view";

Works for me with the attached test program. It prints the whole field
without truncation:

> connected
> <xml>
> Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nunc
> tempor risus sit amet nibh venenatis sit amet vehicula augue
> suscipit. Vivamus augue magna, lacinia vel dapibus nec,
> tincidunt quis eros. Duis vehicula hendrerit dui, ut cursus ligula volutpat.
> This is now at least 255 characters long, but this part will be truncated.</xml>

I think the driver will return 255 as the max length of a text field,
when the application asks for the field length with the SQLGetTypeInfo()
function. Maybe some other functions, too, I'm not sure. That's just an
arbitrary number, because the driver has to return something, and it can
be changed by setting the MaxVarcharSize setting in the ODBC driver
configuration (odbc.ini). You could try setting that to higher value.

- Heikki

Try changing some settings on the ODBC driver.
Maxvarchar(B0) setting, default is 254 , anything less then 254 is a varchar, anything more is a CLOB/Memo(anysql
equivalent)

Add these setting to your connection string:

;BI=2;TextAsLongVarchar=1;UnknownSizes=2;UseServerSidePrepare=1;comment=POSTGRESS;B0=254;B7=0;

Thanks
Barry Bell, IT Department 
Office: 954-429-3771 x267 Fax: 954-281-1464 email Barry_Bell@harte-hanks.com


-----Original Message-----
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Ben Morgan
Sent: Monday, March 18, 2013 4:13 AM
To: pgsql-odbc@postgresql.org
Subject: [ODBC] Bug: attributes dynamically filled (e.g. xml) truncated

Hi,

I have found what I think might be a bug. I am using the ODBC driver in conjunction with AnySQL. After submitting this
bugreport to them, they said they think that it is a problem with the driver, because “it just displays values returned
bythe driver.” So here we go! :-)
 

The text type in PostgreSQL is of unlimited length. When accessing a view in PostgreSQL, some of the attributes contain
datagenerated dynamically that is longer than 255 characters, but it would seem that the driver truncates this to 255
characters.

Steps to reproduce:

drop view if exists "public"."too_short_view";

drop table if exists "public"."too_short";

create table "public"."too_short" (id serial primary key, name text not null);

insert into "public"."too_short" (name) values ('
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nunc tempor risus sit amet nibh venenatis sit amet vehicula
auguesuscipit. Vivamus augue magna, lacinia vel dapibus nec, tincidunt quis eros. Duis vehicula hendrerit dui, ut
cursusligula volutpat.
 

This is now at least 255 characters long, but this part will be truncated.');

create view "public"."too_short_view" as select id, xmlelement(name xml, name) from "public"."too_short";

select * from "public"."too_short_view";

Thanks for your time!
Ben


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Hi,

(2013/03/18 17:12), Ben Morgan wrote:
> Hi,
>
> I have found what I think might be a bug. I am using the ODBC driver
> in conjunction with AnySQL. After submitting this bug report to them,
> they said they think that it is a problem with the driver, because “it
> just displays values returned by the driver.” So here we go! :-)
>
> The text type in PostgreSQL is of unlimited length. When accessing a
> view in PostgreSQL, some of the attributes contain data generated
> dynamically that is longer than 255 characters, but it would seem that
> the driver truncates this to 255 characters.

Please try to turn on the *Text as LongVarChar* option.

regards,
Hiroshi Inoue

> Steps to reproduce:
>
> drop view if exists "public"."too_short_view";
>
> drop table if exists "public"."too_short";
>
> create table "public"."too_short" (id serial primary key, name text not null);
>
> insert into "public"."too_short" (name) values ('
> Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nunc
> tempor risus sit amet nibh venenatis sit amet vehicula augue
> suscipit. Vivamus augue magna, lacinia vel dapibus nec,
> tincidunt quis eros. Duis vehicula hendrerit dui, ut cursus ligula volutpat.
> This is now at least 255 characters long, but this part will be truncated.');
>
> create view "public"."too_short_view" as select id, xmlelement(name
> xml, name) from "public"."too_short";
>
> select * from "public"."too_short_view";
>
> Thanks for your time!
> Ben

Hi guys,

it started working properly when I set

*Max VarChar* and *Max LongVarChar* both to 0.
They were 255 and 8190, respectively.

Thanks for your help!
Ben

On Wed, Mar 20, 2013 at 12:57 AM, Inoue, Hiroshi <inoue@tpf.co.jp> wrote:
> Hi,
>
>
> (2013/03/18 17:12), Ben Morgan wrote:
>>
>> Hi,
>>
>> I have found what I think might be a bug. I am using the ODBC driver
>> in conjunction with AnySQL. After submitting this bug report to them,
>> they said they think that it is a problem with the driver, because “it
>> just displays values returned by the driver.” So here we go! :-)
>>
>> The text type in PostgreSQL is of unlimited length. When accessing a
>> view in PostgreSQL, some of the attributes contain data generated
>> dynamically that is longer than 255 characters, but it would seem that
>> the driver truncates this to 255 characters.
>
>
> Please try to turn on the *Text as LongVarChar* option.
>
> regards,
> Hiroshi Inoue
>
>
>> Steps to reproduce:
>>
>> drop view if exists "public"."too_short_view";
>>
>> drop table if exists "public"."too_short";
>>
>> create table "public"."too_short" (id serial primary key, name text not
>> null);
>>
>> insert into "public"."too_short" (name) values ('
>> Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nunc
>> tempor risus sit amet nibh venenatis sit amet vehicula augue
>> suscipit. Vivamus augue magna, lacinia vel dapibus nec,
>> tincidunt quis eros. Duis vehicula hendrerit dui, ut cursus ligula
>> volutpat.
>> This is now at least 255 characters long, but this part will be
>> truncated.');
>>
>> create view "public"."too_short_view" as select id, xmlelement(name
>> xml, name) from "public"."too_short";
>>
>> select * from "public"."too_short_view";
>>
>> Thanks for your time!
>> Ben
>
>
>