[freetds] Re: Freetds freebcp
James K. Lowden
jklowden at schemamania.org
Fri Jun 11 18:00:01 EDT 2004
On Fri, 11 Jun 2004, "R. Klemme" <bob.news at gmx.net> wrote:
> the Big O database servers don't have anything like bcp
> They have! ... you can do bulk load in two modes; "direct mode" (or
> "direct load") bypasses the TL and performs better [...]
> If you have PK violations it's a real mess since you can't
> simply rollback...
When I looked into this, I found only commands similar to Microsoft's
"Bulk Load" command. That is, the server reads data from its own disk. I
didn't find a protocol optimization, no equivalent to a bcp client
utility. Happy to be enlightened, however. ;-)
The missing primary key situation is of course very similar to dropping
and re-creating the primary key, and just as messy. But, it might be more
effiecient than using a separate table.
When you first described the Oracle tool, you said it just logged error
rows. Maybe I'm reading too much into what you're saying, but if the PK
enforcement is turned off, how are PK violations detected and logged? Or
is it also able to log only data conversion errors? I'm sure I'm missing
> > 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?
The latter, at least in the version I tested.
> 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.
If it *is* built on the bcp protocol, you'll get batch-level rejections.
If it's not, you'll get transaction-log constrained performance. That's a
lose-lose proposition. You'd be as well off writing a stored procedure to
do a one-row insertion, and call it once per data file row.
> Now, that's interesting that you even consider to involve Mr. Rumsfeld.
There are problems we know we can solve, and there are problems we know we
can't solve. Then there are the problems we don't know we can solve, and
the problems we don't know we can't solve.
Let's just hope this is one of that last kind. One of those is enough.
More information about the FreeTDS