FreeTDS Development Group

["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.
>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
>