Cannot retrieve more than 80 chars using DBI:ODBC

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

I'm having some anoying problems with a small script which accesses a database.

I'm connecting to an MS SQL server through an ODBC connection using DBI and DBD:ODBC... this works fine until i attempt to fetch text data with more than 80 chars. It will also work at 81 chars, but will then only return the first 80 chars.. anything more than that and it just returns a blank field.

Is this a known bug? or some sort of setting that can be made?

Any help or pointers to documents that might contain help will be greatly apreciated. btw. the code is running on an NT server with IIS 4.0 and MS SQL 7.0. The perl distribution is from activestate.

Comment on Cannot retrieve more than 80 chars using DBI:ODBC

Replies are listed 'Best First'.
Re: Cannot retrieve more than 80 chars using DBI:ODBC by dave_aiello (Pilgrim) on Sep 13, 2001 at 16:23 UTC
There is an attribute to the ODBC driver called 'LongReadLen'. My experience with Windows NT 4.0 and SQL Server 6.5 is that you must set LongReadLen to the size of the buffer that you need to read the column data into, otherwise you will only get 80 characters. If you have a handle to a database connection called $dbh, you should be able to do: `$dbh->{'LongReadLen'} = 500; # if length of data to be read is 500` [download] I would do this before I issued the $dbh->prepare() with the embedded SQL statement. BTW, if you search on PerlMonks or Google for the word "LongReadLen", you should find plenty of more complete examples. The PerlMonks search engine isn't working for me at the moment. Dave Aiello Chatham Township Data Corporation	[reply] [d/l]
Re: Re: Cannot retrieve more than 80 chars using DBI:ODBC by kapper (Chaplain) on Sep 13, 2001 at 16:33 UTC
Thanks.. this solved my problem =) I tried using the perlmonks search and supersearch feature.. it seemed to work fine, but not knowing what i was looking for, all i could search for was DBI, ODBC, MS SQL, limit and so forth...	[reply]
Re: Cannot retrieve more than 80 chars using DBI:ODBC by wardk (Deacon) on Sep 13, 2001 at 18:16 UTC
was going to make a feeble attempt at humor by mentioning that this must be a relic from Microsoft's past when they did their computing on 80 character punchcards...but then I decided it was probably just not that funny... fwiw, Win32::ODBC handles > 80 without switches, at least that is my memory, then again I seem to have lost memories of microsoft using punchcards....wait, I decided not to go there...nevermind :-) thankfully useful information was posted about this before I arrived	[reply]
Re: Re: Cannot retrieve more than 80 chars using DBI:ODBC by kapper (Chaplain) on Sep 18, 2001 at 12:52 UTC
he he.. well thanks for the tip.. however, I need to use DBI since the code I am writing must work both under windows and solaris, with two different dbms's...	[reply]