Re: ntext 8000 char limit - still there?

2014-08-14 16:45 GMT+01:00 Nem W Schlecht <[email protected]>:

> Hello all,
>    I'm using FreeTDS with Perl and I'm trying to insert large chunks of
> text into a table with an NTEXT field.  My script runs and produces no
> errors, but I'm only getting 8000 chars when I call
> DATALENGTH(ntextfieldname) on the SQL side.  I found a discussion on this
> back from 2004 in the FreeTDS list where it said this was a limitation of
> the protocol.
>
> Is this still a limit?  What is the accepted way of getting around this
> limitation and inserting large-sized values into NTEXT fields?
>
>
The protocol limitation apply to nvarchar, not to ntext. Could be however
that Perl say to FreeTDS to send data using nvarchar, in this case you hit
the limit.

You should use TDSDUMP to discover which type is Perl using.


> My test code:
> #!/usr/bin/perl
>
> #CREATE TABLE testinsert (
> #   querytext NTEXT
> #);
>
> use DBI;
>
> my @srvopts;
> push(@srvopts, "server=MyServer");
> push(@srvopts, "database=MyDatabase");
> my $srv_opts_joined=join(';', @srvopts);
>
> my $dbh = DBI->connect("dbi:Sybase:$srv_opts_joined", 'user', 'secret');
>
> my $sth = $dbh->prepare("
>     INSERT INTO testinsert
>         (querytext)
>     VALUES (?)
> ");
>
> my @words;
> open(WORDS, "/usr/share/dict/words");
> while (<WORDS>) {
>     push(@words, $_);
> }
>
> my $big = join('', @words);
> print "L: ", length($big), "\n";
> $sth->execute($big);
>
> $sth->finish();
> $dbh->disconnect();
>
> #
> # SELECT DATALENGTH(querytext) FROM testinsert;
> #
>
>
> --
> Nem W Schlecht
>

Frediano