FreeTDS Development Group

["James K. Lowden" <jklowden AT schemamania.org>]

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?  
What does "select foo, datalength(foo) from foo" return?

> 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.  

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.  ;-)  

> Try to copy the table back out. Notice it fails to insert anything,
> and code goes to infinite loop, because it thinks that the length
> of the field is always 0. Figure out the minimum change patch:

Will apply, thank you!  

> 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

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.  

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.  

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.  

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

Regards, 

--jkl