Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT freetds.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] bcp currently broken for BIT type in TDS 5.0
  • Date: Sat, 9 Dec 2006 18:07:08 -0500

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

Attachment: ribo.log
Description: Binary data




Archive powered by MHonArc 2.6.24.

Top of Page