freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

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

From: "MATSUMOTO, Tadashi" <pineroot AT yahoo.co.jp>
To: freetds AT lists.ibiblio.org
Subject: Re: [freetds] The behavior of bcp, blk and insert is not corresponding.
Date: Wed, 21 Mar 2007 12:53:05 +0800

Hi,

> 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

It is a mistake. Thank you for revising it.

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

Sorry, I made a mistake, too. The correct one is as follows.

drop table dbo.test_me
create table dbo.test_me (
i integer not null primary key,
i1 integer not null,
i2 integer null,
c1 char(1) not null,
c2 char(1) null,
v1 varchar(10) not null,
v2 varchar(10) null)
insert into dbo.test_me values (1,' ',1, 'a','a','string','string2')
insert into dbo.test_me values (2,2 ,' ','b','b','string','string2')
insert into dbo.test_me values (3,3 ,3, ' ','c','string','string2')
insert into dbo.test_me values (4,4 ,4, 'd',' ','string','string2')
insert into dbo.test_me values (5,5 ,5, 'e','e',' ','string2')
insert into dbo.test_me values (6,6 ,6, 'f','f','string',' ')
insert into dbo.test_me values (7,7 ,7, 'g','g','string','string2')
insert into dbo.test_me values (8,8 ,null, 'g',null,'string',null)
select * from dbo.test_me
go
i i1 i2 c1 c2 v1 v2

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

This is that I wanted to question.
How do you think?

Thank you for your help.

>> 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
>> > -----------------------------

Re: [freetds] The behavior of bcp, blk and insert is not corresponding., MATSUMOTO, Tadashi, 03/21/2007