Today I stumbled onto an interesting problem. I am working on a small system for managing lists of emails. All of the data is stored in a Microsoft SQL Server. One of the tables in the database contains a text column. The table is as follows:

CREATE TABLE [Web_Team].[tblDept] (
    [deptID] [int] IDENTITY (1, 1) NOT NULL ,
    [deptName] [char] (100) NOT NULL ,
    [defaultMsg] [text] NOT NULL ,
    [active] [bit] NOT NULL 
)
[download]

This is the only table in the database that contains a text type column. Any select statements that included the defaultMsg column was behaving funny. I was able to reproduce the behavior using the simplest of select statements.

SELECT * FROM tblDept
[download]

This should return everything within the table, but it doesn't when the defaultMsg in one of the rows is larger than 81 characters. I added the following option to the connect statement to fix the problem.

{'LongTruncOk' => 0, LongReadLen => 1000000 }
[download]

After adding this code to the connect statement my program works as I expected. Because this looked like the problem I ran a trace on the statement and found that the default long truncation length for a text column ( or LONG VARCHAR ) was set at 81. This is what puzzles me. Most of my development has been using MySQL not Microsoft SQL Server.

Is there any special reason that the max length would be set that low? Is there a better way of solving this problem other than the solution I have found already?

my $DSN = 'driver={SQL Server};server=devcgi;tcpip=123.123.123.123:123
+4;database=testbed;uid=web;pwd=team;'; 

my $dbh = DBI->connect("dbi:ODBC:$DSN",'web','team', {'LongTruncOk' =>
+ 0, LongReadLen => 1000000 })
   || die "Couldn't open database: $DBI::errstr\n";

$dbh->trace(2, "test" );

my $sth = $dbh->prepare('SELECT * FROM tblDept')
   || die "Couldn't prepare statement: $DBI::errstr\n";

$sth->execute()
   || die "Couldn't execute statement: $DBI::errstr\n";

my $temp = $sth->fetchall_arrayref({})
   || die "Couldn't fetch: $DBI::errstr\n";

print Dumper($temp),"\n";

$dbh->disconnect();
[download]