Re: Determining column type

On Thu, 24 Nov 2011 11:12:00 +0300
Steve Teale <[email protected]> wrote:

> I take your well-put point, but basically you are saying that either
> SQL or the server is a heap of crap. 

I am saying no such thing.  You asked 

> Can you think of any way that I can get SQL Server 2008 R2 to report
> the as-defined-in-table column type in the context of a just-executed
> SQLExecute() or SQLExecuteDirect().

and I answered that in the general case results cannot be traced back
to a table.  The information you want does not exist.  Questions of
trying and firing -- and suggestions of ORMs and frameworks -- are
beside the point.  

> There's a high degree of
> complexity in many programming languages and compilers, but a lot of
> them seem to manage to hang on to a type.

There are so many assumptions in that sentence, it's hard to know where
to start or whether to start.  

> The cases I am moaning about are when I ask for say an eight byte
> integer from a column that is defined as one, and get back a
> double-precision floating point - a format not even capable of holding
> the value. 

Yes.  You demand to know what's really there.  No mere representation:
tell me God's honest table-defined truth!   OK!  One problem, though:
the truth is expressed in math, and the question is asked in C.  

An SQL datatype e.g. DATE or BIGINT defines a *domain* -- a logical
set of values.  It does not define a *type* in the sense of a pattern of
bits or a number of bytes.  It does not define a type in any
programming language, including C.  The server is utterly unconcerned
with what your host application programming language is.  

That's *intentional*, going all the way back to Codd's objectives: to
define the data abstractly, without reference to technology, because
technology is extraneous to the meaning of the data.  He didn't mention
Perl and C and VB and D by name -- strange oversight, that -- but
he did intend that the data would outlive the application technology.
He was more right than he knew.  And a good thing, too, unless you like
80-column card images.  

You think you're asking for "an eight byte integer from a column", and
granted the definition of BIGINT aligns very closely with a C99
int64_t.  It might actually be implemented that way; it probably is.
But it's not *defined* that way.  

It is the job of the database interface library to map the abstract SQL
types onto the language's defined types.  Pretend for a minute that
that host language is FORTH or Postscript.   What are you going to do
with DATE or, for that matter, FLOAT?  Those languages don't have a
notion of a three-byte date or IEEE double-precision floating point.
Or pretend you want D to run on an Apple II, the venerable 8-bit 6502.
How are you going to represent BIGINT on that architecture?  

The TDS version problem is analogous.  To the server, the protocol is
the client.  The library declares the set of datatypes it recognizes
though its TDS version.  TDS 7.2 doesn't define DATE.
The server knows that, as does the library.  The server has two
choices, the very ones you alluded to: try or fail.  It could error out
if the referenced datatype can't be perfectly represented, or it could
choose something approximate and let the client cope as best it can.
It chose the latter.  

Far from "moaning" about it, you might consider that choice a kind of
feature.  We could call it "backwards compatibility"; that has a
certain ring, wouldn't you say?   It has permitted many thousands of TDS
clients to connect to later-model servers sporting the lastest
datatypes.  

I hear you saying, "Yes, but I'm hip and I'm now and I'm prepared to
take a shortcut.  I know all the datatypes and I'm willing to accept
their C proxies.  I have 64-bit ints, and I don't care a farthing for
your fine disinctions."   

Let us grant your wish.  Let us wave a wand and pretend the ur-datatype
"as defined in the table" is magically available.  Let time pass.  Now
pretend that SQL Server 2015 defines yet another type, say, ASTRODATE.
And you're on top of that, too, coding away in the dead of night to
keep current. But alas some less-than-dedicated user attempts to use
something other than last night's CVS HEAD snapshot.  The library
demands the ur-type and receives its rightful answer: ASTRODATE!  

The application crashes with a bang that wakes dead vaxes three
counties away.  Smoke seeps out from under the Enter key.  Bits drizzle
out of the computer, leaving fine dust on the user's shoe.  You are
eaten by a grue.  

Just kidding!   There are no grues!  

But there's also no ur-magic, and no ur-truth.  There's no way to map
unspecified abstract types onto a host implementation language.  The
representation -- the datatype as expressed by TDS -- is the only
truth.  More than that cannot be answered.  

--jkl