[freetds] bcp currently broken for BIT type in TDS 5.0

James K. Lowden jklowden at freetds.org
Sat Dec 9 18:07:08 EST 2006


More data, still no answer.  Long message follows. 

Completed tasks:

1.  Installed Sybase client tools for 12.5.3 on Mac OS X.
2.  Got isql, bcp, and Ribo running. 
3.  Captured bcp in with Ribo.

Status: Confused. 

James K. Lowden wrote:
> Trying to bcp in to a table with BIT fields on Adaptive Server
> Enterprise 12.5.1 leads to unhappy results.  
...
> 1> exec sp_layout bittypes
> 2> go
>  ColumnName Type                      Length      Nulls
>  ---------- ------------------------- ----------- -----
>  variable   bit                                 1   N  
>  allownulls bit                                 1   N  
> 
> $ cat bittypes.txt 
> 0       1
...
> Can someone easily tell me what the packet should look like?  From the
> log:
> 
> bcp.c:1364:Normal end-of-file reached while loading bcp data file.
> net.c:724:Sending packet
> 0000 07 01 00 0e 00 00 00 00-04 00 00 00 00 01       |........ ......|
> 
> I'm not sure how to decipher that

OK, now I know at least that much.  (We don't have bulk packets
documented.)

07 	bulk packet
01 	status = final
00 0e 	size = 14
00 00 	channel = 0
00 	packet #0 (i.e. 1st packet)
00 	window = 0
04 00 	rowsize = 4 (endianism reversed?)
data:	00 00 00 01

The Sybase bcp client sent two packets, which Ribo describes as:

PDU Header
  TDS Packet Type [1]:          BUF_BULK  (0x07)
  Status [1]:                   BUFSTAT_BEGIN (0x00)
  Length [2]:                   13
  Channel [2]:                  0
  Packet No. [1]:               0
  Window [1]:                   0

PDU Header
  TDS Packet Type [1]:          BUF_BULK  (0x07)
  Status [1]:                   BUFSTAT_EOM (0x01)
  Length [2]:                   8
  Channel [2]:                  0
  Packet No. [1]:               0
  Window [1]:                   0

Raw PDU Data
  Unformatted Contents [5]:     0x0003000002

Capture Record Header
  Source [4]:                   RESPONSE (0x00000002)
  Length [4]:                   17

PDU Header
  TDS Packet Type [1]:          BUF_RESPONSE (0x04)
  Status [1]:                   BUFSTAT_EOM (0x01)
  Length [2]:                   17
  Channel [2]:                  0
  Packet No. [1]:               0
  Window [1]:                   0

DONE Token (0xFD); fixed length.
  Length [0]:                   [8]
  Status [2]:                   DONE_COUNT (0x0010)
  TranState [2]:                TDS_TRAN_PROGRESS (0x0002)
  Count [4]:                    1

Here's Ribo's captured stream data sent by the Sybase bcp client:

00000a10                       07  00 00 0d 00 00 00 00 00
00000a20  03 00 00 02 07 01 00 08  00 00 00 00 00 00 00 02 
00000a30  00 00 00 11 04 01 00 11  00 00 00 00 fd 00 10 00 
00000a40  02 00 00 00 01  [end of transmission]                           
     

which I interpret as:

07 	bulk packet
00 	status = not final
00 0d 	size = 13
00 00 	channel = 0
00 	packet #0
00 	window = 0
00 03 	rowsize = 3 
data	00 00 02 (two bits in last byte: 00000010)

07 	bulk packet
01 	status = final
00 08 	size = 8
00 00 	channel = 0
00 	packet #0 (??)
00 	window = 0

00 00 00 02 00 00 00 11: 17 byte response follows

04 	response from server
01 	status = final
00 11 	size = 17
00 00 	channel = 0
00 	packet #0
00 	window = 0
fd 	done token
00 10 	status = done count
00 02 	state = trans in progress
00 00 00 01 count = 1
                                 
And just for good measure, here's the tcpdump trace:

15:54:02.618459 IP client > server: . ack 782 win 65535 <nop,nop,timestamp
2052629747 2035153131>
        0x0000:  4500 0034 2e91 4000 4006 cfd3 c0a8 0209  E..4.. at .@.......
        0x0010:  d9a0 a00d ca00 07cf ff18 fc58 7ba8 a0df  ...........X{...
        0x0020:  8010 ffff bf0e 0000 0101 080a 7a58 a4f3  ............zX..
        0x0030:  794d f8eb                                yM..
15:54:02.907181 IP client > server: P 899:912(13) ack 782 win 65535
<nop,nop,timestamp 2052629747 2035153131>
        0x0000:  4500 0041 2e92 4000 4006 cfc5 c0a8 0209  E..A.. at .@.......
        0x0010:  d9a0 a00d ca00 07cf ff18 fc58 7ba8 a0df  ...........X{...
        0x0020:  8018 ffff b5e9 0000 0101 080a 7a58 a4f3  ............zX..
        0x0030:  794d f8eb 0700 000d 0000 0000 0003 0000  yM..............
        0x0040:  02                                       .
15:54:02.907294 IP client > server: P 912:920(8) ack 782 win 65535
<nop,nop,timestamp 2052629747 2035153131>
        0x0000:  4500 003c 2e93 4000 4006 cfc9 c0a8 0209  E..<.. at .@.......
        0x0010:  d9a0 a00d ca00 07cf ff18 fc65 7ba8 a0df  ...........e{...
        0x0020:  8018 ffff b7e8 0000 0101 080a 7a58 a4f3  ............zX..
        0x0030:  794d f8eb 0701 0008 0000 0000            yM..........
15:54:03.055799 IP server > client: . ack 920 win 6432 <nop,nop,timestamp
2035153175 2052629747>
        0x0000:  4520 0034 6601 4000 3406 a443 d9a0 a00d  E..4f. at .4..C....
        0x0010:  c0a8 0209 07cf ca00 7ba8 a0df ff18 fc6d  ........{......m
        0x0020:  8010 1920 a5ad 0000 0101 080a 794d f917  ............yM..
        0x0030:  7a58 a4f3                                zX..

As I said, there are two packets sent (for 2 bits of data!).  There's also
a conversation earlier between client and server about the bit fields and
an inspection of sysobjects.status2.  I'm attaching the whole Ribo
translation for your perusal.  

I'm out of time now....

--jkl
-------------- next part --------------
A non-text attachment was scrubbed...
Name: ribo.log
Type: application/octet-stream
Size: 29545 bytes
Desc: not available
Url : http://lists.ibiblio.org/pipermail/freetds/attachments/20061209/057f2e5e/attachment-0001.obj 


More information about the FreeTDS mailing list