FreeTDS Development Group

[Moritz Bunkus <m.bunkus AT linet-services.de>]

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.

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').

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

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?

Any help would be greatly appreciated.

Regards,
Moritz

-- 
Dipl.-Inform. Moritz Bunkus
Geschäftsführung

LINET Services GbR  |  Gotenweg 15  |  38106 Braunschweig
Tel. 0531-180508-0  |  Fax 0531-180508-29

http://www.linet-services.de

Vertretungsberechtigte Gesellschafter:
Moritz Bunkus, Philip Reetz und Timo Springmann

USt-IdNr. DE 206946144

Attachment: pgppnvBtJgWsE.pgp
Description: PGP signature