Skip to Content.
Sympa Menu

freetds - Re: [freetds] freebcp: add support for escaping characters?

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT freetds.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] freebcp: add support for escaping characters?
  • Date: Thu, 30 Mar 2006 22:55:22 -0500

galt AT folkplanet.com wrote:
>
> I kind of like the idea of using CSV.

OK, but how would it affect the work at hand?

Understand there's no single definition of "a CSV" file. Microsoft more
or less invented the term, afaik, and their "standard" has (what else?)
evolved over the years. *If* I were to implement CSV files, it would be
with some specific interoperability, some particular implementation, in
mind.

> Also, currently NULL values and empty strings
> cannot be told apart.

Ahem. I understand they're technically different, of course, but how are
they semantically different? Which user can tell the difference between a
null field and a zero-length string? And what is the difference between
an empty field and a field that's empty?

In moving data across systems, I'd be hard-pressed to explain what
information was lost when zero-length strings became NULL. If someone
pointed out that the column was not NULLable, I'd want to know why, then,
did the application permit entries with no data? If the column can be
empty, it might as well be NULL.

> Basically, for char mode to be useful,
> you either need to have an escaping mechanism,
> or a quoting mechanism, and you need to be
> able to represent NULL differently from an empty string.
> As was noted earlier, if using a quoting mechanism,
> then only that one quote char requires an escape.

FSVO useful. I already explained my reservations about quoting data.
Multicharacter delimiters *can* overcome weird data issues, if the other
application (freebcp's counterpart) can deal with them.

I can think of several ways to encode data to make delimiters unique:
vis(3), uuencode, base64. I would contend that within the FreeTDS realm,
freebcp's multicharacter delimiters suffice. The only purpose to adding
something else, be it CSV or whatever, would be to facilitate moving data
to/from another RDBMS.

--jkl


>
> Please comment!
>
> -Galt
>
> On Mar 29 2006, James K. Lowden wrote:
>
> > Konrad J Hambrick wrote:
> > > What does freebcp do when say, a char (or varchar) field
> > > contains an embedded freebcp (field or record) delimiter char ?
> >
> > As far as freebcp is concerned, there's no such thing as a delimiter
> > embedded in the data. You have data, and between the data you have
> > delimiters. When it sees a delimiter, it marks the end of that field
> > and the beginning of the next.
> >
> > Granted, that causes sometimes confusing errors, because partway
> > through processing your 6-column file it will find "7" columns, often
> > eventually ending with an error about not being able to convert to
> > type X.
> >
> > OTOH, in years of dealing with this stuff, I've never truly been stuck
> > for a delimiter. Normally, I use '\', because it has no place in
> > English prose. If I do find that character in the data, I can safely
> > change it to '/'. Similarly, most of the data I work with doesn't
> > have tabs and newlines, so they're fine delimiters, too. freebcp
> > supports an ASCII NUL delimiter these days; I bet you won't find that
> > in your character data.
> >
> > If I were really stuck, I could always use multicharacter sequence
> > delimiters. '$%^', for example, would do pretty well.
> >
> > For my money, quoting and escaping are too error-prone, too apt to
> > alter the data. Sooner or later you'll wind up with escapes in the
> > database. If you've ever seen email with '>From' in it, you know what
> > I'm talking about.




Archive powered by MHonArc 2.6.24.

Top of Page