Re: Records containing DATE data types fail to bulk load with freebcp

2014/1/15 James K. Lowden <[email protected]>:
> On Mon, 13 Jan 2014 11:16:25 -0500
> Stephen Marshall <[email protected]> wrote:
>
>> 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.
>
> You may find the work Frediano has done with ODBC for TDS 7.3 types is
> useful to you.
>
> Start with tds_willconvert(), some of which is generated from a perl
> script.  You'll also need a struct to hold your DATE components in
> wire format if it doesn't fit something we already have.  Then for
> tds_convert() you'll need
>
> 1.  A function that accepts a DATE and returns at least VARCHAR,
> whatever is commensurate with tds_willconvert().
>
> 2.  For each type that can be converted to DATE, add the logic to the
> switch in the function that accepts that type in convert.c.
>
> 3.  In db-lib, you'll need SYBDATE and the same struct that libtds uses
> fo the wire format.  You want to extend dbdatecrack(), too, lest you
> start parsing strings instead when you want to know the month in your
> application.
>
> Conversion should be symmetrical: if A -> B then B -> A.
>
> Now you have a DATE structure and the functionality to convert between
> DATE and strings.  The changes to freebcp fall out pretty easily, as
> you've already seen.
>
> HTH.
>

I think adding a type is quite complicated at this time. I should add
some guide and probably refactor some code. Every type needs (probably
list not complete):
- some changes in the protocol to tell server we support this type;
- a binary representation for this type;
- function to handle data from server;
- function to handle data to server;
- conversion table;
- conversion function (to and from);
- functions (how many I don't know) to ask informations for the type
(like length, precision, scale) which sometimes change a bit from
library to library.

src/tds/data.c actually collect a bit of these information (functions
to read/write) in a single place but the others. Actually BCP is not
on these list of functions (unfortunately).

For DATE/TIME (Sybase). These types are quite easy to implement. DATE
is the date part or TDS_DATETIME while TIME is the time part of
TDS_DATETIME. So conversions are quite easy, just convert to another
already supported type before converting to another type.

Another problem is to define the proper type. If the official
(dblib/ctlib/odbc) offer already a binary type you need to stick to it
for compatibility. If not you can define the type as you like. I think
Sybase defined the type for DATE/TIME (I'm not sure, just question to
check documentation).

Asking information is really the tricky bit as this code is spread all
over the places :(
Too much places too much switches!

Frediano