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

Replies are listed 'Best First'.
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
i've just found this online String Data Handling (from http://search.cpan.org/~mewp/DBD-Sybase/d +bd-sybase.pod) DBD::Sybase supports CHAR/VARCHAR/BINARY/VARBINARY, limited to 255 cha +racters in length up to version 12.0x. As of 12.5 these datatypes can + be up to 16K in size - but supporting the larger sizes requires that + Open Client 12.5 or later be used. Note that the CHAR type is fixed +length (blank padded). Sybase automatically converts CHAR and VARCHAR data between the charac +ter set of the server (see the syscharset system table) and the chara +cter set of the client, defined by the locale setting of the client. +The BINARY and VARBINARY types are not converted. UTF-8 is supported. See the OpenClient International Developer's Guide in the Sybase OpenC +lient manuals for more on character set issues. Strings can be concatenated using the + SQL operator. [download] it sort of implies that the limitation is stuck at fetching a max of 255 character per blob. Am I wrong(i hope so)? Edit: pre tags replaced with code tags	[reply] [d/l]
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
hello michael i have tried using $source_dbh->do("set textsize 40000"); before the prepare statement and it doesn't work... please help:) thank you. gordon	[reply]
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
hi there whereabts do you set the 'textsize' for freetds?	[reply]
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]