SQL/database server capabilities

[Unknown W. Brackets]

Steve,

The type conversion you talk about (bigint -> double) probably happens 
on 32-bit systems, no?  Some of these things will definitely vary 
depending on the database system.

I disagree with him on validation (although he's right about 
constraints, speaking of atomicy), as others, but I think that's not 
what you're after.

You just want to know the types of the result fields, right?  I don't 
know this specifically for ODBC/SQL Server, but it may be worth pointing 
out that MySQL sends everything as strings:

http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#Row_Data_Packet

Although each field has a type:

http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol#Field_Packet

But since they are returned as strings, they have to be converted. 
Honestly, there are cases where I would turn around and use an integer 
as a string (e.g. when formatting to a log), and if it had to be 
converted back and forth, that'd annoy me a bit.  But, for things like 
COUNT, it definitely makes sense.

I'm also not sure how you'd handle things like enums, at least 
automatically.

I think generally, a binding syntax of some sort might be necessary in 
the general case, where you specify what you're expecting (possibly with 
reasonable automatic defaults.)

Anyway, I would casually expect ODBC/SQL Server to provide some basic 
type information in the result as well, but I don't know specifically.

-[Unknown]


On 11/24/2011 12:18 AM, Steve Teale 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