FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>]

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