iifedy / aseisql

Automatically exported from code.google.com/p/aseisql
0 stars 0 forks source link

Problem with very large stored procedure #80

Open GoogleCodeExporter opened 8 years ago

GoogleCodeExporter commented 8 years ago
When I open a very large stored procedure, sometimes some words together. 
This happens in the latest version, not the original. I see it only when I 
try to compile the stored procedure

Ej: where campo = @valor and

Appears...

where campo = @valorand

Original issue reported on code.google.com by gdagu...@gmail.com on 29 May 2010 at 8:13

GoogleCodeExporter commented 8 years ago
Here it happens in the same way, also if I've use indention:

i.e.
select a.name,
       a.surname,
       b.address_1,
 b.address_2,              <--- this is the strange!
       c.postal_code,
       ...

Normally it doesn't matter but I suppose it's the same problem. I thought 
before it could come from some internal replacing of tabs, but it isn't.

Original comment by ingmar.p...@gmail.com on 9 Feb 2011 at 3:21

GoogleCodeExporter commented 8 years ago
This is due to the way Sybase stores procedures internally. The VARCHAR field 
is limited to 255 characters, so the stored procedure code is stored across 
several varchar fields.

The problem with this is that a varchar cannot end with whitespace, so if you 
have critical whitespace at the end of a 255 character block, and none after 
it, you will have this problem.

It's more likely to happen the longer your stored procedure, but you can work 
around it by adding more spaces earlier so your split occurs mid or end-word.

I found this when trying to do a comparison between two databases to determine 
differences. 

The way to to fix this in the code is to get the length of the returned string, 
and space pad it to 255 characters.
SQL I use
"select text from syscomments WHERE id=($object_id) order by texttype, colid2, 
colid";
Sample perl that fixes the issue:
    while (my @text_ary = $sth->fetchrow_array())
    {
        # work around bizarre truncation issue with spaces and varchars
        if (length($lasttext) < 255) {
            $text .= " " x (255-length($lasttext)) . $text_ary[0];
        } else {
                $text .= $text_ary[0];
        }
            $lasttext = $text_ary[0];
    }

Original comment by Hrdwr...@gmail.com on 9 Sep 2013 at 5:58

GoogleCodeExporter commented 8 years ago
Thank you very much for your reply!

Daniel

Original comment by gdagu...@gmail.com on 9 Sep 2013 at 9:30