FreeTDS Development Group

[Stephen Marshall <smarshall AT kayak.com>]

I've found a bug in freebcp. Any record containing the DATE data type fails
to bulk load using freebcp.  Since the DATE data type is specific to Sybase
data servers, this does not affect use of freebcp with MSSQL.

This email is rather long, but contains all the details needed to reproduce
the error.  It also contains my initial exploration of the problem.  I'd
really appreciate it is a person with more knowledge of the code than me
could take a look at this issue.

Thanks,
Steve
-----

REPRODUCING THE ERROR:

The following test shows the problem (see files in the
freebcp_date_error.tar.gz)

1. Create a table called bcp_test with a single column of date type
   (see attached SQL file create_bcp_test.sql).
2. Bulk load one record into this table using freebcp, like this:
  export TDSDUMP="./tdsdump.txt"
  freebcp <dbname>..bcp_test in bcp_test.txt -U<user> -P<password>
--S<server> -c

Where <user>, <password>, and <server> are the connection credentials for
your database server, and <dbname> is the name of the database holding the
table. The data file bcp_test.txt contains only a single record with a
single textual date of the form YYYY-mm-dd.

The freebcp commands returns an error of this form (where ### are ids
specific to the database being used):
   Bad row data received from the client while bulk copying into object ###
partition ### in database ###.
  Received a row of length 10 whilst maximum or expected row length is 6.

I tested this running freebcp on Mac OS X 10.8.5 against a Sybase ASE 15.7
server running on CentOS 6.

CONFIRMATION THAT THIS IS AN ERROR:

Running the same command with the bcp utility provided with Sybase ASE 15.7
succeeds without error.  A simple insert of the same data also succeeds,
e.g.
    insert into bcp_test values('2014-01-04');

EXPLORATION OF THE ERROR:

Changing the data type in bcp_test from DATE to DATETIME causes freebcp to
succeed.  However, this increases the storage size from 4 to 8 bytes and
causes values to be returned with a complete date time (e.g. "Jan  4 2014
12:00:00:000AM"), rather than just a date.

Interestingly, comparison of the cases where DATE and DATETIME are used
shows Sybase ASE 15.7 returns the same column_type and column_size values
for both cases (61 and 8, respectively). Only the usertype column differs
(37 for DATE, 12 for DATETIME).   The type and length seem incorrect for
the DATE type, given this information from the systypes system table:
 usertype type length name



 -------- ---- ------ --------



       37   49      4 date



       12   61      8 datetime






See tdsdump.txt-date_vs_datetime for details of the comparison.  The type
information comes from the TDS packet returned from a "FMTONLY" query to
get all columns (but no rows) from the table bcp_test.

NEXT STEPS:

Frankly, after isolating this problem to these byte codes, I thought fixing
this would be easy.  However, there are a number of transformations and
special cases in the data conversion code from the tds and dblib libraries.
 After inserting a special case to set type to 49 (SYBDATE) and length to 4
if usertype is 37, freebcp failed with this error, caused by a failure of
the tds_convert function:
   Requested data conversion does not exist

It seems additional logic, perhaps in or around tds_convert() is needed to
support the SYBDATE data type.  I've spent about day trying to find the
right place to insert this logic, but, at this point, I am chasing my tail.
 If someone on the list has insight into how to add support for a new data
type, I'd appreciate some direction.

Thanks,
Steve

Attachment: freebcp_date_error.tar.gz
Description: GNU Zip compressed data