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

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

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

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

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

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

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

>> 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 ;=)

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

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