FreeTDS Development Group

[Nem W Schlecht <nem AT emptec.com>]

Hello all,

First off, I just want to thank all of the developers in this community for
continuing work on such a useful and excellent application.  I cannot thank
and appreciate you all enough!  Thank you, Thank You, THANK YOU!!


I recently grabbed the last stable release and came across an issue that
seems to constantly be plaguing me and has been around for a long time.  I
had reported this back in July of 2014, but the proposed solution
(stripping all but one space) would still screw up some of my stored
procedures (that have lots of spaces in strings for formatting purposes).

My original post and reply:
  https://lists.ibiblio.org/sympa/arc/freetds/2014q3/028964.html
  https://lists.ibiblio.org/sympa/arc/freetds/2014q3/028966.html

The issue is with lines 7311-7314 in dblib.c:

   7311                 case NTBSTRINGBIND: /* strip trailing blanks, null
term */
   7312                     while (srclen && src[srclen - 1] == ' ') {
   7313                         --srclen;
   7314                     }

Looks like something good to do, right?  Get rid of any blanks that we
don't need.  However, this keeps messing up my output when I use "defncopy"
to dump out some of my stored procedures.

If it just so happens that you have a large stored procedure or large
string in that stored procedure *and* there are one or more spaces exactly
at a multiple of byte 4000, dblib.c will strip out the spaces, causing
either data corruption or an SQL syntax error in the stored procedure.

I'm attaching an example to reproduce the issue.

Load in 'dbo.big_proc-load.sql' and then immediately run 'defncopy' to dump
it back out.  Each digit should have 40 spaces between it, but on line 96,
it only contains 8 spaces.  dblib grabbed the first 4000 bytes, saw there
there were spaces, and truncated it back to the last non-space character.

I've actually had this happen (just by pure chance) in actual code as well,
where a line like this:

     SELECT some_field_name AS some_field_alias

has been turned into:

     SELECT some_field_nameAS some_field_alias

Thus causing an error the next time the stored procedure is run, since the
field name is now incorrect.

I've modified my copy of dblib.c and commented out lines 7312-7314 (as I
did so 2 years ago and I've had to negative effects), but would like to
hear thoughts/opinions of the developers and would really like this issue
fixed.

Again, thank you all so much for all of your hard work on FreeTDS!


-- 
Nem W Schlecht
 "Perl did the magic.  I just waved the wand."
USE freetds;
GO

CREATE PROCEDURE big_proc
AS
BEGIN
	SELECT '
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
';
END;

USE freetds
GO


CREATE PROCEDURE big_proc
AS
BEGIN
	SELECT '
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7                                        7
	8                                        8
	9                                        9
	0                                        0
	1                                        1
	2                                        2
	3                                        3
	4                                        4
	5                                        5
	6                                        6
	7        7
	8                                        8
	9                                        9
	0                                        0
';
END;

GO