FreeTDS Development Group

["Roger L. Reid" <roger.reid AT dpw.com>]

Yeah, FreeTDS is the essential bit for porting off Sybase.   I wondered
if there were others with the same task.  I've
had similar things to deal with.

Remember that the delimiters for bcp can be entire strings.  Right now
for the project describe below (a Sybase -> SQL Server port)
I am using "].%.[" as the field delimiter and "~~x#x~~" for the record
delim - and will mangle them further if I hit those in input.

I would like to hear and share more with folks who are using FreeBCP
specifically for cross-platform support.  We started using 
it to update our Sybase based home-grown client accounting system with
the data from the thrid party time input system that
was on SQL Server.

Now I am porting some stuff off Sybase to SQL Server, and tho the apps
themselves are not cross platform, it is FreeTDS that
lets me write tools for the port, and to not redo millions of lines of
dblib code (MS does have a dblib for Solaris, but why bother?)

Mostly I am not using FreeBCP for the data moves, but sometimes it bails
me out when there's no easy way otherwise.

Because I wanted to be able to keep certain pieces synced one way or the
other without wiping out the whole thing everytime
I wrote my own hack to compare tables across platforms and generate
delete and insert statements as need be.  Simple idea,
works with simple data.

Once you start hitting NULL values, that may be handled differently by
each platform, and the question of representing them in
text during transfer, and distinct from "" and " " - you get a whole new
appreciation for Chris Date's loathing of nulls.  Or how about
padding fixed length string fields?

I always expected single and double quotes, but when the \r and \n's
came along, oh boy - especiallt considering that this was 
a developers hack, not release level code, so I was dumping out text
files and letting unix "diff" find my diffs.  (But I still had to 
write the files and the reparse them to have total control of formating
of datatypes).

I HAD been using tabs for field delim till I got some input with it,
switched to BEL (007) which still works with diff.

OK then newlines, my "solution" for the newlines etc was to add a flag
(that makes 15 of them) to remap chars - so with this flag varchar and
char data can get the \r changed to 036 and \n to 037 - and then after
parsing it to create the change statements mapping them back!  But
getting this 
right has been a challenge (tho it sounds simple) and in the end I
bailed and realized that the tables that were trouble did NOT have to 
be kept in sync between machines, so I'd just freebcp them out, delete
the target, and load.  Again, I could solve it "right", for various
values of
right, but this is a special purpose tool, not release code, so why
bother - the important thing is to get the databases moved.

Roger Reid
Senior Sysytems Analyst
Davis Polk & Wardwell (but speaking only for myself)



> -----Original Message-----
> From: galt AT folkplanet.com [mailto:galt AT folkplanet.com] 
> Sent: Tuesday, March 28, 2006 3:50 PM
> To: freetds list
> Subject: [freetds] freebcp: add support for escaping characters?
> 
> 
> First, I should say that freetds is wonderful!
> I don't know what I'd do without it.