Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Bullock, Owen" <Owen.Bullock AT broadridge.com>
  • To: "FreeTDS AT lists.ibiblio.org" <FreeTDS AT lists.ibiblio.org>
  • Subject: [freetds] bcp issue with TDS 5.0, Sybase, and long rows
  • Date: Mon, 12 Aug 2013 17:23:26 +0100


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.
>From freddy77 AT gmail.com Mon Aug 12 13:59:37 2013
Return-Path: <freddy77 AT gmail.com>
X-Original-To: freetds AT lists.ibiblio.org
Delivered-To: freetds AT lists.ibiblio.org
Received: by lists.ibiblio.org (Postfix, from userid 20217)
id 54B20E938E; Mon, 12 Aug 2013 13:59:37 -0400 (EDT)
X-Spam-Checker-Version: SpamAssassin 3.3.1 (2010-03-16) on
mailman1.ibiblio.org
X-Spam-Level:
X-Spam-Status: No, score=-2.6 required=5.0 tests=AWL,BAYES_00,DKIM_SIGNED,
DKIM_VALID,DKIM_VALID_AU,HTML_MESSAGE,RCVD_IN_DNSWL_LOW autolearn=ham
version=3.3.1
Received: from mail-ve0-f171.google.com (mail-ve0-f171.google.com
[209.85.128.171])
by lists.ibiblio.org (Postfix) with ESMTP id 5B9C5E9373
for <freetds AT lists.ibiblio.org>; Mon, 12 Aug 2013 13:59:33 -0400 (EDT)
Received: by mail-ve0-f171.google.com with SMTP id pa12so5862454veb.30
for <freetds AT lists.ibiblio.org>; Mon, 12 Aug 2013 10:59:33 -0700 (PDT)
MIME-Version: 1.0
X-Received: by 10.220.173.134 with SMTP id p6mr174359vcz.36.1376330372966;
Mon, 12 Aug 2013 10:59:32 -0700 (PDT)
Received: by 10.52.112.194 with HTTP; Mon, 12 Aug 2013 10:59:32 -0700 (PDT)
In-Reply-To:
<50D72CA8081B2E4083B68EAB4F96F52201AA7ECA8C9B AT LDCPCWEXMAA1.bsg.ad.adp.com>
References:
<50D72CA8081B2E4083B68EAB4F96F52201AA7ECA8C9B AT LDCPCWEXMAA1.bsg.ad.adp.com>
Date: Mon, 12 Aug 2013 18:59:32 +0100
Message-ID:
<CAHt6W4f+21z38crM1mA_Hi-5WtyiGXKqt+uPNTD7yxPQ_UafxA AT mail.gmail.com>
From: Frediano Ziglio <freddy77 AT gmail.com>
To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
Content-Type: text/plain; charset=UTF-8
X-Content-Filtered-By: Mailman/MimeDel 2.1.9
Subject: Re: [freetds] bcp issue with TDS 5.0, Sybase, and long rows
X-BeenThere: freetds AT lists.ibiblio.org
X-Mailman-Version: 2.1.9
Precedence: list
Reply-To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
List-Id: FreeTDS Development Group <freetds.lists.ibiblio.org>
List-Unsubscribe: <http://lists.ibiblio.org/mailman/listinfo/freetds>,
<mailto:freetds-request AT lists.ibiblio.org?subject=unsubscribe>
List-Archive: <https://lists.ibiblio.org/sympa/arc/freetds>
List-Post: <mailto:freetds AT lists.ibiblio.org>
List-Help: <mailto:sympa AT lists.ibiblio.org?subject=HELP>
List-Subscribe: <http://lists.ibiblio.org/mailman/listinfo/freetds>,
<mailto:freetds-request AT lists.ibiblio.org?subject=subscribe>
X-List-Received-Date: Mon, 12 Aug 2013 17:59:37 -0000

Hi Owen,
it's a known issue fixed on 5 May this years, both on 0.91 and master
branches.

Last 0.91 snapshot is at
ftp://ftp.freetds.org/pub/freetds/stable/freetds-0.91.89.tar.bz2.

Frediano


2013/8/12 Bullock, Owen <Owen.Bullock AT broadridge.com>

>
> 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.
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>




Archive powered by MHonArc 2.6.24.

Top of Page