ActiveRecord with SQLServer - without Rails 15
At my day job today, I figured out how to write a Ruby script to query our SQL Server 2000 database. (Don’t tell anyone here :-)
I had heard that the sqlserver driver in Rails didn’t work well, but I was able to get it working with a little trial and error.
Now keep in mind, this example reflects my particular working in my environment. We tend to just do the “out of box” configuration with SQL Server, so I’m thinking that what I did will probably work for you, too; or at least give you a good head start.
First, the bad news. SQL Server connectivity does not ship with Rails 1.1. You need to download the sql server adapter yourself.
Even worse, I don’t know where you can just get the adapter by itself (it’s just one .rb file). Here’s what I did:
1. Go here and scroll down to get ruby-dbi version 0.1.0. It’s a .gz file with a tar inside; in other words, it’s a compressed package that’s been compressed. (ugh). Open up the .gz, and then open up the .tar inside, and look for a file called bdi-0.1.0/lib/dbd/ADO.rb. But don’t extract it just yet!
2. First create this folder on your machine: c:/ruby/lib/ruby/site_ruby/1.8/DBD/ADO. You should already have all of these folders except for the ADO folder. Create the ADO subfolder.
3. Now extract that ADO.rb file into your new ADO folder.
Now, ActiveRecord will be able to locate ADO.rb, which is the sql server adapter implementation. Note that we won’t be able to use pure ODBC here; I haven’t figured out how to do that yet. I know an additional download is probably required, and since I don’t need it, ADO mode is good enough for me.
Next I started up Notepad and wrote a script to create classes for each table I want to access. Here I’ll show how I connected to our legacy Item table, which uses a string column named “sku” as the primary key (yuck).
require 'rubygems'
require_gem 'activerecord'
ActiveRecord::Base.establish_connection(
:adapter => "sqlserver",
:dsn => "instance_name_goes_here",
:host => "machine_name\\instance_name",
:database => "db_name",
:username => "my_username",
:password => "my_password"
)
class MsSqlTable < ActiveRecord::Base
def method_missing(method, *args)
respond_to?(method) ? super : send(method.to_s.camelize, *args)
end
end
class Item < MsSqlTable
set_primary_key "sku"
set_table_name "item"
end
I’ll walk through it real quick:
1. The :dsn needs to be your SQL Server instance name
2. The :host needs to a path like “myserver\myinstance”, even though you’ve already specified the instance name in the :dsn parameter
3. The rest of the settings should be obvious. If your password is blank, don’t specify :password at all.
4. Our legacy column names are things like OldDescription. So I created an intermediate base class that would provide ruby-like aliases. Now I can use item.old_description instead if I want to.
5. Finally, I declare one class for each table I want to access. I have to set the primary key and table name manually, since our tables don’t correspond to any Rails conventions.
I saved this script as legacy.rb, and then fired up irb from that directory:
irb> load 'legacy.rb'
=> true
irb> Item.find("191191").old_description
=> "Table In A Bag"
irb>
Is that awesome, or what.
You can get a list of all the tables with this:
irb> ActiveRecord::base.connection.tables.sort
Getting a list of columns for a table you have not created a class for takes a little work:
ActiveRecord::Base.connection.columns("Item").sort_by { |c| c.name }.each { |c| puts c.name }; nil
My example Item class here was very simple, but you should realize you’ve got the full power of ActiveRecord to define relationships between tables, etc.
Want to see something else about using the SQL Server adapter? Or did you find an easier way to do what I’ve done? Let me know.
That pretty much sums it up... and to USE rails the only difference is to obviously edit your database.yml to include the information inside of the establish_connection...
I used SQL server to do some testing before...
What's even more difficult is using Rails with multiple databases...
Actually, tar files are not compressed. They're just the concatenated contents of each file, plus a header in front of each file with the filename and other metadata. It's standard procedure to compress a tar file, you get better compression than if you (say) compressed each individual file, then tarred up the result.
Jon, thanks for the clarification. I guess I presumed .tar files are compressed when I saw my winzip kick in to open it for me.
I too started using Active Record with SQL2K this week, and like you found it easy once the adaptor was installed. I've actually been using SQL2K from Ruby but without Active Record for some time and not surprisingly, AR programming seems much more pleasurable than programming with naked Ruby-dbi.
One thing worth pointing out to your readers is that SQL2K views are updateable in many cases (such as a single-table view with no WHERE clause), making it straightforward to Railsify many legacy schemas by adding a set of views that follow Rails conventions. I'm using both this technique and set-table-name/set-primary-key, and both approaches seem equally viable.
In cases where the legacy table names are Railslike but the legacy attribute names are not, it should still be possible to construct adaptor views by making the Rails user account their owner, taking advantage of SQL2K's behavior of exposing user-owned object before dbo-owned objects. I haven't personally tried this approach and am not sure if I will, since it sounds confusion-prone (all users would see an Orders object in the database, but it would look different to the Rails user than to any other user). But it's good to know it's there in case I'm ever desperate enough to use it.
A source of frustration with SQL2K, on the other hand, is that so far I've been unsuccessful in retrieving recordsets returned by stored procedures. Depending on how this is attempted, it tends to return an ADO error suggesting that the recordset has been closed before it was ever retrieved. I've spent a bit of time pounding on this and have found it fairly resistant to solution so far, but I still have a few things left to try.
Excellent point Michael. The reality is, in many enterprisey .NET shops, access to databases are controlled by stored procedures. The lack of a solution to deal with this severely limits our ability to sneak Ruby in the back door. If you can find a way around, please share!
There turn out to be a couple different issues involved in using SQL2K stored procedures from Ruby and obtaining result sets. Fortunately both are solveable or at least hackable.
Under the ADO adaptor, EXEC is not expected to return a result set.
Stored procedures that use PRINT seem not to be ADO-friendly.
Let's start with issue #1: the execute method of ADO.rb calls the commit method unless either AutoCommit is off or SQL.query?(@statement) returns true (which it does only for SELECT statements). I haven't traced the code completely, but it appears that the commit call has the side effect of invalidating any result set that might have been associated with the statement.
There are two straightforward workarounds: either turn off AutoCommit, or patch the SQL.query? method so that EXEC calls as well as SELECT statements return true. I've found both to work, but prefer the latter as I would rather not tamper with the default commit behavior that AR presumably expects.
To patch SQL.query?, open sql.rb (in lib/ruby/site_ruby/1.8/dbi on my system) and change the line
sql =~ /^\s*select\b/i
to
sql =~ /^\s*(select|exec)\b/i
This will cause EXECs to be treated the same as SELECTs in leaving the result set open and uncommitted. After this change, a call to a simple stored procedure that produces a result set and returns works just as if it had been a normal select.
(Note, by the way, that I patched SQL.query? to treat EXEC the same as SELECT, but not EXECUTE. This is a kluge on my part. I have in mind that in cases where I want SELECT-style behavior with a result set and no autocommit I'll use EXEC, and where I want INSERT/UPDATE/DELETE-style behavior with autocommit and no result set, I'll use EXECUTE. Your thinking on this topic may vary.)
Issue #2 is just plain frustrating: using PRINT in a stored procedure seems to 'spoil' ADO's ability to return a result set from it, something you can't see in Query Analyzer but which you can easily test not just from Ruby but with VB or ASP. I have some complex stored procedures which routinely use PRINT to log their actions for diagnostic purposes when running from a scripted batch from OSQL.EXE or SQLCMD.EXE, but it appears I'll have to forego this functionality when calling these procedures from Ruby (which I suppose is OK; they're old enough to be pretty thoroughly debugged at this point).
What I did in this case was to add an @printing TINYINT=1 parameter to these stored procedures, and wrap each PRINT in an IF @printing > 0 statement. Since @printing has a default value of 1, the legacy scripts continue to get their accustomed behavior, but when I call the procedures from Ruby I pass @printing=0 to suppress printing, and the procs run happily and return an ADO result set.
It's possible that one or both of these issues might have been circumvented by using the ODBC adaptor rather than the ADO adaptor. I may explore that in the coming days.
P.S. one final note: above I've talked only about using stored procedures that return a result set. What about stored procedures that return values in OUTPUT parameters of their argument lists? I decided not to tackle that one directly, but instead create wrapper procedures that would return the output parameters as if they were a result set. That is, the wrapper procedure calls the original stored procedure and receives those results in a set of variables; it then performs a SELECT @retval1 AS retval1, @retval2 AS retval2 etc. before returning.
Having posted all of that about stored procedure workarounds with ADO, I now see Carl Graff's post in the Ruby/DBI forum on rubyforge, saying that ODBC works much better than ADO. I'd encourage anyone interested in using SQL2K stored procedures with Ruby to check it out. I haven't tried his methods yet, but at least at first glance, they look more appealing than my methods.
Followup to the above: yup, Carl Graff sure had it right. ODBC works very nicely with stored procedures without requiring any of the hacky workarounds I described earlier.
I haven't benchmarked SELECT/INSERT/UPDATE/DELETE on ODBC vs. ADO, but subjectively the two interfaces seemed about on par with each other in my development environment. Certainly ODBC seems to run well enough for me to stick with that path and drop the ADO approach. (I've also successfully used ODBC in production before (just not in Ruby), which makes me feel pretty comfortable going with it.)
I've had little difficulty with Sql2K and Rails (even calling stored procedures - I have a module for it available from the link). The module uses exec to return resultsets and has worked OK for me. I've not tried it with Sql2005 though.
The print statement problem is because ADO returns multiple recordsets - one with your data and one with a collection of print statements. I've not managed to work round it - I also got the same problem with the "1 row(s) affected" message when NOCOUNT is off.
My beef with MSSQL and ActiveRecord is that the SQLServerAdapter fails to properly handle whitespace-containing table names (such as "My Table").
Has anyone else found an elegant workaround to this? For a number of reasons, merely using settablename('"My Table"') or settablename('[My Table]') don't work.
If necessary, I may hack the source, but wanted to see if others had solved this first.
Cool - the steps listed here helped me get Migrations working with SQL Server.
Thanks!
Have you guys thought of using UDFs instead of stored procedures? You can call them like tables so minimal modification should be needed....
I have the exact same problem as Randall Lucas. Does anyone know how to handle whitespace-containing table names? I works fine on mySQL, but as when I shift to MSSQL doesn't.
Thanks for posting this. I just started a new job at a school that uses .NET exclusively, and I wanted to harness the power of AR to do some poking around the databases. Very helpful...
The thing in itself, insomuch as the transcendental unity of apperception relies on the noumena, is what first gives rise to the employment of the intelligible objects in space and time, and the discipline of human reason, indeed, can never furnish a true and demonstrated science, because, like our experience, it is the clue to the discovery of disjunctive principles.