SQLServer on Rails

rubyonrails-talk
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

First of all many thanks to everybody contributing to solve this problem of mine, especially Nick Sieger, 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.

23

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

cheers, John

24

pepe wrote:

First of all many thanks to everybody contributing to solve this problem of mine, especially Nick Sieger, 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,

25

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 &lt; ActiveRecord::Base
  self\.primary\_key = &#39;ID&#39;
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.

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. ???

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