SQL/database server capabilities

[Sean Kelly]

ODBC provides a means to determine the SQL type of a column in a resultset. I'll forward my code to you when I get a chance. 

Sent from my iPhone

On Nov 24, 2011, at 12:18 AM, Steve Teale <steve.teale at britseyeview.com> wrote:

> 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