dbd::sybase string cut at 78 characters?

[rgcosma]

Wise monks,

I have a seemingly simple insert and can't figure whether the problem is in DBI or in freetds (asked there too, no feedback yet). Data is an e-mail in stdin

while (<STDIN>) {$orig .= $_; $count++; last if ($count>100 || /\w{70,
+}/);
chomp($orig);$orig=~s/\r//g;$orig=~s/\n/\t/g; #print STDERR $orig;
$db1 = DBI->connect("dbi:Sybase:server=$dbserver;database=$dbase;", $u
+ser, $passwd, { syb_err_handler => \&err_handler }) || exit 1;
$st1 = $db1->prepare("insert into bounces (recipientid, documentid, bo
+uncecode, bouncesubject, bounceheaders) values (?,?,?,?,?)") || exit 
+1;
$st1->execute($recid, $docid, $msg, $subj, $orig) || exit 1;

The string, with newlines replaces by tabs, is there when printed, but in the db I only get the first 78 chars for field "bounceheaders":

From MAILER-DAEMON  Mon Jun 14 08:54:23 2010 Return-Path: <> X-Origina
+l-To: a2@e
From MAILER-DAEMON  Mon Jun 14 08:54:24 2010 Return-Path: <> X-Origina
+l-To: a3@e

Any hints on what goes wrong?

[Corion]

Maybe your table doesn't allow longer fields? You haven't shown us the table definition.

Also, maybe the headers usually get wrapped, but you seem to use manual mail header parsing. I guess that 78 is a convenient limit after which headers get wrapped, if it's not within the SMTP standard.

[derby]

As others have pointed out, it may be your schema but your code has couple of red flags in that makes me think it may be in your code:

• connecting to the database inside a loop - more than likely *not* what you want
• creating string via loop and also inserting in loop - that's going to lead to insert foo, insert foobar, insert foobarbaz, insert foobarbazqux, etc
• code formatting leaves a lot to be desired (if it's easier to read, it's easier to debug)
but maybe that's just because it's a snippet posted here for help.

[rgcosma]

Hello, and thanks for the hints.

1. The db field is nvarchar(MAX)

2. Sorry I didn't notice missing the }. The code is more like

my ($orig, $msg, $count, $recid, $docid, $subj);
while (<STDIN>) {
 $orig .= $_;
 $count++; last if ($count>100 || /\w{70,}/);
 if (/(Precedence: bulk|X-Auto-Response-Suppress)/i) {$msg = "out-of-t
+he-office";}
 elsif (/Final-Recipient: rfc822;/i) {$msg = "undeliverable";}
 }
chomp($orig);$orig=~s/\r//g;$orig=~s/\n/\t/g; #print STDERR $orig;
my $dbserver='dbs'; my $user='dbu'; my $passwd='dbp';
my $db1 = DBI->connect("dbi:Sybase:server=$dbserver;database=dbd;", $u
+ser, $passwd, { syb_err_handler => \&err_handler }) || exit 0;
my $now = time();
my ($st1, $d);
$st1 = $db1->prepare("insert into bounces (recipientid, documentid, bo
+uncecode, bouncesubject, bounceheaders) values (?,?,?,?,?)") || exit 
+0;
$st1->execute($recid, $docid, $msg, $subj, $orig) || exit 0;

[choroba]

What is the type of the column bounceheaders in the database? Isn't it limited to 78 chars?

[Anonymous Monk]

Hi,

Is it always 78?
Many years ago I worked with Sybase via DBI
and I seem to remember that the length of the
string that could be passed into Sybase was
limited, 128 chars( ? ). I had to break up the line with
\n, then it was fine.

May have some bearing.


J.C.

[rgcosma]

Hello,

the length is always 78, and

I find no reference to such an issue - it would be quite strange, the nvarchar(MAX) is 2^31-1 according to Microsoft's docs.

[derby]

Hmmm ... well that /\w{70,}/ may be the culprit but maybe not. You should either set a trace on the DBI handle (see the perldoc) or just log the actual sql and values -- I would want to know *if* I'm generating the correct sql *before* I start blaming the underlying library (freetds) or the db.

[Anonymous Monk]

Hi - done, see here: http://lists.ibiblio.org/pipermail/freetds/2010q2/026052.html

[mpeppler]

DBD::Sybase doesn't have any such limitation - but the underlying API (freetds, Open Client, etc) could very well have a limit. By default Open Client will limit you to 255 chars unless you are using a recent version (12.5 or later), and for freetds you probably need to be using TDS version 7 or 8 to get more than 255 chars for a char string with a placeholder.

Of course - the way to test this is to write a simple insert with a string of a known length and see what you get on the other end (i.e. in the database table)

Michael