Re: Determining column type

James,

You missed your vocation. You should have been a poet, a priest, or a
politician.

Anyway I have already grovelled, and with Frediano's help I am where I
want to be.

Thanks
Steve

On Tue, 2011-11-29 at 01:11 -0500, 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