FreeTDS Development Group

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

James K. Lowden wrote:
> > bcp is broken for NULL character data.  
> Correction: bcp is broken for TDS 5.0.  

This is a long, technical message of interest only to people who want to
know how TDS 5.0 sends bcp data to the server.  It may contain errors,
because reverse engineering is subject to error.  If you know something,
won't you please chime in?  

Job #1 was to understand how the row should appear on the wire. 
Specifically, how does the server knows which non-fixed-datatype columns
have data, and which were skipped (are NULL)?  We have no documentation
for the 0x07 packet format.  

I created a test table that contains NULLs and used freebcp to extract it
as a character file.  The file was correctly formed; freebcp extracts
NULLs just fine.  It's uploading that isn't working.  The tokenless 0x07
packet is correctly formed, and the server accepts it.  The problem is
that, although it's a valid packet, it's also an incorrect packet.  I.e.,
it's sending wrong data to the server.  

I used Sybase's bcp utility to upload the file (correctly, of course), and
captured the session with their Ribo tool.  By comparing that with what
freebcp does, I'm pretty sure I know where freebcp goes astray.  

Below is the query and the contents of the table, a diff of the two
packets, captured by Ribo, my analysis of a good packet and of the bad row
data.  

select    rowid
        , nullif(name, 'image') as name
        , case name 
          when 'image' then NULL 
                       else type 
          end as type
        , case name 
          when 'image' then NULL 
                       else afloat 
          end as afloat 
into nulltypes 
from (
          select (      
                select count(*) 
                from systypes 
                where name < a.name
          ) as rowid
        , name
        , type
        , type + (type % 100) / 100.0 as afloat 
        from systypes as a 
        where type < 40
) as A 
where rowid < 25

1> select * from nulltypes order by rowid;
 rowid       name                           type afloat               
 ----------- ------------------------------ ---- ---------------------
           9 extended type                    36             36.360000
          12 NULL                           NULL                  NULL
          14 intn                             38             38.380000
          20 nvarchar                         39             39.390000

0x07 Packet (from TDSDUMP):

net.c:739:Sending packet
0000 07 01 00 86 00 00 00 00-25 00 04 00 25 00 09 00 |........ %...%...|
0010 00 00 65 78 74 65 6e 64-65 64 20 74 79 70 65 24 |..extend ed type$|
0020 00 00 00 00 00 02 2a cf-40 05 1f 16 15 08 04 15 |......*. @.......|
0030 00 02 00 15 00 0c 00 00-00 00 00 00 00 00 02 2a |........ .......*|
0040 cf 40 03 11 08 04 1c 00-04 00 1c 00 0e 00 00 00 |.@...... ........|
0050 69 6e 74 6e 26 00 00 00-00 00 02 49 a1 e0 05 16 |intn&... ...I....|
0060 0d 0c 08 04 20 00 04 00-20 00 14 00 00 00 6e 76 |.... ...  .....nv|
0070 61 72 63 68 61 72 27 00-00 00 00 00 02 59 0b 30 |archar'. .....Y.0|
0080 05 1a 11 10 08 04      -                        |......|

Difference (bcp is G4, freebcp is i386):

$ diff -u uploaded.freebcp.text uploaded.bcp.text  | sort -b -k1.2
+++ uploaded.bcp.text   2007-11-25 12:27:46.000000000 -0500
--- uploaded.freebcp.text       2007-11-25 12:28:02.000000000 -0500

+00  00 25 04 00 00 25 00 00  00 09 65 78 74 65 6e 64  |.%...%....extend|
-00  25 00 04 00 25 00 09 00  00 00 65 78 74 65 6e 64  |%...%.....extend|

 10  65 64 20 74 79 70 65 24  00 00 00 00 00 02 2a cf  |ed type$......*.|

+20  40 05 1f 16 15 08 04 00  0b 01 00 00 0b 00 00 00  |@...............|
-20  40 05 1f 16 15 08 04 15  00 02 00 15 00 0c 00 00  |@...............|

+30  0c 02 08 04 00 1c 04 00  00 1c 00 00 00 0e 69 6e  |..............in|
-30  00 00 00 00 00 00 02 2a  cf 40 03 11 08 04 1c 00  |.......*.@......|

+40  74 6e 26 00 00 00 00 00  02 49 a1 e0 05 16 0d 0c  |tn&......I......|
-40  04 00 1c 00 0e 00 00 00  69 6e 74 6e 26 00 00 00  |........intn&...|

+50  08 04 00 20 04 00 00 20  00 00 00 14 6e 76 61 72  |... ... ....nvar|
-50  00 00 02 49 a1 e0 05 16  0d 0c 08 04 20 00 04 00  |...I........ ...|

+60  63 68 61 72 27 00 00 00  00 00 02 59 0b 30 05 1a  |char'......Y.0..|
-60  20 00 14 00 00 00 6e 76  61 72 63 68 61 72 27 00  | .....nvarchar'.|

+70  11 10 08 04 0a                                    |.....|
-70  00 00 00 00 02 59 0b 30  05 1a 11 10 08 04 0a     |.....Y.0.......|

Analysis of good packet:

$ ribo nulltypes.bcp.tds |grep Unf | perl -ane '$F[3] =~ s/^0x//; printf
qq(%s\n), pack(q(H*), $F[3])' | hexdump -C

00 25   -> row size = 37
04      -> 4 variable columns
00      -> always zero
00 25   -> row size = 37 [again]
        00 00 00 09 -> 9 
        65 78 74 65 6e 64 65 64 20 74 79 70 65 -> "extended type"
        24 -> 36 
        00 00 00 00 00 02 2a cf 40 -> 36.36 [numeric datatype] 
05      -> size of offset table
1f 16 15 08 04  -> offset_table [values inclusive of col count]:
                   col 1:  [4,8)
                   col 2:  [8,21)
                   col 3: [21,22)
                   col 4: [22,31)

00 0b   -> row size = 11
01      -> 1 variable column
00      -> always 0
00 0b   -> row size = 11 [again] 
        00 00 00 0c -> 12
02      -> size of offset table 
08 04   -> offset table

00 1c   -> row size = 28
04      -> 4 variable columns
00      -> always zero 
00 1c   -> row size = 28 [again]
        00 00 00 0e -> 14
        69 6e 74 6e -> "intn"
        26 -> 38
        00 00 00 00 00  02 49 a1 e0 -> 38.38
05      -> size of offset table
16 0d 0c 08 04  -> offset table

00 20   -> row size = 32
04      -> 4 variable columns
00      -> always zero 
00 20   -> row size = 32 [again]
        00 00 00 14 -> 20
        6e 76 61 72 63 68 61 72 -> "nvarchar"
        27 -> 39
        00 00 00 00 00 02 59 0b 30 -> 38.38
05      -> size of offset table
1a 11 10 08 04  -> offset table

The 0x07 packet seems to comprise:

1.  Standard 8-byte header
2.  N row buffers

where a row buffer comprises

1.  16-bit rowsize
2.  8-bit variable column count
3.  8-bit binary zero
4.  16-bit rowsize reprise
5.  fixed-datatype data
6.  variable-datatype data
7.  offset table, 1 byte per variable column + 1

The server knows the table layout, so it knows how many non-null fixed
datatype columns there are, and how big each one is.  The variable columns
come next, followed by the offset table, which tells where each of those
columns ends. The offset table is laid out right-to-left i.e., the offset
of the first column is the last byte in the table.   

The server's chore is to parse the offset table backwards:

1.  Read offset of col C at packet[size-C]
2.  Read offset of col C+1 at packet[ size - (C+1) ]
3.  Compute size of col C as offset - offset.
4.  ++C; Goto step 2. 

until it reads a byte in the offset table whose value equals the size of
the offset table.  (If the end of the last column is not at the start of
the offset table, the server will look for the "adjustment table", the
high-bytes to be added to the offset table values to compute the true
offsets.)  

If the pattern of variable columns is:

        NULL 3-bytes NULL 8-bytes NULL

the offset table will be:

        06   5 columns + me
         0f  begin col 5 (exclusive)
         07  begin col 4
         07  begin col 3
         04  begin col 2
         04  begin col 1

Only 5 bytes are needed because no data appear after the end of column 4. 
NULLs preceding EOD are signified by adjacent offsets whose values are
equal.  In the above row, the start of col 2 (04, last byte) equals the
start of col 1, and the start of col 4 (07) equals the start of col 3.  

Now, then.  Here's the bad row sent by freebcp:

15 00   -> 21 bytes 
02 00   -> 2 variable columns
15 00 
        0c 00 00 00 -> 12
        00 00 00 00 00 02 2a cf 40 -> [error, see below]
03      -> 1 + var col count
  11    -> begin col 3
  08    -> begin col 2
  04    -> begin col 1

The server accepts this row because it obeys all the rules of an 0x07
packet (according to me, by inference).  The problem is that there's a
column definition for the second column, which is NULL in the file.  NULL
data aren't supposed to be sent.  _bcp_add_variable_columns is somehow
deciding to add bogus data to the rowbuffer.  

Wouldn't you know, that's what the server has:

1> select distinct cast(name as binary) as name from nulltypes where rowid
= 12 and name is not NULL
2> \go -m vert
name: 00 00 00 00 00 02 2a cf 40 [spaces added]

Next up: finding the bug in the code.  

Comments welcome.  I'm not beat, but it's a steep climb.  And if anyone
knows why Sybase uses this format instead of a simple TDS_ROW packet, do
please share!  

--jkl

P.S.  N.B. for programmers.  Just because the wire format has a a weird
layout isn't any reason to write code that way.  The adjustment table
needn't be computed per column as the buffer is being constructed; all
that's needed is the offsets.  When the last one is ready, it's easy to
see if its value exceeds 256, and if so first write each value left
shifted 8 bits.  Then write the offset table modulo 256.