FreeTDS Development Group

["Thompson, Bill D (London)" <bill_d_thompson AT ml.com>]

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