FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

Dang!

I was halfway through a (much less eloquent) explanation of the issues...
:-)

Bill 

> -----Original Message-----
> From: Lowden, James K [SMTP:LowdenJK AT bernstein.com]
> Sent: 14 November 2003 16:39
> To:   'FreeTDS Development Group'
> Subject:      RE: [freetds] Removal of trailing spaces by FreeTDS
> 
> > 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.
> 
> 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds