Forum: Ruby MsSqlClient ActiveRecord Adapter initial gem-release!

First gem-release of the MsSqlClient. MsSqlClient is an ActiveRecord
Database Adapter for Win32 systems connecting to Microsoft SQL Server
(2000, 2005 [Express]).

It's much faster than the DBI adapter, and more importantly, has full
support for Unicode NVARCHAR to UTF8 translation to and from the
database!

Please don't forget to submit any bugs/requests here:
http://rubyforge.org/projects/mssqlclient

Progress, details, and a short installation overview can be found here:
http://substantiality.net/articles/2006/10/19/mssqlclient-gem-now-available

Any comments/criticisms on the code would be very welcome as well.

How bad was the default AR SQLServer adapter? How much better is this 
one? Will it provide any additional interfaces to the DB that the 
default AR API provides?

Nic

On 19/10/06, Sam Smoot <ssmoot@gmail.com> wrote:
> First gem-release of the MsSqlClient. MsSqlClient is an ActiveRecord
> Database Adapter for Win32 systems connecting to Microsoft SQL Server
> (2000, 2005 [Express]).
>
> It's much faster than the DBI adapter, and more importantly, has full
> support for Unicode NVARCHAR to UTF8 translation to and from the
> database!

It's great you're working on improving the connectivity between rails
and MS SQL.  While I don't know of many issues with the ODBC
connectivity, the ADO DBI interface is ripe for improvement.

It looks like most of the work you've put in is in implementating the
ms_sql_client.dll.  While the adapter code you're using is slightly
modified, it's not significantly changed from that in the main SQL
Server adapter (which uses DBI to connect, either through ADO or
ODBC).  Is there any reason you couldn't adapt this to provide a new
DBI driver which would work seamlessly with the current
sqlserver_adapter code?  If there are reasons, are there any changes
that could be made to the sqlserver_adapter so it could easily work
with both DBI and your interface?

Tom

Dr Nic wrote:
> How bad was the default AR SQLServer adapter? How much better is this
> one? Will it provide any additional interfaces to the DB that the
> default AR API provides?
>
> Nic
>
> --
> Posted via http://www.ruby-forum.com/.

I want to make it clear I have nothing but respect for the
maintainer(s) of the sqlserver adapter, WIN32OLE, RubyDBI, etc and
their abilities. My friend Scott (who wrote most of the initial version
of mssqlclient until feature parity) and I just saw an opportunity to
contribute.

Performance of the sqlserver adapter is fine (for Rails) to me. I just
figured people love performance improvements. If  you're doing some
sort of processing with AR this might raise the bar for AR a bit higher
though allowing it to operate in situations you might otherwise have
required batch inserts for.

The issue with the original adapter for my use, is that you can't
really use it effectively for internationalized applications since
WIN32OLE (what the DBI/ADO driver uses) doesn't support multi-byte
encodings.

Another issue was that all the Rails adapters use a single connection
that stays open for the lifetime of the application. Which is just a
really bad choice IMO and results in "zombie state" errors eventually.
I wrote a fix for that that basically just drops the connection and
reconnects. It's a convention imposed by Rails though, and it's a
little hard to work around so I understand why the sqlserver adapter
doesn't try to. Fortunately, we've managed to get around it and provide
an adapter that only has an open connection if executing a query or
currently in a transaction (at least that's the intent).

I also have an acts_as_partitioned plugin for managing partitioned
tables transparently, and a bulk_copy mixin that's kind of related,
allowing you to do things like this:

  # batches are .NET DataTables, that aren't sent to the
  # database until "commit!" is called.
  Customer::batch do |batch|
    batch.save Customer.new(:name => 'bob', :age => rand())
  end

  # batch.commit! is called on block exit automatically
  # unless batch.abort! or batch.rollback! was called.

Eventually I'd like to look into integrating that as well (maybe in
it's own project?).

So, I think with a little hard work we can hack together a solution
that makes MS SQL Server not only an acceptable deployment option for
Rails/Ruby, but one with a number of unique performance and programming
advantages. At least that's my hope.

Now if someone wants to pay me to work on this full-time (hint,
hint)... ;-)

I'm re-thinking my email reply to Tom:

Tom Ward wrote:
> It looks like most of the work you've put in is in implementating the
> ms_sql_client.dll.

It's subtle, but getting the sqlserver adapter to be "stateless" was a
bit tricky, and for me at least, definitely a behaviour I want to keep.
I can't stress this one enough. There's no performance advantage to
Rails' "pseudo-connection-pooling", and it causes problems. It goes on
the chopping block IMO. :-)

> Is there any reason you couldn't adapt this to provide a new
> DBI driver which would work seamlessly with the current
> sqlserver_adapter code?  If there are reasons, are there any changes
> that could be made to the sqlserver_adapter so it could easily work
> with both DBI and your interface?
>
> Tom

I've been thinking about it some more, and I think maybe a "DBI-Plus"
interface... I'm not sure. Bulk Inserts are definitely something I
would want to integrate eventually.

I liked the idea of bringing more of the adapter into C++ eventually,
but the truth is it probably doesn't matter all that much, I probably
should focus on the cleaner seperation you suggest instead.

I would definitely value any input/opinions anyone has on the matter
though.

Thanks, -Sam

PS: Dr Nic:
  If there's any extra database introspection or anything you would
find helpful in your work don't hesitate to suggest it!