Skip to Content.
Sympa Menu

freetds - Re: [freetds] freebcp out from MS SQL Server drops spaces [C1]

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Cedric ROUVRAIS <cedric.rouvrais AT sgcib.com>
  • To: freetds AT lists.ibiblio.org
  • Subject: Re: [freetds] freebcp out from MS SQL Server drops spaces [C1]
  • Date: Tue, 7 Jul 2009 14:05:26 -0400


Hello,

Just checked with the DBA on this issue. The space padding is defined by
the ANSI specification, when the data is fixed length (ie not varchar) then
it has to be space padded per ANSI spec, which explains the behaviour of
Sybase and MSSQL bcp command.

Will tentatively modify the source code accordingly and test.

a++ Cedric

__________________


Hi James,

Thanks for the pointer in the source code. I had a quick look at the
source, and if I may suggest a small change. In the source dblib.c, the
function dbconvert, in my point of view should not trim spaces if the
column type is SYBCHAR.

The reasoning behind this is that if the spaces are not data, then I would
choose SYBVARCHAR type. By choosing a fixed length character type, I
explicitly am saying that spaces are data to me.

Just my 2p,

Cedric

__________________

Hi James,

Thank you for your response, I would just like to respond to the
philosophical aspect whilst I look at the other points, if you don't mind
:o)

> You bring up an interesting philosophical point: are trailing spaces
data?
> IMO, no, they're not, and freebcp is correct not to pad character data.
> Evidently the authors of bcp thought otherwise, hence the tiny
> incompatibility

>From my perspective it depends on the data type. If we look at Sybase and
MS SQL BCP the behaviour depends on the datatype.

If you bcp out a column that is varchar() then the trailing spaces are
dropped, on the other hand if you bcp out a char(64) then the you get
exactly 64 characters in the file.

So, as I see it, in the case of a fixed column width, the trailing spaces
are data, otherwise I would use varchar() :o)

You can also simulate this behaviour by doing a select colA + colB + colC +
colD from someThing

You will see that in the case of fixed column width the trailing spaces are
preserved by the database.

Also, I am inclined to think that consistency is sometimes more appropriate
than correctness. As such, it would appear to me that the fact that freebcp
doesn't behave as the other bcp commands, would be a disadvantage more than
an advantage.

Just a thought.

By the way, it's not really a report writer, the downstream process
requires simili COBOL Copybooks (fixed width files and not delimited type
files). In this context, views/tables, are extremely handy and the database
is clearly more performant than anything else to process large volumes of
data in such a manner.

Regards,

Cedric


*************************************************************************
This message and any attachments (the "message") are confidential, intended
solely for the addressee(s), and may contain legally privileged information.
Any unauthorised use or dissemination is prohibited. E-mails are susceptible
to alteration. Neither SOCIETE GENERALE nor any of its subsidiaries or
affiliates shall be liable for the message if altered, changed or falsified.
*************************************************************************





Archive powered by MHonArc 2.6.24.

Top of Page