FreeTDS Development Group

["James K. Lowden" <jklowden AT schemamania.org>]

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