FreeTDS Development Group

["James K. Lowden" <james.k.lowden AT icloud.com>]

On Tue, 27 Oct 2015 21:01:26 +0100
Frediano Ziglio <freddy77 AT gmail.com> wrote:

> > SQLDescribeCol API returns wrong datatypes for BigInt as
> > SQL_NUMERIC and SQL_TIMESTAMP_STRUCT as SQL_WVARCHAR. Are these
> > issues already identified? Have they been fixed? If so please help
> > me with FreeTDS versions to get!
> 
> You are using the wrong protocol version. See
> http://www.freetds.org/userguide/choosingtdsprotocol.htm

I offer the following as a new section for that page:

[snip]
Backward Compatibility

        Server Behavior

Backward compatibility allows old clients to connect to new servers:
The server supports the latest version (by definition) of the protocol,
and accepts connections for all prior versions (even going back 10
years and more).  

One of the primary reasons to change the protocol is to support new
datatypes on the server.  New datatypes require new binary
representations in the protocol.  

When an old client references a new datatype -- or when a new client
uses an old version of the TDS protocol -- the server must do
*something*.  One option (not taken) would be to return an error to the
effect that the client needs to be upgraded.  Microsoft to its credit
has consistently chosen the harder and better route: backward
compatibility.  The server represents the data in a form the client can
recognize, regardless of its server-side definition.  

However, as is often the case, compatibility entails a loss of
information.  Representing a new type as an old one requires a
compromise, losing some information along the way.  

For example, while TDS has long supported the DATETIME datatype, SQL
Server 2008 (TDS 7.3) introduced the DATE and TIME datatypes.  To
transmit these types to the client, the protocol had to be expanded to
include new identifiers for them and to define how their values would
appear on the wire.  Because DATE can include dates not representable by
DATETIME, for old TDS versions the server converts DATE to VARCHAR.  

Usually, but not always, the server converts unsupported datatypes to
VARCHAR strings.  (All data can be represented as strings.)  But not
always: when TDS version 7.0 was introduced, old clients saw the new
NUMERIC type converted to FLOAT.  In every case the data are returned
to the client using datatypes recognized by the protocol version the
client is using.  

        [Question: what happens when a new client connects to an old
server?  That should be answered here, but I would need to test it.  I
think the answer is, "nothing, it just works". I think the protocol is
always modified by adding information, such that version N includes
only new (not changed) information relative to version N-1.  If
correct, the server can accept connections from clients for protocol
version >= N, because its use of N will be entirely valid as N+1, too.
It just won't happen to support the new datatypes defined in N+1.]

        Programmer Advice

For the programmer, backward compatibility means clients can be
upgraded "as needed", not just because the server was upgraded.  Anyone
who's been through a server upgrade knows just how valuable that it.
But it also means that as the server's new datatypes begin to be used,
old clients will see those new types in converted form, as old types.  

If you want to make full use of the server's datatypes, use its highest
supported TDS version.  If the datatype returned to the client is not
the one you expect given the defintion on the server, check the
protocol version.  

Setting the TDSDUMP environment variable will generate a log with
the protocol version near the top.  The log also contains the TDS
dataype for each column, as supplied by the server.  It does *not*
contain the server's conversion choice, if any, because that
action is taken on the server and does not appear in the protocol.  

[pins]

Humbly submitted, 

--jkl