FreeTDS Development Group

[christos AT zoulas.com (Christos Zoulas)]

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