Skip to Content.
Sympa Menu

freetds - Re: [freetds] Weird problem with CHAR columns being reported as VARCHAR columns with FreeTDS 0.61.2, DBD::ODBC and unixODBC and iODBC talking to SQL Server 2000, perl 5.8.1 on Solaris

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT schemamania.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Weird problem with CHAR columns being reported as VARCHAR columns with FreeTDS 0.61.2, DBD::ODBC and unixODBC and iODBC talking to SQL Server 2000, perl 5.8.1 on Solaris
  • Date: Mon, 13 Oct 2003 20:51:40 -0400

On Mon, 13 Oct 2003, "Harrington, Paul" <Paul-Harrington AT deshaw.com>
wrote:

> I thought that I was having problems with the ChopBlanks attribute being
> honored in DBD::ODBC as any of my tests with CHAR columns were failing
> because of trailing spaces. However, on checking into it a bit closer,
> it seems that the columns in the result set are being described as
> varchar rather than char. The retrieved values do not contain trailing
> spaces when I access the database with DBD::Sybase built over the same
> revision of FreeTDS (but -- of course -- going over ct-lib).

Whether or not you get blank-padded character fields depends on several
things:

char/varchar
null/not null
ANSI_PADDING

In theory, ANSI_PADDING ON makes everything be padded, and OFF makes
everything except non-null char not be padded. In fact, it depends more
on
what driver you're using.

The documentation goes to great pains to describe what is not true:

"The varchar data type is a variable-length data type. Values shorter than
the size of the column are not right-padded to the size of the column. If
the ANSI_PADDING option was set OFF when the column was created, any
trailing blanks are trimmed from character values stored in the column. If
ANSI_PADDING was set ON when the column was created, trailing blanks are
not
trimmed."

But:

1> set ansi_padding off
2> go
1> create table _off (name varchar(10))
2> go
1> set ansi_padding on
2>
3> go
1> create table _on (name varchar(10))
2> go
1> insert _off values ('off') insert _on values ('on')
2> go
(1 row affected)
(1 row affected)
1> select '"' + _off.name + _on.name + '"' from _off cross join _on UNION
2> select '"' + _on.name + _off.name + '"' from _off cross join _on
3> go

----------------------
"offon"
"onoff"

On top of that, it depends on the binding. I don't find any control over
padded blanks in ODBC binding, but db-lib and ct-lib both provide for
explicit treatment of them in db-bind and ct-bind. AFAIK, that's
independent of all the above conditions (as it should be).

I found a statement in Microsoft's documentation to the effect that their
driver blank-pads nullable char columns.

I would check if ANSI_PADDING is on according to ODBC (it should be, by
default). I think if you turn it off, you'll get something more like what
you're getting with ct-lib.

HTH.

--jkl




Archive powered by MHonArc 2.6.24.

Top of Page