Skip to Content.
Sympa Menu

freetds - RE: [freetds] native bcp broken

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Thompson, Bill D (London)" <bill_d_thompson AT ml.com>
  • To: "'FreeTDS Development Group'" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] native bcp broken
  • Date: Tue, 6 Jan 2004 10:04:00 -0000

James,

After Christos raised the issue last week, I took a look at the code.
There are a couple of issues with the "native" format.
One is the NULL columns (pretty much understood, I think).
The second is the "prefix length" that is part of the native format.
I think you've also understood this.
In freebcp native format, we call bcp_colfmt for each column, passing -1 as
the prefix length.
-1 in this context means "make your mind up whether a prefix length is
needed or not"
whether there is a "prefix length" or not is dependant on the column
attributes.
For example, it shouldn't be there for a column like "col1 int not null",
but should be present for a column like "col2 int null" or "col3
varchar(30)"
I'm pretty sure that the prefix length is not always being generated
correctly (on output) or interpreted correctly (on input), when -1 has been
specified for the prefix length.

I started working on this last friday, but then saw Christos's patch whch
claimed to fix all the issues.
I thought I'd test his patch before continuing further...

I think there's probably a couple of days programming in this.
I'm happy to do the work, but I can't work on it now until next week :-(

As the problems are isolated to "native format" bcp files, we COULD leave
this until 0.63, when we do the other bcp work.


Bill


> -----Original Message-----
> From: James K. Lowden [SMTP:jklowden AT schemamania.org]
> Sent: 06 January 2004 02:25
> To: TDS Development Group
> Subject: [freetds] native bcp broken
>
> 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 << File: dump >> << File: ATT1526766.txt >>





Archive powered by MHonArc 2.6.24.

Top of Page