[freetds] The behavior of bcp, blk and insert is not corresponding.

ZIGLIO, Frediano, VF-IT Frediano.Ziglio at vodafone.com
Fri Mar 16 05:31:58 EDT 2007


> 
> Hi, 
> 
> I've made this patch to change characters ,if which are all 
> space, into "0".
> It seems to do what I want to do. 
> Could someone give me some advice about my patch?
> 
> Thank you for your help.
> 
> ==============================================================
> ==========
> --- freetds-0.64/src/tds/convert.c.ORG  Wed Mar  7 14:32:33 2007
> +++ freetds-0.64/src/tds/convert.c      Thu Mar 15 19:02:50 2007
> @@ -2879,22 +2879,24 @@
>         { blank = ' ' };
>         const char *p;
>         int sign;
>         unsigned int num;       /* we use unsigned here for 
> best overflow check */
> 
>         p = buf;
> 
>         /* ignore leading spaces */
>         while (p != pend && *p == blank)
>                 ++p;
> -       if (p == pend)
> -               return TDS_CONVERT_SYNTAX;
> +       if (p == pend) {
> +               p--;
> +               *p = '0';
> +       }
> 
>         /* check for sign */
>         sign = 0;
>         switch (*p) {
>         case '-':
>                 sign = 1;
>                 /* fall thru */
>         case '+':
>                 /* skip spaces between sign and number */
>                 ++p;
> @@ -2954,22 +2956,24 @@
>         { blank = ' ' };
>         const char *p;
>         int sign;
>         TDS_UINT8 num;          /* we use unsigned here for 
> best overflow check */
> 
>         p = buf;
> 
>         /* ignore leading spaces */
>         while (p != pend && *p == blank)
>                 ++p;
> -       if (p == pend)
> -               return TDS_CONVERT_SYNTAX;
> +       if (p == pend) {
> +               p--;
> +               *p = '0';
> +       }
> 
>         /* check for sign */
>         sign = 0;
>         switch (*p) {
>         case '-':
>                 sign = 1;
>                 /* fall thru */
>         case '+':
>                 /* skip spaces between sign and number */
>                 ++p;
> ==============================================================
> ==========
> 

Mmm... p is const char* so you should have a warning. Also if p == buf
== pend you have a buffer underflow.

This patch have same results without these issues

diff -u -1 -0 -r1.171 convert.c
--- src/tds/convert.c   26 Dec 2006 14:56:21 -0000      1.171
+++ src/tds/convert.c   16 Mar 2007 09:27:10 -0000
@@ -2946,22 +2946,24 @@
        { blank = ' ' };
        const char *p;
        int sign;
        unsigned int num;       /* we use unsigned here for best
overflow check */

        p = buf;

        /* ignore leading spaces */
        while (p != pend && *p == blank)
                ++p;
-       if (p == pend)
-               return TDS_CONVERT_SYNTAX;
+       if (p == pend) {
+               *res = 0;
+               return TDS_SUCCEED;
+       }

        /* check for sign */
        sign = 0;
        switch (*p) {
        case '-':
                sign = 1;
                /* fall thru */
        case '+':
                /* skip spaces between sign and number */
                ++p;
@@ -3021,22 +3023,24 @@
        { blank = ' ' };
        const char *p;
        int sign;
        TDS_UINT8 num;          /* we use unsigned here for best
overflow check */

        p = buf;

        /* ignore leading spaces */
        while (p != pend && *p == blank)
                ++p;
-       if (p == pend)
-               return TDS_CONVERT_SYNTAX;
+       if (p == pend) {
+               *res = 0;
+               return TDS_SUCCEED;
+       }

        /* check for sign */
        sign = 0;
        switch (*p) {
        case '-':
                sign = 1;
                /* fall thru */
        case '+':
                /* skip spaces between sign and number */
                ++p;

The question is: "is it correct? do all library behave like this?".
We'll have to try.

> 
> MATSUMOTO, Tadashi wrote:
> > MATSUMOTO, Tadashi wrote:
> >> Thank you! The problem is solved. 
> >> It succeeded as follows. 
> >>
> >> However, another problem occurred. Details E-mail separately. 
> > 
> > The another problem is this. (only tested on 0.64)
> > 
> >  Freebcp and the blk function cannot insert space into a 
> numeric column. 
> >  The "insert" statement on tsql can insert space into a 
> numeric column as 0. 
> > 
> >  Sybase bcp and the blk function can insert space into a 
> numeric column as 0. 
> >  The "insert" statement on Sybase isql cannot insert space 
> into a numeric column. 
> > 
> > Why have the behavior of Sybase and FreeTds reversed?
> > 
> > thanks,
> > 
> > tsql 
> > ------------------------
> > drop table dbo.test_me
> > create table dbo.test_me (
> >     i integer not null primary key,
> >     c char(1) not null,
> >     v varchar(10) null)
> > insert into dbo.test_me values (3,' ','string')
> > insert into dbo.test_me values (1,' ','string2')
> > insert into dbo.test_me values (2,' ',null)
> > insert into dbo.test_me values (4,' ',null)
> > select * from dbo.test_me
> > go
> > i       i1      i2      c1      c2      v1      v2
> > 1       0       1       a       a       string  string2
> > 2       2       0       b       b       string  string2
> > 3       3       3               c       string  string2
> > 4       4       4       d               string  string2
> > 5       5       5       e       e               string2
> > 6       6       6       f       f       string         
> > 7       7       7       g       g       string  string2
> > 8       8       NULL    g       NULL    string  NULL
> > ----------------
> > 

I don't understand... I think that the create table is not correct, you
created a table with 3 fields and select returned 8 fields...

> > freebcp
> > ------------------------
> > % freebcp "test_me" "out" "test_me.txt" -Sxxxx -c -Uuser -Ppass
> > 
> > Starting copy...
> > 
> > 8 rows copied.
> > % tsql .....
> > truncate table test_me
> > go
> > select * from test_me
> > go
> > i       i1      i2      c1      c2      v1      v2
> > % vi test_me.txt
> > % diff test_me.txt.orig test_me.txt
> > 1,2c1,2
> > < 1     0       1       a       a       string  string2
> > < 2     2       0       b       b       string  string2
> > ---
> >> 1             1       a       a       string  string2
> >> 2     2               b       b       string  string2
> > % freebcp "test_me" "in" "test_me.txt" -Sxxxx -c -Uuser -Ppass
> > 
> > Starting copy...
> > 
> > Msg 20050, Level 4
> > Attempt to convert data stopped by syntax error in source field
> > 
> > Msg 20050, Level 4
> > Attempt to convert data stopped by syntax error in source field
> > 
> > 6 rows copied.
> > % tsql .......
> > select * from test_me
> > go
> > i       i1      i2      c1      c2      v1      v2
> > 3       3       3               c       string  string2
> > 4       4       4       d               string  string2
> > 5       5       5       e       e               string2
> > 6       6       6       f       f       string   
> > 7       7       7       g       g       string  string2
> > 8       8       NULL    g       NULL    string  NULL
> > -----------------------------
> 

freddy77




More information about the FreeTDS mailing list