Re: Determining column type

On Tue, 29 Nov 2011 12:53:49 -0600
Konrad J Hambrick <[email protected]> wrote:

> However, what concerned me most about
> your findings was that the ODBC Function SQLColAttribute( ... )
> 'lied' or it 'made something up' rather than saying 'I dunno' or
> 'NULL' or undef or 'mind your own business'.
> 
> It sounds like Freddie fixed an honest-to-goodness bug in the CSV
> Head so that SQLColAttribute() now returns proper SQL Types.

Fear not: I'll be briefer this time.  Somewhat.  :-)

There was no bug.  No. Bug.  

A column was defined in SQL to be type BIGINT.  The client used a
version of the TDS protocol that pre-dated the existence of BIGINT.
The data were delivered to the client as double-precision floating
point, a fact that SQLColAttribute() reported accurately.  

That's a feature, not a bug.  It even has a name: backward
compatibility.  

Imagine the needless pain of upgrading every last client at the same
*instant* that your new server comes online. [1]   Needless, because you
just upgraded and have the same tables you did yesterday.  The server's
new datatypes haven't been used yet and won't be seen by the clients.
Why upgrade them when they're happy as they are?  

To make that more concrete, pretend SQL Server 2010 defines a  new
column type PDF.  It's for holding PDF files, and there are functions
to get the PDF metadata e.g. title, author, and page count.  You
upgrade, but you don't care about the PDF datatype.  If someone does
use it, your Neanderthal 2008 clients will see the column as IMAGE.
Which is *fine*, because they're not expecting PDF and wouldn't know
what to do with it if they got one.  

Steve wanted to know how to find out how the column was defined on the
server. He phrased his question in terms of tables, and I took pains to
point out that lots of data doesn't originate in tables.  An obvious
example for BIGINT is the COUNT_BIG() function.  You can't discover the
server's datatype for your resultset by rooting around in the catalog.  

Last night's exegesis was more academic:  the idea that BIGINT is a
size_t is flat-out wrong.  BIGINT is an SQL construct, an integer
between -2^63 and 2^63-1.  It isn't a C type.  It doesn't have an bit
pattern in memory.  It's an SQL datatype.  

Remember Magritte's "This is not a Pipe"?  

I just told you the range of values BIGINT can hold, and I did it
without using any numbers!  You're reading email, and this message is
100% text.  Even the numbers are text!  (And the text is numbers, but
that's another discussion.)  I put the *letters* 2, ^, 6, and 3 in the
message, and you interpreted them as a single value.  Those letters are
not a number.  They just conjure one in your mind.  A really big one, I
hope.  

I took information from my mind and put in yours by causing certain
data to take a shape that (I trusted) you would receive and interpret.
The data aren't the information; they're an information conduit.  

As a reader of this mailing list, you naturally are concerned with what
shape BIGINT takes in your programming language of choice, be it C or
PHP or something else.  The job of representing the SQL-defined
data in client client memory is called "binding", and it's exactly
the one FreeTDS undertakes.  The choice of binding -- the mapping of
client to server types -- is an engineering decision.  The mapping
is more or less precise, depending on the target host language and the
capabilities of the client library as expressed through the TDS
version.  

Steve noted correctly that using a C double to represent a T-SQL BIGINT
was less than optimal because a C double can't represent every integer
between -2^63 and 2^63-1.  He was looking for a server-side solution,
but he needed a client-side one.  Which, mirabile dictu, Frediano
provided.  

So: no bug.  Just a slight misunderstanding.  I hope I've cleared that
up now.  

--jkl

[1]  Or don't imagine.  Subversion requires client upgrades to speak to
the server sometimes.  I don't believe the rumor that they learned that
trick from the Microsoft Word team, but who knows?