Skip to Content.
Sympa Menu

freetds - [freetds] native bcp broken

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "James K. Lowden" <jklowden AT schemamania.org>
  • To: TDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: [freetds] native bcp broken
  • Date: Mon, 5 Jan 2004 21:24:49 -0500

src/dblib/unittests/bcp.c works, but does not exercise native bcp file
formats.

freebcp fails to load and unload the unit test's table, using -n (native
file format). I'm testing rc3 (all Christos's patches to date) on SQL
Server 7.0.

Is this fixable for 0.62, or do we punt with a Known Issue?

To reproduce:

1. Run the bcp unit test. Kill it before it drops the table.
2. freebcp out the data in -c and -n formats.
3. freebcp in the data. -c works, -n fails.

Errors in log:

19:27:37.556831 parsing host column 15
19:27:37.556852 prefix_len = -1 collen = 0
19:27:37.556874 dbconvert(47 [SYBCHAR] len 210 => 47 [SYBCHAR] len 10)
19:27:37.556899 dbconvert() srctype == desttype
19:27:37.557274 tds_client_msg: #20049: "Data-conversion resulted in
overflow.". Connection state is now -1.
19:27:37.557311 bcp(1073) failed to convert 210 bytes at offset 0x3d in
the data file.
19:27:37.557338 parsing host column 16
19:27:37.557359 prefix_len = -1 collen = 0
19:27:37.557431 tds_client_msg: #20070: "I/O error while reading bcp
data-file.". Connection state is now 2.

Relevant portion of data file:

0000 01 0a 00 61 20 20 20 20 20 20 20 20 20 01 00 61
0010 52 94 00 00 00 47 03 01 52 94 b0 03 00 00 00 00
0020 08 e2 01 00 08 e2 01 00 ae 47 e1 7a 14 ae 28 40
0030 a4 70 45 41 23 00 05 02 00 00 04 d2 00 00 00 00
^^^^^ == 1234 decimal
0040 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
0050 00 00 00 00 00 00 00 00 00 23 00 05 02 00 00 04

Failing code:

} else { /* unterminated field */
bcpcol = dbproc->bcp.db_columns[hostcol->tab_colnum - 1];
if (collen == 0 && (is_nullable_type(bcpcol->db_type)
|| bcpcol->db_nullable)) {
TDS_SMALLINT len;

if (fread(&len, sizeof(len), 1, hostfile) != 1) {
if (i != 0)
_bcp_err_handler(dbproc, SYBEBCRE);
return (FAIL);
}

Theory: This code is wrong, but that's not the only problem. "len" is
being read from the file because the code expects to find
a prefix there, not noticing that prefix_len is -1. The prefix length
shouldn't be -1 for a nullable type (see below), and in any case the
prefix should not be read here. The prefix should only ever be read in
the switch at the top of the function, 133 lines earlier.

To be clear: We're talking about the format of a "native" bcp data file.
A prefix_len of -1 (or 0) means there's no prefix for this column in the
file. None is needed because it's a fixed-length datatype, whose size is
implied by its type. A prefix_len of 1, 2, or 4 indicates there *is* a
prefix (of that size), the contents of which indicate the length of the
data for that column in the file. Nullable types need a prefix. Until a
week or so ago, a prefix of 0 meant a null column. Today, a prefix of 0
means an "empty" column, one whose length is zero (relevant only for
character data on Microsoft servers), whereas a NULL column is signified
by a prefix of -1.

When sending data via TDS 5.0, it's necessary to make some adjustment on
this score. The data file might have been fetched from a Microsoft server
and be written to a Sybase server. (This is very bad practice, bound to
fail in all but very limited circumstances, but there's no reason for us
to add to the potential woes.) If a -1 prefix is encountered in the data
file, it has to be rendered as a column length of zero in the TDS stream.
Both those and any so-called "empty" columns will then be stored as NULL.


Theoretical fix:

1. Write a -1 as prefix for null columns?
2. Set column prefix correctly in
dbproc->bcp.host_columns[i]->prefix_len.
3. Read the prefix per normal.
4. Adjust the data stream if necessary (for TDS 5.0).

Here are the columns we're loading. As you can see, we stumble on #15,
the first nullable column:

[37] varley.FreeTDS.1> select colorder, c.xusertype, case isnullable when
1 then 'Y' else 'N' end as 'fNull', cast(t.name as varchar(15)) as type,
c.length, cast(c.name as varchar(22)) as name from syscolumns c join
systypes t on c.xusertype = t.xusertype where object_name(id) =
'all_types_bcp_unittest' order by colorder
[37] varley.FreeTDS.2> go
colorder xusertype fNull type length name
-------- --------- ----- --------------- ------ ----------------------
1 104 N bit 1 not_null_bit
2 175 N char 10 not_null_char
3 167 N varchar 10 not_null_varchar
4 61 N datetime 8 not_null_datetime
5 58 N smalldatetime 4 not_null_smalldatetime
6 60 N money 8 not_null_money
7 122 N smallmoney 4 not_null_smallmoney
8 62 N float 8 not_null_float
9 59 N real 4 not_null_real
10 106 N decimal 5 not_null_decimal
11 108 N numeric 5 not_null_numeric
12 56 N int 4 not_null_int
13 52 N smallint 2 not_null_smallint
14 48 N tinyint 1 not_null_tinyint
15 175 Y char 10 nullable_char
16 167 Y varchar 10 nullable_varchar
17 61 Y datetime 8 nullable_datetime
18 58 Y smalldatetime 4 nullable_smalldatetime
19 60 Y money 8 nullable_money
20 122 Y smallmoney 4 nullable_smallmoney
21 62 Y float 8 nullable_float
22 59 Y real 4 nullable_real
23 106 Y decimal 5 nullable_decimal
24 108 Y numeric 5 nullable_numeric
25 56 Y int 4 nullable_int
26 52 Y smallint 2 nullable_smallint
27 48 Y tinyint 1 nullable_tinyint

--jkl

Attachment: dump
Description: Binary data




Archive powered by MHonArc 2.6.24.

Top of Page