Skip to Content.
Sympa Menu

freetds - Re: [freetds] bcp is really busted

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: christos AT zoulas.com (Christos Zoulas)
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] bcp is really busted
  • Date: Mon, 29 Dec 2003 08:22:34 -0500

On Dec 28, 11:44pm, jklowden AT schemamania.org ("James K. Lowden") wrote:
-- Subject: Re: [freetds] bcp is really busted

| On Wed, 24 Dec 2003, christos AT zoulas.com (Christos Zoulas) wrote:
| >
| > Create a table with a single varchar(X) field.
| >
| > insert five rows
| >
| > 1. fooo
| > 2. bar
| > 3. barfoo
| > 4.
| > 5. NULL
|
| How did you insert row 4?

Not using bcp obviously (from Enterprise manager on MSSQL 2000).

| What does "select foo, datalength(foo) from foo" return?

fooo, 4
bar, 3
barfoo, 6
, 0
NULL, NULL

| > Copy the table into a file: notice the contents.
| >
| > ^D\0fooo^C\0bar^F\0barfoo\0\0\0\0
| >
| > Obviously this will not work because the code does not differentiate
| > between the empty string and NULL. Look at the code: assumptions
| > everywhere about datalen == 0 being equivalent to NULL.
|
| You're talking about "freebcp -n", right? I don't use native file formats
| very much.

Yeah, I don't use delimited formats much.

| I did what you suggested using a delimited file, and it works, subject to
| the limitation that a character data file cannot distinguish between a
| zero-length string and a NULL, viz:
|
| Script started on Thu Dec 25 17:09:40 2003
| $ sqsh -Svarley -U$U -P$P
| sqsh-2.1 Copyright (C) 1995-2001 Scott C. Gray
| This is free software with ABSOLUTELY NO WARRANTY
| For more information type '\warranty'
| [22] varley.FreeTDS.1> create table foo( varchar(20) NULL )
| [22] varley.FreeTDS.2> go
| [23] varley.FreeTDS.1> insert foo
| [23] varley.FreeTDS.2> select 'fooo' union
| [23] varley.FreeTDS.3> select 'bar' union
| [23] varley.FreeTDS.4> select 'barfoo' union
| [23] varley.FreeTDS.5> select '' union
| [23] varley.FreeTDS.6> select NULL
| [23] varley.FreeTDS.7> go
| (5 rows affected)
| [24] varley.FreeTDS.1> select * from foo
| [24] varley.FreeTDS.2> go
| foo
| --------------------
| NULL
|
| bar
| fooo
| foobar
|
| (5 rows affected)
| [25] varley.FreeTDS.1> exit
| $ freebcp FreeTDS..foo out foo.txt -c -Svarley -U$U -P$P
|
| Starting copy...
|
| firstrow = 0 row_of_query = 5 rows written 5
| 5 rows copied.
|
| $ cat -vte foo.txt
| $
| $
| bar$
| fooo$
| foobar$
|
| $ freebcp FreeTDS..foo in foo.txt -c -Svarley -U$U -P$P
|
| Starting copy...
|
| 5 rows copied.
| $ sqsh -Svarley -U$U -P$P
| sqsh-2.1 Copyright (C) 1995-2001 Scott C. Gray
| This is free software with ABSOLUTELY NO WARRANTY
| For more information type '\warranty'
| [21] varley.FreeTDS.1> select foo, count(*) from foo group by foo
| [21] varley.FreeTDS.2> go
| foo
| -------------------- -----------
| NULL 3
| 1
| bar 2
| foobar 2
| fooo 2
|
| (5 rows affected)
| [22] varley.FreeTDS.1> exit
| $ exit
|
| Script done on Thu Dec 25 17:15:04 2003
|
| How can a delimited file distinguish between no data and null data? There
| are exactly zero bytes to carry the information. ;-)

By encoding the NULL specially. Microsoft's bcp works fine. I will check
how it encodes it and report back. (Same for native format).

| > Of course this does not fix the NULL problem; this would require a lot
| > more work, because we need to change the way we write and the way we
| > read.
|
| The TDS protocol doesn't distinguish between a zero-length character
| column and a NULL. That used to be handled with a kludge. Watch: on
| Sybase 11.9.2 (TDS 5.0), the server produces this unintuitive result:
|
| [30] sandbox.freetds.1> select 'a' + '' + 'a'
| [30] sandbox.freetds.2> go
|
| ---
| a a
|
| IOW, what you'd think is a zero-length string actually has a blank in it.
| rtrim it, and you get a NULL. You get the same result if you insert it in
| a table. Because there's no way for the on-disk format (I infer) to say
| "there's data here, of zero size". So they write a blank and hope you
| won't notice.
|
| Microsoft yields what you'd expect with TDS 7.0:
|
| [23] varley.FreeTDS.1> select 'a' + '' + 'a'
| [23] varley.FreeTDS.2> go
|
| --
| aa

Wow.

| On the wire, Sybase sends a NULL by setting the datalength to zero.
| There's no way to say "here's a string with no bytes". Microsoft uses a
| different datatype (e.g. xvarchar), and sets the datalength to -1. This
| is managed very subtly in tds_get_data(): The datalength field, known as
| the "column_varint_size", is 1 byte for Sybase's and 2 bytes for
| Microsoft's datatypes. In the 2-byte case, we have:
|
| if (colsize == 0) {
| tds_clr_null(current_row, i);
| ^^^^^^^ [not null, zero length]
| curcol->column_cur_size = 0;
| return TDS_SUCCEED;
| }
| if (colsize == -1)
| colsize = 0;
|
| I don't understand what tds_clr_null() does. It's magical.
|
| Returning to BCP. For delimited files, I'm sure we DTRT, because the
| vendors' utilities both treat an empty field as NULL. For a native bcp
| datafile, we could (as you say we don't) distinguish. We'd have to do a
| little experimenting, to see if the server recognizes as NULL an uploaded
| bcp field whose length is -1. Let's hope Dr. Thompson thinks that's an
| interesting problem. At any rate, I think we're closer than you might
| expect, because this negative-one-is-null indicator affects only a few
| datatypes.

I think that we should take a look at the MSSQL 2000, or 2003 bcp output
native output and see how they do it.

| Because of your message, I spent quite some time reviewing the bcp-out
| code. I have to admit, we might benefit from a special purpose function
| for handling native files, because afaik it *should* be just a matter of
| reading/writing the row image as delivered by the server. The function we
| have, _bcp_exec_out(), is general purpose, and handles the trivial native
| case by interpreting the image and reconstituting it on disk, when all it
| needs to do is splat the whole buffer. That would be an interesting 0.63
| change.

Thanks for looking into it. I've spent quite some time trying to figure
what is going on myself (I'd like to get it working).

| Bill Thompson has said he's interested in moving bcp from db-lib to
| libtds, where it can support all three client libraries. I think he's
| waiting for me to create the skeleton, which I'll do as soon as we release
| 0.62. That move seems like the perfect time to address nulls in native
| bcp files.

Sounds good to me.

| Please tell me if I've misunderstood something, or if you see things
| differently.

No, you've understood all the issues.

Regards,

christos




Archive powered by MHonArc 2.6.24.

Top of Page