[freetds] bcp issue with TDS 5.0, Sybase, and long rows

Bullock, Owen Owen.Bullock at broadridge.com
Mon Aug 12 12:23:26 EDT 2013


Apologies in advance for the long post, hopefully its of interest to someone.


I'm in the process of moving some old C/Sybase db-lib code over to FreeTDS but I've run into an issue when we're programmatically bcp'ing long table rows ( > 255 chars) which are nullable.  The bcp either fails or garbles my data.

I've read what I can on the bulk copy offset/adjustment table (http://freetds.schemamania.org/tds.html#p7), and fired up Ribo so I could compare packets between Sybase and FreetDS.

Its apparent that the Sybase library calculates the high-byte adjustment table rather differently to FreeTDS.  (Perhaps FreeTDS is more mssql focussed here? Or have Sybase changed this recently? - I'm running on Sybase 15.7 and can confirm this is the same back to 12.5 but that's the oldest I have access to)

Has anyone else run into this issue?  (I suspect not many people are doing this on Sybase - we're motivated by the need for Bigint support on our old dblib code)


example - I'm copying a row of 5 columns into a table composed of char(255) columns. These are the calculated offsets:

   0068
   00CC
   0130
   0131
   0132

FreeTDS calculates (correctly, as per spec) the offset/adjustment table as this:

1+ncols   Adjustment          Offset
+--------+-------------------+-------------------+
|  06    | 01 01 01 00 00 00 | 32 31 30 cc 68 04 |
+--------+-------------------+-------------------+

However, the Sybase lib sends this:

1+ncols   Adjustment          Offset
+--------+-------------------+-------------------+
|  06    | 04                | 32 31 30 cc 68 04 |
+--------+-------------------+-------------------+


The adjustment table is hugely different, and of course my data gets garbled when I send it using FreeTDS.

After some analysis of various scenarios - it seems to me that Sybase is indicating the column number where the high-order byte *changes* (unless its the last column - in which case it just drops it.)  I'd welcome other peoples thoughts on this.



Here's a couple more examples to show this:

         1+ncols   Adjustment          Offset
        +--------+-------------------+-------------------+
FreeTDS |  06    | 01 01 00 00 00 00 | 32 31 cd cc 68 04 |
Sybase |  06    | 05                | 32 31 30 cc 68 04 |
        +--------+-------------------+-------------------+

-   change in column 5


         1+ncols   Adjustment          Offset
        +--------+-------------------+-------------------+
FreeTDS |  06    | 04 04 04 02 01 00 | 7a 79 78 fc 80 04 |
Sybase |  06    | 04 04 03 02       | 32 31 30 cc 68 04 |
        +--------+-------------------+-------------------+

-   in the above one the offset goes from 0x2fc to 0x478, so Sybase indicates this high jump of two by putting 04 in the adjustment table twice.


         1+ncols   Adjustment          Offset
        +--------+-------------------+-------------------+
FreeTDS |  04    | 01 00 00 00       | 30 cc 68 04       |
Sybase |  04    |                   | 30 cc 68 04       |
        +--------+-------------------+-------------------+

-   high-byte change is the last column so Sybase doesn't mention it. This also happens if the following cols are null and empty



In my code this saves only a handful of bytes, but i guess if you've got long rows with many columns then you'd start to see some advantage.


Any thoughts?

regards,
Owen

This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail and delete the message and any attachments from your system.


More information about the FreeTDS mailing list