Skip to Content.
Sympa Menu

freetds - RE: [freetds] Removal of trailing spaces by FreeTDS

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Lowden, James K" <LowdenJK AT bernstein.com>
  • To: 'FreeTDS Development Group' <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] Removal of trailing spaces by FreeTDS
  • Date: Fri, 14 Nov 2003 11:39:26 -0500

> From: Simon Talbot [mailto:simont AT nse.co.uk]
> Sent: November 14, 2003 10:28 AM
>
> I still think this is fundimentally wrong though, the library is not
> returning a faithfull representation of what is contained in the
> Database, it is arbitrarily deciding that trailing spaces are not
> required by the client app and stripping them without being asked ! In
> the case our our data mining application, the trailing spaces are all
> important -- still think this is a prime candidate for a switch in the
> config file,

You'll be pleased to learn you have a choice. :-)

Have another look at Microsoft's docs for dbbind. You'll find:

vartype varaddr Padding Terminator
------------ ------------ ------------ ----------
CHARBIND DBCHAR blanks none
STRINGBIND DBCHAR blanks \0
NTBSTRINGBIND DBCHAR none \0
VARYCHARBIND DBVARYCHAR none none

There isn't really any perfect "faithful representation of what is contained
in the database", in that what you store is what you get. (Huh. A WYSIWYG
database....) It's all a question of how you want db-lib to represent
character data for you. You might say, "just give me exactly what's there",
begging the question of how to fill the rest of the buffer you provide. If
you use CHARBIND to fetch a 6-byte string into a 10 byte buffer, it will
give you your six bytes (blanks and all), write blanks to the last four
bytes, and tell you the datalength() is 6. You're free of course to ignore
those extra four blanks; they're not even data.

That's pretty faithful, but a lot of C programmers expect their strings to
be null terminated, which led STRINGBIND, but lots of people found they were
trimming trailing blanks, leading to NTSTRINGBIND.

In practice, routinely trimming trailing blanks -- on insertion and
retrieval -- is almost always the best answer. You can't see them, and SQL
ignores them in comparisons. Always trimming them leads to less confusion
and generally Just Works. But then if you actually want your trailing
blanks (say, to catenate varchar fields), you have to understand the arcane
business of binding database data to host application buffers.

I also second Bill: the documentation is a little vague. For instance, it
doesn't say whether NTBSTRINGBIND will trim trailing blanks that are stored
in the database, only that it won't pad the field with trailing blanks.
FreeTDS is nothing if not a product of empiricism.

> Upon further investigation, my Like for Like
> comparison of code uses the ODBC API to get the data in question to
> overcome the 255 character limit on VarChar's which Microsoft's DB
> Library seemingly imposes.

Not seemingly -- does. Microsoft's db-lib hasn't changed very much since
the Bronze Age. Since SQL Server was licensed from Sybase, Microsoft added
new datatypes (including IDENTITY types) and domain logins. That's about
it. It uses TDS 4.2, which is what imposes your varchar(255) limitation.

Regards,

--jkl
-----------------------------------------
The information contained in this transmission may contain privileged and
confidential information and is intended only for the use of the person(s)
named above. If you are not the intended recipient, or an employee or agent
responsible for delivering this message to the intended recipient, any
review, dissemination, distribution or duplication of this communication is
strictly prohibited. If you are not the intended recipient, please contact
the sender immediately by reply e-mail and destroy all copies of the original
message. Please note that we do not accept account orders and/or instructions
by e-mail, and therefore will not be responsible for carrying out such orders
and/or instructions.






Archive powered by MHonArc 2.6.24.

Top of Page