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

On 2011-11-24 09:18, 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

Apperently James K. Lowden has never heard of ActiveRecord and Ruby on Rails. It's possible to get the types of the columns in tables. ActiveRecord can do that with all supported databases and it supports all major databases including SQL Server. SQL Server can be used either through ODBC or directly using FreeTDS.

-- 
/Jacob Carlborg

Steve Teale Wrote:

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

It seems this has no connection to columns whatsoever. Whatever data you receive from server, it's type is encoded in the received data packet. One may want to match types exactly or do sensible conversions like round a float to int or parse int from string. You don't want to have count(*) return an int32 on mssql and int64 on oracle and crap up if you requested, say, int32.

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

Sean,

I accidentally deleted your post, and in Pan I don't know how to get it back.

Yes, most of the API's support that nominal capability, but what they tell you may not be what you expect, especially with ODBC.

Steve

On Thu, 24 Nov 2011 13:09:41 -0500, Kagamin wrote:
> 
> It seems this has no connection to columns whatsoever. Whatever data you receive from server, it's type is encoded in the received data packet. One may want to match types exactly or do sensible conversions like round a float to int or parse int from string. You don't want to have count(*) return an int32 on mssql and int64 on oracle and crap up if you requested, say, int32.

Cases like count() and other function results are problematic. What I am trying to do is define interfaces with a graduated approach to output binding or translation.

The simplest case would be doing this entirely on auto. You'd issue a query, and get back a set of variants whose types were set up on the basis of information about 'column' types received after the execution of the query. In many cases that would be sufficient.

In more complex cases you would add qualifications to the binding to specify e.g. that a specific column should translate the result to the most capable integral type, or that the received value should be dealt with in chunks, and so on.

Steve

Hi Steve
First of all : I am sorry about my harsh words within my last reply.
--- I am afraid that this feedback is also not very gentle.

Picking up ODBC in order to figure out how an generic database Interface may look like is a very bad idea.

Creating an ODBC Interface at all is pretty useless. NOBODY is using ODBC at all.

Creating std.database based on sockets is useless.
Let's take MySQL for instance.  In case that you create a commercial application based on MySQL you have to pay fees to ORACLE ( approx. 1000 Euro, per Server) and nobody cares about your BOOST licensed Phobos raw socket stuff..

Despite that : std.database becomes unmaintainable. I've had a look at your sources, Tough stuff.  Same is valid Piotr's PostgreSQL implementation.

NO!.
I am all against it. I think that implementing std.database requires understanding of Martin Fowler's  Enterprise patterns, As said before : Function follows Form  :)

Last, and most probably useless comment, Have a look at http://www.sqlalchemy.org/

Cheers,
Bjoern

bls Wrote:

> Hi Steve
> First of all : I am sorry about my harsh words within my last reply.
> --- I am afraid that this feedback is also not very gentle.
> 
> Picking up ODBC in order to figure out how an generic database Interface may look like is a very bad idea.
> 
> Creating an ODBC Interface at all is pretty useless. NOBODY is using ODBC at all.
> 
> Creating std.database based on sockets is useless.
> Let's take MySQL for instance.  In case that you create a commercial
> application based on MySQL you have to pay fees to ORACLE ( approx. 1000
> Euro, per Server) and nobody cares about your BOOST licensed Phobos raw
> socket stuff..
> 
> Despite that : std.database becomes unmaintainable. I've had a look at your sources, Tough stuff.  Same is valid Piotr's PostgreSQL implementation.
> 
> NO!.
> I am all against it. I think that implementing std.database requires
> understanding of Martin Fowler's  Enterprise patterns, As said before :
> Function follows Form  :)
> 
> Last, and most probably useless comment, Have a look at http://www.sqlalchemy.org/
> 
> Cheers,
> Bjoern
> 

I'm sorry , this NABS may be unable to do it.

Steve, If you don't understand , please only copy£¨translate£© the success of an existing library£¬such as: jdbc, ado.net, activerecord£¨ruby£©, Python SQL Toolkit, etc. otherwise, Please clear-cut  don't do it, the std.database will be turn into odbc is meaningless.

On Sat, 26 Nov 2011 15:31:33 -0800, bls wrote:

> Hi Steve
> First of all : I am sorry about my harsh words within my last reply. ---
> I am afraid that this feedback is also not very gentle.
> 
> Picking up ODBC in order to figure out how an generic database Interface may look like is a very bad idea.
> 
> Creating an ODBC Interface at all is pretty useless. NOBODY is using ODBC at all.
> 
> Creating std.database based on sockets is useless. Let's take MySQL for instance.  In case that you create a commercial application based on MySQL you have to pay fees to ORACLE ( approx. 1000 Euro, per Server) and nobody cares about your BOOST licensed Phobos raw socket stuff..
> 
> Despite that : std.database becomes unmaintainable. I've had a look at your sources, Tough stuff.  Same is valid Piotr's PostgreSQL implementation.
> 
> NO!.
> I am all against it. I think that implementing std.database requires
> understanding of Martin Fowler's  Enterprise patterns, As said before :
> Function follows Form  :)
> 
> Last, and most probably useless comment, Have a look at http://www.sqlalchemy.org/
> 
> Cheers,
> Bjoern

Bjoern,

No need for apologies, the D newsgroup is a hard school.

The intro for SQLAlchemy says:

"Over five years of constant development, profiling, and refactoring has led to a toolkit that is high performing and accurate, well covered in tests, and deployed in thousands of environments."

The situation for D is probably roughly as follows:

"About three months  of experimentation, and struggle with inaccurate documentation, has led to a point where a group of three or four of us can communicate reasonably effectively with four database systems - MySQL (API and Protocol), SQLite, PostgreSQL (API and Protocol), and SQL Server (from Linux and from Windows vis ODBC).

We are learning to walk. To do the things SQLAlchemy describes, I think you have to understand how to do that.

You may detest ODBC, but it is very soon going to be the only way to communicate with SQL Server short of writing another wire protocol effort.  There was the alternative of OLE DB, but MS is dumping that.

In another post under the std.database thread I have already suggested that the post of top-down high level designer is certainly up for grabs. Do you fancy it? Maybe by the time the top level design is completed, Piotr and I and and others will have the means to do the nitty-gritty lower-level stuff. in a reasonably consistent way.

Steve

bls Wrote:

> Creating an ODBC Interface at all is pretty useless. NOBODY is using ODBC at all.

Then why SQLAlchemy supports it?