Bullock, Owen
2013-08-12 16:23:26 UTC
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.
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.