FreeTDS Development Group

[Frediano Ziglio <freddyz77 AT tin.it>]

Il giorno dom, 29/01/2006 alle 12.21 +1100, James Harper ha scritto:
> When I have a look at the systypes table in the sql server 2000 master
> database, I see 4 columns depicting types: 'type', 'usertype', 'xtype',
> and 'xusertype'.
> 
> Taking varchar as an example, it has type = 39, usertype = 2, xtype =
> 167, xusertype = 167. 167 = 39 + 128, and I seem to remember reading in
> some of the source that with bit 7 set it denotes a 'large' type and
> uses a smallint to depict length instead of a byte.
> 
> tds.h defines SYBVARCHAR = 39 and XSYBVARCHAR = 167. Is it just two
> numbers for the same type, or are they actually different types?
> 

There are some differences:
- XSYBVARCHAR use 2 bytes for length
- SYBVARCHAR do not allow empty string ('' -> ' ' on wire)
- XSYBVARCHAR require collation information for tds8

> Why do the two types exist? My first guess would be that the SYBVARCHAR
> is for older protocol version (<7?), and XSYBVARCHAR is for newer (>=
> 7)?
> 
> If that's the case, obviously the old protocol version won't understand
> any of the new types, but does the new protocol accept the old types?
> Some testing shows that it does...
> 

You have always to reminds that tds7 or tds8 is not newer that tds5...
is quite true if you speak only about mssql however server accept old
encoding (and sometimes also return old encoding) for tds7/8

> Then there appears to be the SYBINTN = 38 (nullable integer) which
> doesn't appear at all in the systypes table. What's with that? Is it a
> Sybase but not Microsoft type?
> 

No, is supported by both vendors. In systype there are integer only with
fixed length cause in t-sql you can use only these types however on wire
to represent nullable integers you have to use SYBINTN. Same for all
nullable types (ie bit, float)

> Another question, if I want to place a character string in a result set
> to send to the client, and there isn't an indication from the client as
> to what type to use, how do I determine the type? Do I just use
> XSYBVARCHAR, or should I use SYBVARCHAR if the length is < 255 and
> XSYBVARCHAR if the length is > 255?
> 

You can't use XSYBVARCHAR for tds4/5 but if you use tds7/8 use only
XSYBVARCHAR.

bye
  freddy77