www.digitalmars.com         C & C++   DMDScript  

digitalmars.D - SQL/database server capabilities

reply Steve Teale <steve.teale britseyeview.com> writes:
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
Nov 24 2011
next sibling parent Jacob Carlborg <doob me.com> writes:
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
Nov 24 2011
prev sibling next sibling parent reply Kagamin <spam here.lot> writes:
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.
Nov 24 2011
parent Steve Teale <steve.teale britseyeview.com> writes:
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
Nov 25 2011
prev sibling next sibling parent Sean Kelly <sean invisibleduck.org> writes:
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.=20

Sent from my iPhone

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


 This is quite a long exchange relating to ODBC and SQL Server, but I'd=20
 like the opinion of the D community on it. Am I being unreasonable?
=20
 On Wed, 2011-11-23 at 18:11 -0500, James K. Lowden wrote:
=20
 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. =20=





=20
 One of us doesn't understand something.  I'm looking at you, but maybe
 you can explain something to me I've overlooked. =20
=20
 Let's say we have this simple table:
=20
    create table  nvp
        ( name varchar(30) not NULL
        , value int not NULL
        , primary key (name, value)
        )
       =20
 Some queries:
=20
 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
   =20
 That's just one table.  We haven't gotten to views derived from views,
 linked servers, table-valued functions, or unions. =20
=20
 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. =20
=20
 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! =20
=20
 Fundamentally, the datatype of the column is the domain of the data,
 and the domain is the province of the server. =20
=20
 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. =20
=20
 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. =20
=20
 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. =20
=20
 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. =20
=20
 Your turn!  ;-)
=20
 --jkl
=20


=20
=20
 James,
=20
 I take your well-put point, but basically you are saying that either SQL=20=



 or the server is a heap of crap. There's a high degree of complexity in=20=



 many programming languages and compilers, but a lot of them seem to=20
 manage to hang on to a type.
=20

 create table  nvp
 ( name varchar(30) not NULL
 , value int not NULL
 , primary key (name, value)
 )
       =20
 Some queries:
=20
 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
=20


=20
   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
=20
 All these seem to be reasonably explicit in terms of type to me.
=20
 The cases I am moaning about are when I ask for say an eight byte integer=20=



 from a column that is defined as one, and get back a double-precision=20
 floating point - a format not even capable of holding the value. If the=20=



 server can't return one of the types it supports via its protocol, it=20
 should return an error ("not implemented" perhaps).
=20
 I've written a few libraries as well, and I've also worked for several=20
 companies where I'd have got fired if I had not at least done my=20
 damnedest to achieve what I'm trying to do.
=20
 Steve
Nov 24 2011
prev sibling next sibling parent Steve Teale <steve.teale britseyeview.com> writes:
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
Nov 25 2011
prev sibling next sibling parent reply bls <bizprac orange.fr> writes:
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
Nov 26 2011
next sibling parent dolive <dolive89 sina.com> writes:
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.
Nov 26 2011
prev sibling next sibling parent reply Steve Teale <steve.teale britseyeview.com> writes:
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
Nov 26 2011
next sibling parent reply Jacob Carlborg <doob me.com> writes:
On 2011-11-27 07:13, Steve Teale wrote:

 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.


FreeTDS can be used directly.

-- 
/Jacob Carlborg
Nov 27 2011
parent reply Steve Teale <steve.teale britseyeview.com> writes:
On Sun, 27 Nov 2011 12:31:32 +0100, Jacob Carlborg wrote:


 On 2011-11-27 07:13, Steve Teale wrote:

 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.


 
 FreeTDS can be used directly.


True. I was thinking Windows at the time (very unusual).

Steve
Nov 28 2011
parent reply Jacob Carlborg <doob me.com> writes:
On 2011-11-28 15:34, Steve Teale wrote:

 On Sun, 27 Nov 2011 12:31:32 +0100, Jacob Carlborg wrote:


 On 2011-11-27 07:13, Steve Teale wrote:

 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.


 FreeTDS can be used directly.


 True. I was thinking Windows at the time (very unusual).

 Steve


I'm pretty sure FreeTDS works on Windows. There's a RubyGem, TinyTDS and 
works on Windows and uses FreeTDS.

https://github.com/rails-sqlserver/tiny_tds

-- 
/Jacob Carlborg
Nov 28 2011
parent reply Steve Teale <steve.teale britseyeview.com> writes:
On Mon, 28 Nov 2011 19:48:37 +0100, Jacob Carlborg wrote:


 On 2011-11-28 15:34, Steve Teale wrote:

 On Sun, 27 Nov 2011 12:31:32 +0100, Jacob Carlborg wrote:


 On 2011-11-27 07:13, Steve Teale wrote:

 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.


 FreeTDS can be used directly.


 True. I was thinking Windows at the time (very unusual).

 Steve


 
 I'm pretty sure FreeTDS works on Windows. There's a RubyGem, TinyTDS and
 works on Windows and uses FreeTDS.
 
 https://github.com/rails-sqlserver/tiny_tds


All that said, I think we must still cover ODBC. MS ODBC will be the 
official standard interface to SQL Server, and they are doing Linux 
versions - the 64 bit one is already available.

Steve
Nov 28 2011
parent reply Jacob Carlborg <doob me.com> writes:
On 2011-11-29 05:21, Steve Teale wrote:

 On Mon, 28 Nov 2011 19:48:37 +0100, Jacob Carlborg wrote:


 On 2011-11-28 15:34, Steve Teale wrote:

 On Sun, 27 Nov 2011 12:31:32 +0100, Jacob Carlborg wrote:


 On 2011-11-27 07:13, Steve Teale wrote:

 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.


 FreeTDS can be used directly.


 True. I was thinking Windows at the time (very unusual).

 Steve


 I'm pretty sure FreeTDS works on Windows. There's a RubyGem, TinyTDS and
 works on Windows and uses FreeTDS.

 https://github.com/rails-sqlserver/tiny_tds


 All that said, I think we must still cover ODBC. MS ODBC will be the
 official standard interface to SQL Server, and they are doing Linux
 versions - the 64 bit one is already available.

 Steve


Of course we can still cover ODBC, I just don't think ODBC should be the 
only, or primary, interface to SQL Server.

-- 
/Jacob Carlborg
Nov 28 2011
parent reply Steve Teale <steve.teale britseyeview.com> writes:
 All that said, I think we must still cover ODBC. MS ODBC will be the
 official standard interface to SQL Server, and they are doing Linux
 versions - the 64 bit one is already available.

 Steve


 
 Of course we can still cover ODBC, I just don't think ODBC should be the
 only, or primary, interface to SQL Server.


But in that case we should do a D TDS version to escape from any 
licensing/linking limitations. That won't happen tomorrow ;=)
Nov 29 2011
parent reply Jacob Carlborg <doob me.com> writes:
On 2011-11-29 17:21, Steve Teale wrote:

 All that said, I think we must still cover ODBC. MS ODBC will be the
 official standard interface to SQL Server, and they are doing Linux
 versions - the 64 bit one is already available.

 Steve


 Of course we can still cover ODBC, I just don't think ODBC should be the
 only, or primary, interface to SQL Server.


 But in that case we should do a D TDS version to escape from any
 licensing/linking limitations. That won't happen tomorrow ;=)


As I understand it, FreeTDS provides three client libraries: db-lib, 
ct-lib and odbc. These libraries are available as dynamic libraries and 
then it won't be any licensing issues.

TinyTDS uses db-lib and it HAS to use dynamic library since it's a Ruby 
library. I took a quick look at the source code for TinyTDS, it's quite 
a small library.

http://www.freetds.org/which_api.html

-- 
/Jacob Carlborg
Nov 29 2011
parent reply Steve Teale <steve.teale britseyeview.com> writes:
 As I understand it, FreeTDS provides three client libraries: db-lib,
 ct-lib and odbc. These libraries are available as dynamic libraries and
 then it won't be any licensing issues.
 
 TinyTDS uses db-lib and it HAS to use dynamic library since it's a Ruby
 library. I took a quick look at the source code for TinyTDS, it's quite
 a small library.
 


As far as I can see db-lib is a dead end for SQL Server - http://
msdn.microsoft.com/en-us/library/aa936940%28v=sql.80%29.aspx. ct-lib 
seems to be a Sybase branch.

Steve
Nov 30 2011
parent reply Jacob Carlborg <doob me.com> writes:
On 2011-11-30 18:09, Steve Teale wrote:

 As I understand it, FreeTDS provides three client libraries: db-lib,
 ct-lib and odbc. These libraries are available as dynamic libraries and
 then it won't be any licensing issues.

 TinyTDS uses db-lib and it HAS to use dynamic library since it's a Ruby
 library. I took a quick look at the source code for TinyTDS, it's quite
 a small library.


 As far as I can see db-lib is a dead end for SQL Server - http://
 msdn.microsoft.com/en-us/library/aa936940%28v=sql.80%29.aspx. ct-lib
 seems to be a Sybase branch.

 Steve


I've seen that page as well. I'm wondering if that is about Microsoft's 
implementation. Using Ruby on Rails, TinyTDS is the preferred way of 
connecting to SQL Server these days. We're successfully using it at work 
to connect to SQL Server 2007.

-- 
/Jacob Carlborg
Nov 30 2011
parent Steve Teale <steve.teale britseyeview.com> writes:
 As far as I can see db-lib is a dead end for SQL Server - http://
 msdn.microsoft.com/en-us/library/aa936940%28v=sql.80%29.aspx. ct-lib
 seems to be a Sybase branch.

 Steve


 
 I've seen that page as well. I'm wondering if that is about Microsoft's
 implementation. Using Ruby on Rails, TinyTDS is the preferred way of
 connecting to SQL Server these days. We're successfully using it at work
 to connect to SQL Server 2007.


Well yes that could well be the case. As long as they are still using TDS, 
whatever is on the other side of that is a black box as far as they are 
concerned. I'll take a look at it as soon as I am on top of the ODBC 
implementation.
Nov 30 2011
prev sibling parent bls <bizprac orange.fr> writes:
On 11/26/2011 10:13 PM, Steve Teale wrote:

 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


Point taken! Thanks. :)
Despite that....  hope you will agree with me that following/mimic JDBC 
instead of ODBC makes more sense.

Sure, it's your turn and  ..
asinus sacuum portat.

so I'd better shut up.
Bjoern
Dec 04 2011
prev sibling next sibling parent Kagamin <spam here.lot> writes:
bls Wrote:


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


Then why SQLAlchemy supports it?
Nov 27 2011
prev sibling next sibling parent "Steven Schveighoffer" <schveiguy yahoo.com> writes:
On Sat, 26 Nov 2011 18:31:33 -0500, bls <bizprac orange.fr> wrote:


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


You do not have to pay licensing fees to oracle to use MySQL -- it's GPL.

You do not have to pay licensing fees to oracle to modify MySQL -- it's  
GPL.

You have to pay licensing fees to oracle to modify MySQL *and* keep the  
modifications private when selling the modified server code.

Note that an application that uses MySQL as a data storage but does not  
modify the MySQL source is not required to be GPL.  However, I think if  
you use the MySQL client code, it does need to be GPL.  Writing a new  
client that uses the correct protocol is not required to be GPL.  So the  
licensing of the client code *is* the thing most commercial users will  
care about.

-Steve
Nov 28 2011
prev sibling parent Steve Teale <steve.teale britseyeview.com> writes:
On Sat, 26 Nov 2011 15:31:33 -0800, bls wrote:

 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.


Just a point of clarification. It is not my intention to write a D 
wrapper around ODBC. I'm simply saying that D's DB implementations for
particular DBs might need to use ODBC.

Steve
Nov 28 2011
prev sibling parent reply "Unknown W. Brackets" <usefirstnameinstead-newsgroup unknownbrackets.org> writes:
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
Nov 29 2011
parent reply Steve Teale <steve.teale britseyeview.com> writes:
On Tue, 29 Nov 2011 09:01:29 -0800, Unknown W. Brackets wrote:


 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:
 


It is sorted now. I was using a version of TDS provided by the Ubuntu 
package management system that turned out to be hopelessly out of date. 
I've built it now from FreeTDS CVS, and it works OK.

MySQL 5 returns data from plain old ExecSQL as strings, but for prepared 
statements it uses a binary format. However I have no problems with type 
determination there.

Thanks for taking the trouble to help.

Steve
Nov 29 2011
parent "Unknown W. Brackets" <usefirstnameinstead-newsgroup unknownbrackets.org> writes:
Steve,

Ah, yes, I totally forgot that prepared statements used a better format.

-[Unknown]


On 11/29/2011 9:42 AM, Steve Teale wrote:

 On Tue, 29 Nov 2011 09:01:29 -0800, Unknown W. Brackets wrote:


 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:


 It is sorted now. I was using a version of TDS provided by the Ubuntu
 package management system that turned out to be hopelessly out of date.
 I've built it now from FreeTDS CVS, and it works OK.

 MySQL 5 returns data from plain old ExecSQL as strings, but for prepared
 statements it uses a binary format. However I have no problems with type
 determination there.

 Thanks for taking the trouble to help.

 Steve
Nov 29 2011