SQL/database server capabilities

[Steve Teale]

This is quite a long exchange relating to ODBC and SQL Server, but I'd 
like the opinion of the D community on it. Am I being unreasonable?

On Wed, 2011-11-23 at 18:11 -0500, James K. Lowden wrote:

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
> 


James,

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

> 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
> 

   alias (some table, view, join, or whatever) Thingie
   Get name and value from Thingie
   Get name and (size_t)(a count of items) from Thingie
   Get name and (NULL or the above) from Thingie
   Get a string, name, and value from Thingie
   Get name and the lexically least name from Thingie

All these seem to be reasonably explicit in terms of type to me.

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. If the 
server can't return one of the types it supports via its protocol, it 
should return an error ("not implemented" perhaps).

I've written a few libraries as well, and I've also worked for several 
companies where I'd have got fired if I had not at least done my 
damnedest to achieve what I'm trying to do.

Steve