Perl Database Select Results Being Truncated

cpatrick has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: Perl Database Select Results Being Truncated by injunjoel (Priest) on Apr 28, 2005 at 16:02 UTC
Greetings all, Disclaimer: The following is a best guess. I am by no means a Sybase guru. My first question would be, are you sure the datatype should be varchar(1000)? That seems to ring a bell in my memory about varchar size limit being <=255 or something like that. Perhaps Sybase is converting it internally into something like a TEXT field or Blob since you mentioned that a direct query gives you the results you are looking for? I am wondering if Sybase is labeling the column as a varchar when Perl is trying to use it? possibly leading to the clipping you are seeing. Not really an answer I know but one thing to possibly try is changing the datatype of that column to a Text/Blob and read the docs about LongReadLen to figure out how to fetch it out. -InjunJoel "I do not feel obliged to believe that the same God who endowed us with sense, reason and intellect has intended us to forego their use." -Galileo	[reply]
Re^2: Perl Database Select Results Being Truncated by Anonymous Monk on Apr 28, 2005 at 16:13 UTC
I'll talk to our database team. I originally wanted a blob type, but they said that they would give me a varchar 1000 to fit my need, but that raises a good point if Perl somehow truncates based on the 255 typical limitation of varchar. The thing that confuses me though is that if 255 is the varchar limit, why the sybase dbase allows it, and allows for me to do a command like select and retrieve the entire entry? Thanks again for any continued assistance.	[reply]
Re^3: Perl Database Select Results Being Truncated by Anonymous Monk on Apr 28, 2005 at 17:14 UTC
Hi again- I've tried the LongReadLen, and it still truncates. The database team has said that they kept the type varchar to save space in the database, and that it is possible to go over the 255 character contstraint for the varcar, so I am at a loss why the truncation still happens. Here is a clip of the new code with the LongReadLen attribute set: $dbh->{LongReadLen} = 65536; $statement = 'SELECT Description FROM DevelopmentTool WHERE ShortName like "%PSP%"'; $sth = $dbh->prepare($statement); $sth->execute; while (($Description) = $sth->fetchrow) { print $Description . "\n\n"; } Talk soon.	[reply]
Re: Perl Database Select Results Being Truncated by mpeppler (Vicar) on Apr 28, 2005 at 15:44 UTC
Which database is this, and what perl module/client are you using? Michael	[reply]
Re^2: Perl Database Select Results Being Truncated by Anonymous Monk on Apr 28, 2005 at 16:00 UTC
I am using a Sybase database, and the Perl DBI module. I even tried to write a simple, non-CGI version of the script, where the perl code only does the select on the trouble column, and print to the command line...same results.	[reply]
Re^3: Perl Database Select Results Being Truncated by mpeppler (Vicar) on Apr 28, 2005 at 19:08 UTC
Ah. Then please make sure that you have Sybase 12.5 Client Libraries installed, and that your DBD::Sybase module is recent (1.04 or later), and that it was built with the 12.5 libraries. Otherwise the client side of the connection tells the server side that it doesn't know about "wide" varchar columns, and you get the 255 char truncation. Michael	[reply]
Re: Perl Database Select Results Being Truncated by TedPride (Priest) on Apr 28, 2005 at 17:24 UTC
Another, much more likely possibility is that the information is being cropped on input rather than output. Have you checked your database file, either by looking at the file itself or through an admin utility, to make sure there's more than 255 characters of data? And yes, Sybase varchar is usually 255 characters max.	[reply]
Re^2: Perl Database Select Results Being Truncated by mpeppler (Vicar) on Apr 28, 2005 at 19:12 UTC
The 255 char limit was dropped in 12.5, and is now raised to a few bytes shorter than a page (a page being 2k, 4k, 8k or 16k). However as I mentioned above, because this change requires a change in the protocol between client and server you need to have a (relatively) recent version of the client for this to work correctly. Michael	[reply]
Re^3: Perl Database Select Results Being Truncated by Anonymous Monk on Apr 29, 2005 at 17:12 UTC
That did it...thank you so much for the help. I was pointing to older versions of the perl executable, and once I updated to versino 5.8.3, it worked the first time. Thanks again for the help. Chris	[reply]
Re^2: Perl Database Select Results Being Truncated by cpatrick (Novice) on Apr 28, 2005 at 17:25 UTC
Hi there- Actually, that was the first thing I did. Within the database file, I can query and retrieve the entire entry without truncation, so I know the file is valid. Thanks again for all of the help...	[reply]
Re^3: Perl Database Select Results Being Truncated by injunjoel (Priest) on Apr 28, 2005 at 18:37 UTC
Greetings again, I read through the source of DBD::Sybase and came upon this little snippet `# Calling sp_datatype_info returns the appropriate data for the server + that # we are currently connected to. # In general the data is static, so it's not really necessary, but ASE + 12.5 # introduces some changes, in particular char/varchar max lenghts that + depend # on the server's page size. 12.5.1 introduces the DATE and TIME datat +ypes.` [download] might be worth investigating? If not, I think mpeppler is the author of this module, perhaps you can /msg him about this. -InjunJoel "I do not feel obliged to believe that the same God who endowed us with sense, reason and intellect has intended us to forego their use." -Galileo	[reply] [d/l]