FreeTDS Development Group

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

Hello all!

Longtime user/admirer of the FreeTDS package, so first off I must
thank and praise everybody who has contributed to the development of
this extremely useful package. Thank you! :)

Secondly, I've had a couple of issues with 'defncopy' that I
investigated and fixed on my local copy, but want to know from the
developers if the changes I've made are stupid or not. :)

My first issue was that every now and then 'defncopy' would seem to
eat a space or two.  I'd have a stored procedure with a line like this
(in a SELECT):

     , some_field_name AS some_field_alias

Which would come out of 'defncopy' as:

     , some_field_nameAS some_field_alias

Which then throws an error upon re-loading into a database as the
field "some_field_nameAS" doesn't exist.  When the space *after* the
AS gets eaten, it'll reload successfully, but then my client will have
issues as the field alias is "ASsome_field_alias".

I've seen others report this issue on the internet and with a little
trial and error, I can now replicate the issue with the attached
stored procedure - where it will eat up 50 spaces and leave me with a
bad field alias. (dbo.defncopy_test.sql)  Just load this stored
procedure into tempdb and then dump it out via defncopy and the spaces
between "AS" and "defncopytest" should disappear.  If you add a couple
more of the 50 space concatenation lines in the SELECT, you'll see
that it'll eat spaces inbetween the single quotes as well (very bad,
IMO, since those spaces might be something that I really need).

The issue is a combination of things.  First off, defncopy uses an old
table, syscomments, to find the stored procedure code.  This stores
the code in 4000 character chunks, which defncopy fetches and
re-assembles.  If any of those chunks has trailing spaces, they get
removed by lines 7269-7271 in dblib/dblib.c.  The space(s) have to
occur at a chunk boundry (4000 chars, 8000 chars, etc).  In my case, I
have a large number of stored procedures and about .5% of them have an
issue with whitespace being eaten when I dump them out via 'defncopy'.

My solution: comment out lines 7269-7271 in dblib/dblib.c.  So far, I
haven't seen any adverse issues with having done this, but I don't
know the code well enough to know if I just screwed up something else.
:)

Since syscomment is a deprecated table, defncopy really should be
updated to to use either:
     EXEC sp_helptext 'procowner.procname';
or
     SELECT OBJECT_DEFINITION(OBJECT_ID('procowner.procname'))

I'm not sure about backward compatibility with these, though (I can
test SQL Server 2005 on and both work in all of them).  But either of
these returns the text of a stored procedure in a nicer way, IMO, than
syscomment does.  I prefer using OBJECT_DEFINITION().


My second issue with 'defncopy' is that I recently converted pretty
much everything I work with over to UTF-8.  Besides a few issues
(still no UTF-8 support in grep!), it's gone quite well. :)  My issue
with defncopy is that after I switched my locale from C to C.UTF-8, it
would always core dump.  Increasing the size of sql_text in 546 in
defncopy.c from 8000 to 16000 fixed that problem though and it has
been working perfectly since.


I've attached a patch file for both of these changes and would
appreciate any feedback/discussion. :)


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


CREATE PROC defncopy_test
AS
BEGIN
	SELECT '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '                                                  '
		+  '      '
		+  ' ' AS                                                  defncopytest
		-- note the spaces between 'AS' and 'defncopytest'
		-- they are missing when dumped with defncopy
		;
END
GO

Attachment: defncopy-fix.patch
Description: Binary data