# MsSqlClient ActiveRecord Adapter initial gem-release! **URL:** \ **Category:** Ruby\ **Created:** [October 20, 2006, 12:51am UTC](https://www.ruby-forum.com/t/mssqlclient-activerecord-adapter-initial-gem-release/72946 "2006-10-20T00:51:58Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![sams](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e79b87/32.png) [@sams](https://www.ruby-forum.com/u/sams)\ **Post date:** [October 20, 2006, 12:51am UTC](https://www.ruby-forum.com/t/mssqlclient-activerecord-adapter-initial-gem-release/72946/1 "2006-10-20T00:51:58Z")
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](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](http://substantiality.net/articles/2006/10/19/mssqlclient-gem-now-available) Any comments/criticisms on the code would be very welcome as well. ---
**Author:** ![sams](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e79b87/32.png) [@sams](https://www.ruby-forum.com/u/sams)\ **Post date:** [October 20, 2006, 3:24pm UTC](https://www.ruby-forum.com/t/mssqlclient-activerecord-adapter-initial-gem-release/72946/2 "2006-10-20T15:24:58Z")
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 ---
**Author:** ![sams](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e79b87/32.png) [@sams](https://www.ruby-forum.com/u/sams)\ **Post date:** [October 20, 2006, 5:14pm UTC](https://www.ruby-forum.com/t/mssqlclient-activerecord-adapter-initial-gem-release/72946/3 "2006-10-20T17:14:06Z")
On 19/10/06, Sam S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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 ---
**Author:** ![sams](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e79b87/32.png) [@sams](https://www.ruby-forum.com/u/sams)\ **Post date:** [October 20, 2006, 9:01pm UTC](https://www.ruby-forum.com/t/mssqlclient-activerecord-adapter-initial-gem-release/72946/4 "2006-10-20T21:01:14Z")
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/](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)… 😉 ---
**Author:** ![sams](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/e79b87/32.png) [@sams](https://www.ruby-forum.com/u/sams)\ **Post date:** [October 20, 2006, 10:27pm UTC](https://www.ruby-forum.com/t/mssqlclient-activerecord-adapter-initial-gem-release/72946/5 "2006-10-20T22:27:25Z")
I’m re-thinking my email reply to Tom: Tom W. 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!