[freetds] Re: Freetds freebcp

R. Klemme bob.news at gmx.net
Fri Jun 11 07:57:59 EDT 2004

Hi again,

> > [mailto:freetds-bounces at lists.ibiblio.org]On Behalf Of R. Klemme
> > Sent: June 10, 2004 6:47 AM
> > 
> > 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.
> I see.  BTW, afaik the Big O database servers don't have anything like
> bcp, that is, no special protocol, no way to bypass the transaction log. 
> you could point me to information about such a thing, I'd be interested.

They have!  Unfortunately I don't have the documentation at hand (I'm at
home today) but you can do bulk load in two modes; "direct mode" (or "direct
load") bypasses the TL and performs better (well, it should have at least
*one* advantage, shouldn't it?).  The downside is, that with these primary
key violations the index stays in an invalid state.  AFAIK the database
directly writes to the pages of the table and tries to update the index
*afterwards*.  If you have PK violations it's a real mess since you can't
simply rollback...
> For our part, rows with client-side conversion errors are filtered to the
> error file and count toward the (-m) maximum error count.  

Same as MS bcp.

> > 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).
> You're right, and I spoke too soon.  :-(  A little experimentation and log
> perusal reveals that when the server rejects the batch, it doesn't say
> which row was the offender.  

Now, that's interesting.  I think MS's bcp spits out a message to stdout
about which row was rejected.  Or was it just the batch rejected?  I'll
check it on Monday when I'm back in the office.

> For such problems, you have two options.  The clean one is to BCP to a
> separate table, and "insert A ... select T ... where not in (select A
> Defeats some of the advantages of bcp, but, like oatmeal, it's the right
> thing to do.

Right, we've thought of that, too, but as you say: it's not optimal.

>  The dirty way is to use IGNORE_DUP_KEY when creating the index.
>  That will generate a 3604 message for each batch containing ignored rows,
> but it still won't tell you which ones were ignored.  

That's interesting!

> My experiments with Microsoft's BCP show that a batch containing duplicate
> keys, when applied to a table whose index was created "with
> IGNORE_DUP_KEY", is rejected in its entirety.  freebcp OTOH doesn't
respond that way; the
> non-duplicate rows land in the table.  I conclude Microsoft's utility 
> cancels the batch somehow.  

Aha, thanks for pointing me into that direction!  I'll check this out next

We considered another option: using PreparedStatement.addBatch() (we're
using jTDS) and basically implement the bulk load ourselves.  The JDBC
protocol states, that you can get the information which rows were imported
and which had an error.  Now, jTDS's batch functionality is not yet fully
implemented (it works but with poor performance and not using bcp protocol
internally as I understand) and considering your earlier remark about SQL
Server not mentioning the offending row's index I doubt whether it can be
done in a JDBC compliant way at all.  Darn!

> > > 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?
> Right.  It seems we can say that client-side errors are detected per row
> (and per column), but server-side errors are reported per batch (without
> information).  The maxerror option thus applies to client-side (mostly
> data conversion) errors.  
> I've done a lot of BCP work over the years, and have often confronted the
> problem of loading data into a table when I don't know what's already
> there.  I never tried the backup-and-skip scenario, and now I know why.  


> Always
> the solution was to use another table and some SQL, or go the
> route.  Them's the choices, because we can't force the server to give us
> the row-level information.  Not even with Donald Rumsfeld's help.  

Now, that's interesting that you even consider to involve Mr. Rumsfeld. 
That gives a slight indication of the problem's hardness. :-))

Thanks again!

Kind regards


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

More information about the FreeTDS mailing list