FreeTDS Development Group

["R. Klemme" <bob.news AT gmx.net>]

** first one was sent too early and thus rejected from the list **


Hi again,

> On Wed, 09 Jun 2004, Robert <r_klemme AT users.sourceforge.net> wrote:
> > 
> > Hi James,
> 
> Hi Robert, 
> 
> > you are the author of freebcp as far as I understand.  
> 
> That credit goes to Bill Thompson.  He started it; I just give it a trim
> now and then.  

Ok.

> > I'd like 
> > to your opinion on whether it's technically possible to craft a 
> > (free)bcp version that simply ignores error lines (for example 
> > lines that cause a primary key violation) instead of aborting 
> > the whole import.  Do you think that's feasible?  Many thanks!
> 
> Sure it's feasible.  It's only software.  ;-)  Whether it's a good idea or
> not is another question.  

Well, the motivation is this: bulk loaders of other databases (namely a big
one with an "O" ;-)) just send error lines to a log file and continue
importing.  So for reasons of simplicity and consistency I'd like to see the
same behavior with MS SQL Server.

> Keep in mind that bcp works in batches.  If you don't set a batch size,
> the whole set is considered a single batch.  Each time a batch is closed,
> the server commits the rows.  If, as the rows are being laid in prior to
> commitment, some aspect of the data prevents a row from being stored, the
> whole batch is rejected.  That's a function of the server, not the
> application or FreeTDS.  A primary key violation is one example of such an
> error.  

Yes, of course.

> To ignore such errors, you'd have to do one of two things.  One approach
> would be to set the batch size to 1.  In effect, you'd be inserting a row
> at a time.

Nobody would need a bcp for this... :-)

>  The downside would be performance: bcp performance is
> proportional to batch size.  The other approach would be to rely on the
> server's indication of the problem row, back up to the beginning of the
> batch, send a partial one, skip the offending row, and begin another
> batch.  

That's a more interesting scenario.  I've thought of this of course.  The
interesting question is IMHO whether the bcp API in freetds allows to
implement such a behavior with reasonable effort.  I've browsed through the
sources but unfortunately the bcp API functions still seem to be
underdocumented, so It wasn't too easy to figure what's going on exactly
(especially how error line indexes are reported).

> There are other kinds of errors, mostly on the client side.  There are
> numerous opportunities for data conversion errors, short rows, invalid
> nulls, etc.  You'd have to cope with them, too, of course.

Yes, of course.  But as I understand these errors are reported but lines are
otherwise ignored.  I think I've seen a command line argument that denotes
the max ignorable errors - or do I confuse that with MS's bcp?

>  Some aren't
> recoverable, depending on how your data file (if that's what you're using)
> is delimited.  
> 
> HTH and regards, 

Thx a lot for replying!

> --jkl
> 
> P.S.  Let me know if I can post your message and my answer to the list. 
> That's where this conversation belongs.  

True.  I'll include it as receiver.  Hope I got the right one.

Kind regards

robert

-- 
"Sie haben neue Mails!" - Die GMX Toolbar informiert Sie beim Surfen!
Jetzt aktivieren unter http://www.gmx.net/info



-- 
"Sie haben neue Mails!" - Die GMX Toolbar informiert Sie beim Surfen!
Jetzt aktivieren unter http://www.gmx.net/info