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

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?

Replies are listed 'Best First'.
Re: dbd::sybase string cut at 78 characters?
by Corion (Pope) on Jun 14, 2010 at 20:32 UTC

    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.

Re: dbd::sybase string cut at 78 characters?
by derby (Abbot) on Jun 14, 2010 at 22:22 UTC

    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.

      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;
Re: dbd::sybase string cut at 78 characters?
by choroba (Archbishop) on Jun 14, 2010 at 20:35 UTC
    What is the type of the column bounceheaders in the database? Isn't it limited to 78 chars?
Re: dbd::sybase string cut at 78 characters?
by Anonymous Monk on Jun 14, 2010 at 23:41 UTC

    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.


      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.

      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.

        Hi - done, see here:
Re: dbd::sybase string cut at 78 characters?
by mpeppler (Vicar) on Jun 24, 2010 at 20:13 UTC
    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)