FreeTDS Development Group

[Brian Bruns <camber AT umcc.ais.org>]

On 6 Apr 2000, Stefan Bodewig wrote:

> Hi,
> 
> I recently switched from SUN's JDBC-ODBC bridge to freetds_jdbc but I
> don't think my problem is isolated to the JDBC driver.
> 
> Microsoft SQL 7.0 allows CHAR, BINARY and their VARYING counterparts
> to be up to 8000 bytes long and the ODBC-JDBC bridge allowed me to use 
> the full length.
> 
> Using freetds_jdbc it doesn't seem to be possible to retrieve more
> than 255 bytes as it assumes the length of those columns to be encoded 
> in exactly one byte.
> 
Hi,

This is a feature of TDS 7.0 *only*  Using TDS 4.2, varchar/chars are
limited to 1 byte size fields. I'm not quite sure if freetds_jdbc supports
7.0 or not. (I'm the C lib guy)

> Looking at include/tds.h from the FreeTDS 0.50 I find
> 
> /* large type means it has a two byte size field */
> #define is_large_type(x) (x>128)
> 
> and further above
> 
> #define SYBCHAR      47   /* 0x2F */
> #define SYBVARCHAR   39   /* 0x27 */
> 
> which clearly means only one byte size information. 
Actually the upper bit gets stripped off, so 39+128 = varchar with size
field of two bytes.

> 
> NOTE: I didn't check whether I could retrieve my longer CHAR columns
> using C, just flipped through the sources searching for a quick
> solution to the JDBC problem.
> 
You can, but only if configured with --with-tdsver=7.0

> My short term solution will probably be: dump out all data from the
> tables with VARCHARs longer than say 250, create new tables with TEXT
> (MS SQL's version of CLOB) columns and reload the data. TEXT and IMAGE
> work well using the JDBC driver.
you can issues selects like "select convert(text,largecolumn1) from atable"
that should also work.  If on the other hand (assuming the jdbc driver has
no 7.0 support) you want to add the support, I can give you a quite run down
of the changes used to patch 7.0 into the C libraries (they are not that
bad).

> 
> I really don't know anything about the TDS protocol and so I don't know
> where to start. But I really want to fix it instead of write
> workarounds.
> 
> I see that one byte is read from the stream to identify the column
> type. For CHAR and BINARY types (among others that don't have a fixed
> length) exactly one byte is read to determine the length.of data to
> retrieve for this column.
> 
> If I look at the returned String, there is no garbage in it - it is
> just truncated - which leads to the assumption that SQL Server is
> really handing back one byte of size information, not more.
> 
> Would any of the TDS gurus - who I hope will read this - please shed
> some light on this?
> 
> Thanks
> 
>         Stefan