SQLServer on Rails

Rails
21

Got the adapter installed (your command was missing the word
‘install’, though) but now I am getting errors. After running this
command:

Oops, yes it was.

That was an easy fix after I went to
http://kenai.com/projects/jruby/pages/GettingStarted
from the JRuby main page. :wink:

You’ll need to re-install the Rails gem as well.

Well, that comment made me realize that JRuby actually works
‘separate’ from Ruby. Sometimes I wonder if I actually have a
brain. :slight_smile:

Thanks a lot Nick.

22

I’m a little late but here’s what I did with window, ROR and MSSQL
server

cheers,
John

23

pepe wrote:

First of all many thanks to everybody contributing to solve this
problem of mine, especially Nick S., who proposed what ended up
being a pretty easy way to making everything work.

The original task: I needed to be able to use SQL Server 2005 in a RoR
application in a Windows environment.

The problem: Accessing SQL Server from RoR was proving a pretty
challenging task for my little and lazy brain.

[…]

You don’t need JRuby to do this. At my job, we’re connecting to MS SQL
databases with MRI on Mac OS (with FreeTDS).

Also, check out marnen-foreigner (my fork of Foreigner). It’s just like
the original Foreigner, but it handles foreign key constraints on MS
SQL.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

24 
end

That did the trick. It seems you have to specify the primary key or
you won’t be able to save the records.

I found the ‘problem’. It was the case of the field names. All fields
in the table were either upper or mixed case, including ID. I renamed
the fields to all lowercase and removed the primary key declaration
from the model. I then tried again to create a new record and it went
through. I didn’t try making the ID column NOT IDENTITY as I think the
table still needs it, though.

just to check and the tool didn’t let me. ???
I looked everywhere I could around the DB Management tool and could
find nothing that would disregard the case of the column names. I also
looked up all methods names for ActiveRecord::Base and couldn’t find
anything there either. If anybody knows how to set this up so the case
is disregarded I would appreciate the tip.

Thank you

25

First of all many thanks to everybody contributing to solve this
problem of mine, especially Nick S., who proposed what ended up
being a pretty easy way to making everything work.

The original task: I needed to be able to use SQL Server 2005 in a RoR
application in a Windows environment.

The problem: Accessing SQL Server from RoR was proving a pretty
challenging task for my little and lazy brain.

The solution: Use JRuby and activerecord-jdbcmssql-adapter, as Nick
suggested.

Here are the steps:

  1. Install JRuby.
    Go to Downloads — JRuby.org and select the version you
    need (in my case it was “JRuby 1.5.0 Windows Executable
    (md5, sha1)”), then run the executable.
  2. Get familiar with how to run JRuby commands
    Go to http://kenai.com/projects/jruby/pages/GettingStarted and
    check out the several commands on that page. The commands
    are similar to Ruby’s but different enough that it’s a good idea
    to
    check them out. Pay especial attention to ‘jruby -S’
  3. Install activerecord-jdbcmssql-adapter
    At the command line type:
    => jruby -S gem install activerecord-jdbcmssql-adapter
  4. Install rails (this one is only for those who are as thick as I am,
    probably nobody but me)
    What? Again? I already have the thing installed, don’t I?
    No, you don’t. It took me a moment to realize this but you are
    installing rails under JRuby. The other version you have runs
    under regular Ruby (one of Nick’s comments light the bulb in my
    head).
    => jruby -S gem install rails (in my case: jruby -S gem install
    rails -v=2.3.5)
  5. Create your rails application
    => jruby -S rails your_app
  6. Run the jdbc generator (as per Nick’s instructions).
    => jruby script/generate jdbc
  7. Your database.yml should look something like this:
    development:
    adapter: mssql
    database: your_db_name_here
    username: your_user_name_here
    password: your_password_here
  8. Work on your application

One little word of caution in case you run into the same problem…

After I got all the above done I went to the console (jruby script/
console) and instantiated a table record and tried to save it:
c = Contact.new
c.save!

I got this error:
ActiveRecord::StatementInvalid: ActiveRecord::ActiveRecordError:
IDENTITY_INSERT could not be turned OFF for table…

After I played a little bit with the table definition in the DB using
Microsoft SQL Server Mangement Studio Express I realized that the ID
column, although primary key, was not set as IDENTITY column. I
changed that and… nothing! Same error.

Just for the fun of it I explicitly declared the primary key in my
model:

class Contact < ActiveRecord::Base
  self.primary_key = 'ID'
end

That did the trick. It seems you have to specify the primary key or
you won’t be able to save the records.

And one last thing. My table has column names such as FirstName and
LastName. ActiveRecord forced me to use case sensitive symbols during
assignments:
I could use :FirstName => …
I could not use :firstname or :firstName

This last one might have to do with the DB or table definition but I
have not had time to research it to see if making column names case
insensitive is possible under SQL Server. The weird thing is that
using the Management tool I tried to create a column named ‘firstname’
just to check and the tool didn’t let me. ???

Well, I hope this helps somebody else.

Thank you to everybody.