FreeTDS Development Group

[galt AT folkplanet.com]

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