Playing with Ruby on Rails on Windows

Having built a simple ASP.NET MVC app to do something utterly trivial, I decided to try my hand at building it in Ruby on Rails. Why? Because I can, and because I wanted to see how that worked. As an implementation constraint, it had to run on Windows and it had to read from an SQL Server database, and this turned out to be the tricky bit.

In reading this it'll be helpful to know my background. Prior to this I had no RoR experience. I did know some Ruby because I picked it up during a vacation, but I haven't written any serious applications in it. Other than that, I can go down to assembly to solve problems if that's what it takes. That wasn't necessary, fortunately, but I did have to dig quite deep to make things work. A lot of it might have been avoided by more careful Googling, but that's always after the fact.

First things first. Obviously we pick up the Windows installer for Ruby from rubyinstaller.org. This doesn't contain Rails, but it doesn't need to — Rails can be installed with the gem package manager. Like so:

gem install rails

Pssh, trivial. Now you have a fully functional Ruby on Rails installation — except that it has no way to connect to SQL Server. Let's ignore that for now and set up our Rails site first. Multiple sites helpfully document how to do this.

For these purposes, I'll be connecting to a simple demonstration database called "Words" that contains only one table, also called "Words", which has three columns: "Word", "SpeechPart" and "Frequency". As you might guess, this is a table containing English word frequencies. This was not, in fact, the database for my actual problem, but it showed some more issues useful for illustration. This database already exists, so it's not to be generated by Rails. Let's set up a Rails site for such a table:

rails words

ruby words\script\generate scaffold word Word:string SpeechPart:string Frequency:int

Now we need to set up this site to connect to our SQL database. Googling around, I gathered additional packages were needed to connect to SQL Server over ODBC. Incidentally, ODBC is the slowest way to connect to SQL Server, but there seems to be no native support as of yet. I saw pages discussing abandoned attempts from earlier Rails versions, but I didn't try installing those. For my purposes, ODBC was good enough. So we continue:

gem install activerecord-sqlserver-adapter

Now we edit the site configuration (config\database.yml) to use my database:

development:

  adapter: sqlserver

  mode: odbc

  dsn: driver={SQL Native Client};Server=localhost;Database=words;Trusted_Connection=yes

Some of you may already see the problem — but don't spoil it for the rest. Let's just run our Rails application:

ruby words\script\server

After we get the welcome page, we surf to /words and... we get a nice error page. The log file clears things up:

no such file to load — odbc

      D:/Temp/ruby/lib/ruby/gems/1.9.1/gems/activesupport-2.3.8/lib/active_support/dependencies.rb:156:in `require'

The SQL Server adapter needs ODBC, but that isn't installed automatically for some reason. No matter, we can guess what's next:

gem install odbc

Good guess, but no — the gem is actually called ruby-odbc. Because… I don't know — as far as I know gems come in only one language. Maybe it's to emphasize that it's a core gem. Never mind:

gem install ruby-odbc

Building native extensions.  This could take a while...

ERROR:  Error installing ruby-odbc:

          ERROR: Failed to build gem native extension.

D:/Temp/ruby/bin/ruby.exe extconf.rb

checking for version.h... no

checking for sql.h... yes

checking for sqlext.h... yes

checking for SQLTCHAR in windows.h,sqltypes.h... yes

checking for SQLLEN in windows.h,sqltypes.h... yes

checking for SQLULEN in windows.h,sqltypes.h... yes

checking for odbcinst.h... yes

checking for main() in -lodbc32... yes

checking for main() in -lodbccp32... yes

checking for main() in -luser32... yes

creating Makefile

make

'make' is not recognized as an internal or external command,

operable program or batch file.

Gem files will remain installed in d:/Temp/Ruby19/lib/ruby/gems/1.9.1/gems/ruby-odbc-0.99991 for inspection.

Results logged to d:/Temp/Ruby19/lib/ruby/gems/1.9.1/gems/ruby-odbc-0.99991/ext/gem_make.out

Oh dear, what's this? It's trying to compile some C files. At this point I suspected that I was supposed to download precompiled binaries. Of course, I laugh in the face of precompiled binaries. Though my default command prompt has no build environment in the path, I do have MinGW installed. Let's see how things go from a MinGW command prompt, then.

$ gem install ruby-odbc

Building native extensions.  This could take a while...

Successfully installed ruby-odbc-0.99991

Hooray! So we run our Rails site again and... we still get an error page. What's wrong now?

 S1090 (0) [Microsoft][ODBC Driver Manager] Invalid string or buffer length

   D:/Temp/ruby/lib/ruby/gems/1.9.1/gems/activerecord-sqlserver-adapter-2.3.8/lib/active_record/connection_adapters/sqlserver_adapter.rb:803:in `initialize'

At this point, you're probably going to try all sorts of variations on the configuration, but that will get you nowhere. Being programmers, we of course peek at the source to see what it's doing:

class SQLServerAdapter < AbstractAdapter

   ...

  def connect

    config = @connection_options

    @connection = case connection_mode

        when :odbc

          ODBC.connect config[:dsn], config[:username], config[:password]

        when :adonet

          System::Data::SqlClient::SqlConnection.new.tap do |connection|

The "adonet" stuff is interesting, as it suggests another way of connecting, but I didn't dig into it. Instead, I scratched my head wondering what was wrong with my configuration. After trying lots of permutations, I recalled that ODBC has excellent logging that allows you to see precisely how ODBC methods are being called. KB 274551 describes it in detail.

Now, for those of you who have already spotted what I'm doing wrong, it may seem strange that I couldn't see it but did recall the ODBC tracing option, which is a lot more obscure than the problem I was having. That's just how these things go: without direct and frequent interaction with ODBC, what I can recall about it is a crapshoot.

Anyway, this is what the ODBC tracing tells us:

server          370-328   EXIT  SQLConnectW  with return code -1 (SQL_ERROR)

    HDBC                011B1830

    WCHAR *             0x011B1938 [      -3] "driver={SQL Native Client};Server=localhost;Database=words;Trusted_Connection=yes\ 0"

    SWORD                       -3

    WCHAR *             0x74324EC8 [      -3] "******\ 0"

    SWORD                       -3

    WCHAR *             0x74324EC8 [      -3] "******\ 0"

    SWORD                       -3

    DIAG [S1090] [Microsoft][ODBC Driver Manager] Invalid string or buffer length (0)

The first thing that throws up a red flag is the weird "\ 0" at the end. Shouldn't that be "\0" or something? And what's with the "-3"? If that's supposed to be the length...

Looking up the documentation for SQLConnect, there's a description for the error that explains everything:

HY090

Invalid string or buffer length

(DM) The value specified for argument NameLength1, NameLength2, or NameLength3 was less than 0 but not equal to SQL_NTS. (DM) The value specified for argument NameLength1 exceeded the maximum length for a data source name.

Aha! I bet SQL_NTS means "Null Terminated String" and it has the value -3. Google immediately confirms this. And now we know what's wrong through a process of elimination: "The value specified for argument NameLength1 exceeded the maximum length for a data source name." At this point, you slap your forehead and recall that the parameter isn't called "DSN" for nothing: you are supposed to specify a data source name there, not a connection string. What I'm passing for a DSN is not a DSN. So the solution is obvious: rewrite the Ruby code so it handles connection strings.

Aaaaactually, it may have occurred to some of you that the obvious solution is to create a DSN through the ODBC control panel and specify it in the configuration. This is true, and I have no doubt that it would have worked, but honestly, DSNs are lame. Seriously, do you use a DSN to connect to SQL Server? Accustomed as I am to .NET, I expect to be able to use connection strings. I don't want to futz around with DSNs, especially not for a simple test application like this. And I'm willing to go to great lengths to make that possible. All in the interest of simplicity and saving time, you understand.

So if SQLConnect can't handle connection strings, what can? The documentation helpfully points out that we're looking for SQLDriverConnect. Where's SQLConnect called from Ruby? It's in odbc.c, the native part of the ODBC gem:

static VALUE

dbc_connect(int argc, VALUE *argv, VALUE self)

{

     ...

     if (!succeeded(SQL_NULL_HENV, dbc, SQL_NULL_HSTMT,

             SQLConnect(dbc, (SQLTCHAR *) sdsn, SQL_NTS,

                       (SQLTCHAR *) suser,

                       (SQLSMALLINT) (suser ? SQL_NTS : 0),

                       (SQLTCHAR *) spasswd,

                       (SQLSMALLINT) (spasswd ? SQL_NTS : 0)),

             &msg,

             "SQLConnect('%s')", sdsn)) {

OK, but this has no way to call SQLDriverConnect. Is that anywhere in the file, by chance? Yes it is:

static VALUE

dbc_drvconnect(VALUE self, VALUE drv)

{

     ...

     if (!succeeded(e->henv, dbc, SQL_NULL_HSTMT,

             SQLDriverConnect(dbc, NULL, (SQLTCHAR *) sdrv, SQL_NTS,

                          NULL, 0, NULL, SQL_DRIVER_NOPROMPT),

             &msg, "SQLDriverConnect")) {

Great, now we just need to find the place where dbc_connect is called and make it call dbc_drvconnect somehow. The only other reference to dbc_connect (and dbc_drvconnect) is still in odbc.c:

/* connection (database) methods */

rb_define_method(Cdbc, "initialize", dbc_connect, -1);

rb_define_method(Cdbc, "connect", dbc_connect, -1);

rb_define_method(Cdbc, "connected?", dbc_connected, 0);

rb_define_method(Cdbc, "drvconnect", dbc_drvconnect, 1);

I'm no Ruby interop expert but that's clear enough: there's a "connect" method that maps to dbc_connect and a "drvconnect" method that maps to dbc_drvconnect. Recall that we saw a "connect" call in the SQL adapter:

  def connect

     config = @connection_options

     @connection = case connection_mode

            when :odbc

                ODBC.connect config[:dsn], config[:username], config[:password]

There's no "drvconnect" call around, though. Does any other code call "drvconnect"? On a fresh installation, no. If you have happened to have installed "rails-odbc", like I did in the course of experimenting, then yes:

if config.has_key?(:dsn)

# Connect using dsn, username, password

  conn = ODBC::connect(dsn, username, password)     

  conn_opts = {

       :dsn => dsn, :username => username, :password => password,

       :trace => trace, :conv_num_lits => conv_num_lits,

       :emulate_booleans => emulate_bools

  }

else 

# Connect using ODBC connection string

  # - supports DSN-based or DSN-less connections

  # e.g. "DSN=virt5;UID=rails;PWD=rails"

  #      "DRIVER={OpenLink Virtuoso};HOST=carlmbp;UID=rails;PWD=rails"

  connstr_keyval_pairs = connstr.split(';')

  driver = ODBC::Driver.new

  driver.name = 'odbc'

  driver.attrs = {}

  connstr_keyval_pairs.each do |pair|

     attr = pair.split('=')

     driver.attrs[attr[0]] = attr[1] if attr.length.eql?(2)

  end

  conn = ODBC::Database.new.drvconnect(driver)

  conn_opts = {

       :conn_str => config[:conn_str], :driver => driver,

       :trace => trace, :conv_num_lits => conv_num_lits,

       :emulate_booleans => emulate_bools

  }

end

Well well, isn't that interesting. Note the "connect" call and the code demonstrating how to call drvconnect. In fact, we could simply use this adapter instead of the SQL Server adapter. To be honest, I have no idea what the pros and cons are. If you do go with the Rails ODBC adapter, be sure to get the latest version from RubyForge, not the 1.5 version you'll get if you use the default gem install.

I didn't go with this approach (too simple and obvious, surely), instead, I hacked the SQL Server adapter to include the same support the ODBC adapter has. To that end, we change the check on the configuration:

module ActiveRecord

 

  class Base

 

    def self.sqlserver_connection(config) #:nodoc:

      ...

      case mode

      when :odbc

        require_library_or_gem 'odbc' unless defined?(ODBC)

        require 'active_record/connection_adapters/sqlserver_adapter/core_ext/odbc'

        raise ArgumentError, 'Missing :dsn configuration.' unless config.has_key?(:dsn)

To

        raise ArgumentError, 'Neither :dsn nor :conn_str configuration supplied.' if !config.has_key?(:dsn) and !config.has_key?(:conn_str)

And at the site where we would usually call "connect":

class SQLServerAdapter < AbstractAdapter

  ...

  def connect

    config = @connection_options

    @connection = case connection_mode

                  when :odbc

                    ODBC.connect config[:dsn], config[:username], config[:password]

We plug in the code from the ODBC adapter to support conn_str in addition to dsn:

when :odbc

if config[:dsn]

  ODBC.connect config[:dsn], config[:username], config[:password]

else 

  connstr = config[:conn_str]

  connstr_keyval_pairs = connstr.split(';')

  driver = ODBC::Driver.new

  driver.name = 'odbc'

  driver.attrs = {}

  connstr_keyval_pairs.each do |pair|

     attr = pair.split('=')

     driver.attrs[attr[0]] = attr[1] if attr.length.eql?(2)

  end

  ODBC::Database.new.drvconnect(driver)

End

And we change our configuration to read:

development:

  adapter: sqlserver

  mode: odbc

  conn_str: driver={SQL Native Client};Server=localhost;Database=words;Trusted_Connection=yes

And now our site gives a completely different error, so you know we're on the right track:

ActionView::TemplateError (word_url failed to generate from {:controller=>"words", :action=>"show", :id=>#}, expected: {:controller=>"words", :action=>"show"}, diff: {:id=>#}) on line #15 of app/views/words/index.html.erb:

12:     <%=h word.Word %>

13:     <%=h word.SpeechPart %>

14:     <%=h word.Frequency %>

15:     <%= link_to 'Show', word %>

16:     <%= link_to 'Edit', edit_word_path(word) %>

17:     <%= link_to 'Destroy', word, :confirm => 'Are you sure?', :method => :delete %>

18:  

    (eval):17:in `word_path'

And I'll reveal what this means in my next post, when we get down to actually building our site — and when I've hopefully figured out how to do proper formatting and syntax highlighting for these things without relying on Microsoft Word and its awful HTML as a crutch. Seriously, if anyone has tips here that make this process even a fraction less painful than it currently is, please share. It's stone knives and bearskins over here.