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: galt AT folkplanet.com
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] freebcp: add support for escaping characters?
  • Date: 30 Mar 2006 17:07:17 -0800


The bioinformatics database I am working with has
all kinds of chars in the notes fields.
it use both / and \ as well as \n and \t etc.

I kind of like the idea of using CSV.

Also, currently NULL values and empty strings
cannot be told apart.
If we were using CSV, we could escape the delimiter itself by doubling it
e.g."john ""bigboy"" johnson".
Then for NULL, use it without quotes.
At least MySQL treats "NULL" different from NULL
when using ENCLOSED BY '"' clause.

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.

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.
Regards,
-jkl
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds





Archive powered by MHonArc 2.6.24.

Top of Page