DBI::Sybase -retrieving BLOB values from ms-sql question

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

This is really simple. I am querying a MS-SQL Server table that has TEXT in its blob field.It stores descriptions.
When i run an sql such as below from Microsoft Enterprise console,

"                              SELECT IssueNo , SUBSTRING(IssueDescrip
+tion,0,DATALENGTH(IssueDescription)+1) AS IssueText
                                FROM Issues
                                WHERE     AccountNo = '1036'
                                ORDER BY IssueNO ASC "
[download]

it returns the blob value stored in "IssueText" in completeness but when i ran the PERL script to get it, the dump out showed the value with a length of 255 characters! This is utterly strange as I have read around online and have tried using differt ways of querying (ie such as syb_ct_get_data) and can't get anywhere...

At the moment,here is how I do the query :

                        $stmt =             $source_dbh->prepare($self
+->get_T3_issue_ids_sql($T3_account_id));
                        $status = $stmt->execute();
                        if ($status)
                        {
                                $res = $stmt->fetchall_hashref('IssueT
+ext');
                        }
                        debug(20,qq|Sql to get_issues for account,$T3_
+account_id : \"|.$self->get_T3_issue_ids_sql($T3_account_id).qq|\"\n|
+);

                        #debug more...
                        foreach my $elem(%{$res})
                        {
                                if (defined($res->{$elem}->{'IssueText
+'}))
                                {
                                        debug (20,qq|$res->{$elem}->{'
+IssueNo'}:Current IssueText-\"|.$res->{$elem}->{'IssueText'}.qq|\"\n|
+);
                                }
                        }
[download]

Very sorry to trouble you guys but I really need your help.
thank you in advance.

Gordon,
anexiole@gmail.com

20060707 Janitored by Corion: Changed PRE tags into code tags, as per Writeup Formatting Tips

Comment on DBI::Sybase -retrieving BLOB values from ms-sql question Select or Download Code

Replies are listed 'Best First'.
Re: DBI::Sybase -retrieving BLOB values from ms-sql question by liverpole (Monsignor) on Jul 07, 2006 at 11:17 UTC
Hi anexiole, Since nobody else has answered your question, I'll take a shot at it... My guess is that a "blob" in ms-sql has an internal length of 255, and somehow your "Microsoft Enterprise console" is simply reformatting it, to remove extra spaces. You haven't shown what your output looks like, so it's hard to be sure, but if that is what's happening, how about just doing the same thing in Perl? Try something like the following: `foreach my $elem(%{$res}) { if (defined($res->{$elem}->{'IssueText'})) { my $result = $res->{$elem}->{'IssueNo'}; $result =~ s/\s+$//; # Delete trailing whitespace debug (20,"$result:Current IssueText-\"$result\"\n\|); } }` [download] (Note that I've cleaned up the multiply-concatenated qq syntax which made things a bit more difficult to read). Does that give you something more like what you're getting with "Microsoft Enterprise console"? s''(q.S:$/9=(T1';s;(..)(..);$..=substr+crypt($1,$2),2,3;eg;print$..$/	[reply] [d/l]
Re^2: DBI::Sybase -retrieving BLOB values from ms-sql question by anexiole (Novice) on Jul 07, 2006 at 22:41 UTC
hello there good day. This approach looks reasonable and i want to try it. I will do so on monday when i get back to work. many thanks for your help:) gordon	[reply]
Re^3: DBI::Sybase -retrieving BLOB values from ms-sql question by Anonymous Monk on Jul 10, 2006 at 02:19 UTC
hello there unfortunately, it doesn't make a difference ... sigh	[reply]
Re: DBI::Sybase -retrieving BLOB values from ms-sql question by imp (Priest) on Jul 07, 2006 at 12:58 UTC
DBI::FAQ - 5.2 How do I handle BLOB data with DBI? The key is $dbh->{LongReadLen}.	[reply]
Re^2: DBI::Sybase -retrieving BLOB values from ms-sql question by derby (Abbot) on Jul 07, 2006 at 13:26 UTC
That's what I was thinking too but looking at the docs for DBD::Sybase: Note that LongReadLen has no effect when using DBD::Sybase with an MS-SQL server. And further on: Sybase defaults the TEXTSIZE attribute (aka LongReadLen) to 32k, but MS-SQL 7 doesn't seem to do that correctly, resulting in very large memory requests when querying tables with TEXT/IMAGE data columns. The work-around is to set TEXTSIZE to some decent value via $dbh->{LongReadLen} (if that works - I haven't had any confirmation that it does) or via $dbh->do("set textsize <somesize>"); So I think the $dbh->do( "set textsize <somesize>" ) may be your only option -- but luckily mpeppler is a monk and he'll probably be along soon enough with a better answer. -derby	[reply]
Re^3: DBI::Sybase -retrieving BLOB values from ms-sql question by mpeppler (Vicar) on Jul 07, 2006 at 15:44 UTC
You are correct - the OP needs to call $dbh->do("set textsize somevalue"); to get things to work. The reason for this is that when $dbh->{longReadLen} is updated DBD::Sybase calls a Sybase API call under the cover, and this API call is not one that is compatible between MS-SQL and Sybase. Michael	[reply]
Re^4: DBI::Sybase -retrieving BLOB values from ms-sql question by anexiole (Novice) on Jul 07, 2006 at 22:48 UTC
Re^5: DBI::Sybase -retrieving BLOB values from ms-sql question by mpeppler (Vicar) on Jul 08, 2006 at 06:01 UTC
Re^4: DBI::Sybase -retrieving BLOB values from ms-sql question by Anonymous Monk on Jul 10, 2006 at 02:20 UTC
Re^5: DBI::Sybase -retrieving BLOB values from ms-sql question by mpeppler (Vicar) on Jul 11, 2006 at 17:18 UTC
Re^3: DBI::Sybase -retrieving BLOB values from ms-sql question by imp (Priest) on Jul 07, 2006 at 14:13 UTC
Good catch. It's been a little while since I used DBD::Sybase, as I switched over to DBD::ODBC after discovering iodbc Depending on the platform the textsize can be controlled by external configuration as well, e.g. freetds has a 'textsize' setting	[reply]
Re^4: DBI::Sybase -retrieving BLOB values from ms-sql question by anexiole (Novice) on Jul 07, 2006 at 22:44 UTC
Re^5: DBI::Sybase -retrieving BLOB values from ms-sql question by mpeppler (Vicar) on Jul 08, 2006 at 06:04 UTC
Re^2: DBI::Sybase -retrieving BLOB values from ms-sql question by anexiole (Novice) on Jul 07, 2006 at 22:42 UTC
yeah i read through the dbd::sybase and have tried setting LongReadLen to really big values (like 80000,8000,4000) and a small one (1) but they all do not seem to affect the output.Sigh:(	[reply]
Re^3: DBI::Sybase -retrieving BLOB values from ms-sql question by anexiole (Novice) on Jul 10, 2006 at 04:09 UTC
guys!! thank you for your help! I solved the issue! I shouldn;t have used the SUBSTRING function within the SQL!! my previous Sql read : SELECT IssueNo , SUBSTRING(IssueText,0,DATALENGTH(IssueText)+1) AS IssueText FROM Issues WHERE AccountNo = 'account-number' AND IssueNo = 'issue-number' ORDER BY IssueNO ASC	[reply]


Just another Perl shrine
	PerlMonks