Skip to Content.
Sympa Menu

freetds - Re: [freetds] problems storing more than 8000 bytes of data in avarchar(max) column

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] problems storing more than 8000 bytes of data in avarchar(max) column
  • Date: Mon, 20 Aug 2007 15:59:45 +0200

>
> Hey guys,
>
> Summary:
>
> I'm trying to get a product (OTRS) running on Linux with a MS SQL 2005
> server. Most of it works, but there are servere problems regarding
> 'text' or 'varchar(max)' columns:
>
> 1. Error messages from the SQL server that the client is trying to use
> more than the allowed 8000 bytes with a varchar column and
> 2. Even if I circumvent the first problem I still only get 8000 bytes
> stored in the column, even if the bind parameter is much longer.
>
> Software setup:
>
> * SuSE Linux Enterprise Server 10 on a standard x86 machine
> * unixODBC 2.2.11 (part of SLES 10)
> * Perl, its DBI and DBD::ODBC modules (part of SLES 10)
> * FreeTDS 0.64 (self-compiled with "--with-tdsver=8.0", also tried
> various other protocol versions with less success)
>
> Here's my simple test script:
>
> ---------------------------------------------------------
> #!/usr/bin/perl
>
> use DBI;
>
>
> my $dbh = DBI->connect('DBI:ODBC:db-otrs-mbu', 'otrs', 'otrs') || die;
>
> $dbh->do('SET TEXTSIZE 2147483647') || die;
> $dbh->do('CREATE TABLE dummy1 (col1 varchar(10), col2 text)') || die;
> $dbh->do('INSERT INTO dummy1 (col1, col2) VALUES (?, ?)',
> undef, 'test', 'helloworld' x 3000) || die;
>
> $dbh->disconnect();
> ---------------------------------------------------------
>
> The ODBC database db-otrs-mbu is configured correctly and
> uses FreeTDS,
> obviously. Connections do work. Now the problems:
>
> 1. With a totally unmodified version of FreeTDS 0.64 I get
> the following
> error message back from the SQL server:
>
> [unixODBC][FreeTDS][SQL Server]The size (30000) given to
> the type 'varchar'
> exceeds the maximum allowed for any data type (8000). (SQL-42000)
>
> Indeed, tcpdump'ing the network traffic reveiled that FreeTDS uses
> 'VARCHAR(30000)' as the parameter type which MS SQL doesn't like.
>

I think we should truncate to maximun limit. I though DBD::ODBC should
use TEXT (LONGVARCHAR) for big strings like this...

> 2. After digging in the source code I've simply changed
> src/tds/query.c,
> function "tds_get_column_declaration", the part from
>
> case SYBVARCHAR:
> case XSYBVARCHAR:
> fmt = "VARCHAR(%d)";
> break;
>
> to
>
> case SYBVARCHAR:
> case XSYBVARCHAR:
> fmt = "VARCHAR(MAX)";
> break;
>
> After compiling FreeTDS again I do NOT get that error message
> anymore. Instead, only 8000 bytes are written to the
> column (verified
> with the MS SQL admin tools from Microsoft, don't know its English
> name... something with 'studio').
>

Good.. I think we truncate data to adhere to mssql limits (currently we
support TDS protocol till 8.0 version that is mssql 2000).

> 3. I've further modified the source of FreeTDS and dumped the "curcol"
> structure. Interesting is that the "curcol->column_type" is 39
> (SYBCHAR), and "curcol->on_server.column_type" is 167 (XSYBCHAR),
> even though the table definition uses the type "text".
>

You are inserting so our ODBC driver is using informations from
DBD::ODBC.

> 4. The very same problems persist if I use "varchar(max)" instead of
> "text" during table creation.
>
> So I think, the biggest question is: why does FreeTDS think that the
> column type on the server is a varchar even though it isn't? The next
> question is: Why are there the very same problems with "varchar(max)"
> columns? Are those simply not supported yet?
>

1) as James reply we can't know server type that easily
2) no, currently not supported

freddy77




Archive powered by MHonArc 2.6.24.

Top of Page