FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> 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.