Re: Determining column type

JK,

You are waxing very poetic these days! I am enjoying reading these 
answers, not least because I keep learning new things.

However, it seems to me that there is another approach that might 
satisfy the OP's needs. Why not query the metadata using the appropriate 
Transact SQL commands? (I'd have to dig around a bit to remember exactly 
how to do this, but IIRC, it is certainly not difficult.)

ahw


On 11/29/2011 01:11 AM, James K. Lowden wrote:
> 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
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds
>