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 <[email protected]> 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
