[freetds] ntext 8000 char limit - still there?

[Nem W Schlecht]

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?


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;
#

[Randy Syring]

Have you seen this:

http://www.freetds.org/userguide/troubleshooting.htm#KNOWNISSUES

There is some info there about lengths for both varchar and text.

*Randy Syring*
Husband | Father | Redeemed Sinner

/"For what does it profit a man to gain the whole world
and forfeit his soul?" (Mark 8:36 ESV)/

Post by Nem W Schlecht
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?
#!/usr/bin/perl
#CREATE TABLE testinsert (
# querytext NTEXT
#);
use DBI;
my $dbh = DBI->connect("dbi:Sybase:$srv_opts_joined", 'user', 'secret');
my $sth = $dbh->prepare("
INSERT INTO testinsert
(querytext)
VALUES (?)
");
open(WORDS, "/usr/share/dict/words");
while (<WORDS>) {
}
print "L: ", length($big), "\n";
$sth->execute($big);
$sth->finish();
$dbh->disconnect();
#
# SELECT DATALENGTH(querytext) FROM testinsert;
#

[Nem W Schlecht]

Thanks for the replies, Randy and Frediano!

Yes, I did read that document and attempted setting TEXTSIZE to various
values in my freetds.conf file to no avail - I keep getting just 8000 chars
(of course, wide chars since its NTEXT).

Frediano, I'll take a look at the DBD::Sybase code and see if that's the
case (and try TDSDUMP as well).

Thanks for the pointers.

Post by Randy Syring
http://www.freetds.org/userguide/troubleshooting.htm#KNOWNISSUES
There is some info there about lengths for both varchar and text.
*Randy Syring*
Husband | Father | Redeemed Sinner
*"For what does it profit a man to gain the whole world and forfeit his
soul?" (Mark 8:36 ESV)*
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?
#!/usr/bin/perl
#CREATE TABLE testinsert (
# querytext NTEXT
#);
use DBI;
my $dbh = DBI->connect("dbi:Sybase:$srv_opts_joined", 'user', 'secret');
my $sth = $dbh->prepare("
INSERT INTO testinsert
(querytext)
VALUES (?)
");
open(WORDS, "/usr/share/dict/words");
while (<WORDS>) {
}
print "L: ", length($big), "\n";
$sth->execute($big);
$sth->finish();
$dbh->disconnect();
#
# SELECT DATALENGTH(querytext) FROM testinsert;
#

[Frediano Ziglio]

Post by Nem W Schlecht
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.

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

Frediano