Re: Determining column type

On Wed, 23 Nov 2011 13:40:13 +0300
Steve Teale <[email protected]> wrote:

> 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().

Steve, 

I've written two C++ database interface libraries.  I don't understand
why you want to know what you say you want to know.  The information
you seem to want doesn't reliably exist.  I assert no database
interface library cares what the "as-defined-in-table" datatypes are.  

One of us doesn't understand something.  I'm looking at you, but maybe
you can explain something to me I've overlooked.  

Let's say we have this simple table:

	create table  nvp
		( name varchar(30) not NULL
		, value int not NULL
		, primary key (name, value)
		)
		
Some queries:

1	select * from nvp
2	select name, avg(value) as v from nvp
3	select name, count(*) as q from nvp
4	select name, nullif(count(*), 0) as q from nvp
5	select 'nvp' as src, name, value from nvp
6	select a.name, min(b.name) as nextname
	from nvp as a left join nvp as b
	on a.name < b.name and a.value < b.value
	
That's just one table.  We haven't gotten to views derived from views,
linked servers, table-valued functions, or unions.  

The client can't know the column with any certainty.  There may be no
column, or the column may be indeterminable from the results.
Indeterminable.  Humpty Dumpty would like that word.  

Don't take my word for it.  Check your local copy of the SQL Standard
for the terms of an "updatable view".  I think you'll find examples 2-6
have properties excluding them from WITH CHECK OPTION.  Not only can
the client not know the column, neither does the server!  

Fundamentally, the datatype of the column is the domain of the data,
and the domain is the province of the server.  

You seem to want to support client-side validation, to check if a date
or time or bigint is in range.  I suggest that's a fool's errand
because you can't, at the client end, know very much about what the
server will accept as valid.  You can't check constraints (unique,
foreign-key, primary key).  Even if you could implement the logic, that
force of nature called the "speed of light" prevents you from knowing
the status of the data when they arrive at the server.  

The client can validate according to the problem domain, not the
server's choice of column datatype.  People can't arrive before they
leave, can't leave before they're born.  Credit cards have 16 digits --
but no spaces or dashes, the horror! -- and dates have to appear on the
calendar in use.  

But they can order a book that just sold out, or try to sell stock at a
non-market price.  They can be disconnected in mid-transaction.  

You didn't ask, but I'm sure, absolutely *positive* you want my advice,
right?   My advice is both to give up and try harder. Yield to the
speed of light and the indeterminism of in-flight transactions.  As the
Irish prayer has it, accept what you cannot change: errors will occur
because the universe insofar as we understand it makes them
inevitable.  The measure of all database libraries is how graciously
they handle errors.  Therefore resolve to do the difficult: handle
errors well.  

Your turn!  ;-)

--jkl