FreeTDS Development Group

["Pickett, David" <David.Pickett AT phlx.com>]

Well, storing nullable column data in text does require some sort of
encoding.  A common solution is that one blank is a character field blank,
and an empty field is a null.  The MS bcp -k option writeup implies this is
their bcp method.

 
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp
_bcp_61et.asp

This is similar to -n native mode, where length zero is a null on every
type.

The best solution is to trim all blanks except the last when that would
empty the column.  A similar rule would apply to trimming excess zeros from
numbers.  This ensures that NULLS are not created, and still allows a lot of
file size reduction.

Best regards,

David

-----Original Message-----
From: Craig A. Berry [mailto:craigberry AT mac.com]
Sent: Sunday, December 18, 2005 11:38 PM
To: FreeTDS Development Group
Subject: [freetds] [PATCH bcp.c:_bcp_read_hostfile] don't trim trailing
spaces when all spaces


When bulking in character data, we currently trim trailing spaces, which
in general seems like the right thing to do with one exception. The
exception is when a field is all spaces; I don't think we should trim
those to nothing because then we end up inserting nulls in what may or
may not be nullable fields. The attached patch changes behavior so that
we only trim trailing spaces when one or more characters is not a space.

The behavior I'm proposing appears to be consistent with what I'm seeing
in an old Sybase client. I have not done any comparison with Microsoft
clients, but I think we can justify the change on the principle of
round-trip integrity. We should be able to bulk out a table, truncate
it, bulk it back in, and get the same thing we started with. If there
are non-nullable fields containing all spaces, that will not be true
before the patch, but it will be true after. Granted, application
validation logic that allows all spaces in a non-nullable field is
arguably lousy, but that's not a battle that bcp should be fighting.