FreeTDS Development Group

["James K. Lowden" <jklowden AT schemamania.org>]

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