SQLServer on Rails

Does any one know how to setup SQLserver with ruby1.9. It was working
on an earlier version and now nothing works! I have installed the
gems dbi ,dbd-odbc and activerecord-odbc-adapter and can connect in
straight Ruby. However, using rails with database.yml nothing works!
I hosed everything and reinstalled rails and now, I get Win32/Parite
virus on ruby.exe. Any ideas? I'm losing confidence on open
source......

Ha! really?
Poor you, go for a paid solution then.

Leonardo Mateo wrote:

Does any one know how to setup SQLserver with ruby1.9. �It was working
on an earlier version and now nothing works! �I have installed the
gems dbi ,dbd-odbc and activerecord-odbc-adapter and can connect in
straight Ruby. �However, using rails with database.yml nothing works!
I hosed everything and reinstalled rails and now, I get Win32/Parite
virus on ruby.exe. �Any ideas? �I'm losing confidence on open
source......

Ha! really?
Poor you, go for a paid solution then.

It looks you need some teaching about manners.

To Steve: Is there any particular reason why do you need ruby1.9. I also
had lots of problems with 1.9 on Windows and went back to 1.8.6 where
everything works fine.

Don't fix if it aint broken.

by
TheR

I wrote this tutorial for using Ruby on Rails with SQL Server. It is
very easy to follow:
http://www.codecapers.com/post/A-Ruby-on-Rails-Tutorial-for-NET-Developers.aspx

mceranski wrote:

I wrote this tutorial for using Ruby on Rails with SQL Server. It is
very easy to follow:
http://www.codecapers.com/post/A-Ruby-on-Rails-Tutorial-for-NET-Developers.aspx

mceranski,
Thanks for the tutorial. It was informative. Unfortunately I wasn't
able to get it to work. I'm running on a WinXP machine, using SQL
Server 2008 Express. I am using ruby 1.8.7 and rails 2.3.5. My gem
list:

actionmailer (2.3.5)
actionpack (2.3.5)
activerecord (2.3.5)
activerecord-sqlserver-adapter (2.3.5)
activeresource (2.3.5)
activesupport (2.3.5)
cgi_multipart_eof_fix (2.5.0)
fastthread (1.0.1)
gem_plugin (0.2.3)
linecache (0.43)
mongrel (1.1.5)
mysql (2.7.3)
rack (1.0.1)
rails (2.3.5)
rake (0.8.7)
ruby-debug-base (0.10.3)
ruby-debug-ide (0.4.5)
rubygems-update (1.3.6)
sqlite3-ruby (1.2.1)

It seems my message got cut off. What I wanted to include was the error
log from when I accessed http://localhost:3000/Contacts

Here it is:

/!\ FAILSAFE /!\ Mon May 10 09:32:43 -0700 2010
  Status: 500 Internal Server Error
  S1090 (0) [Microsoft][ODBC Driver Manager] Invalid string or buffer
length
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.5/lib/active_record/connection_adapters/sqlserver_adapter.rb:765:in
`initialize'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.5/lib/active_record/connection_adapters/sqlserver_adapter.rb:765:in
`connect'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.5/lib/active_record/connection_adapters/sqlserver_adapter.rb:765:in
`connect'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.5/lib/active_record/connection_adapters/sqlserver_adapter.rb:192:in
`initialize'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.5/lib/active_record/connection_adapters/sqlserver_adapter.rb:28:in
`new'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.5/lib/active_record/connection_adapters/sqlserver_adapter.rb:28:in
`sqlserver_connection'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:in
`send'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:in
`new_connection'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:245:in
`checkout_new_connection'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:188:in
`checkout'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:in
`loop'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:in
`checkout'
    C:/Ruby/lib/ruby/1.8/monitor.rb:242:in `synchronize'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:183:in
`checkout'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:98:in
`connection'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:326:in
`retrieve_connection'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_specification.rb:123:in
`retrieve_connection'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_specification.rb:115:in
`connection'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/query_cache.rb:9:in
`cache'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/query_cache.rb:28:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/connection_pool.rb:361:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/string_coercion.rb:25:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/head.rb:9:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/methodoverride.rb:24:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/params_parser.rb:15:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/session/cookie_store.rb:93:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/failsafe.rb:26:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/lock.rb:11:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/lock.rb:11:in
`synchronize'
    C:/Ruby/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/lock.rb:11:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/dispatcher.rb:114:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/reloader.rb:34:in
`run'
    C:/Ruby/lib/ruby/gems/1.8/gems/actionpack-2.3.5/lib/action_controller/dispatcher.rb:108:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/rails-2.3.5/lib/rails/rack/static.rb:31:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/urlmap.rb:46:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/urlmap.rb:40:in
`each'
    C:/Ruby/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/urlmap.rb:40:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/rails-2.3.5/lib/rails/rack/log_tailer.rb:17:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/content_length.rb:13:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/chunked.rb:15:in
`call'
    C:/Ruby/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/handler/mongrel.rb:64:in
`process'
    C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:159:in
`process_client'
    C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:158:in
`each'
    C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:158:in
`process_client'
    C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:285:in
`run'
    C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:285:in
`initialize'
    C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:285:in
`new'
    C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:285:in
`run'
    C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:268:in
`initialize'
    C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:268:in
`new'
    C:/Ruby/lib/ruby/gems/1.8/gems/mongrel-1.1.5-x86-mswin32-60/lib/mongrel.rb:268:in
`run'
    C:/Ruby/lib/ruby/gems/1.8/gems/rack-1.0.1/lib/rack/handler/mongrel.rb:34:in
`run'
    C:/Ruby/lib/ruby/gems/1.8/gems/rails-2.3.5/lib/commands/server.rb:111
    C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in
`gem_original_require'
    C:/Ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:31:in
`require'
    script/server:3

Any suggestions?

Thanks,
Snowdall

Clark Snowdall wrote:

It seems my message got cut off. What I wanted to include was the error
log from when I accessed http://localhost:3000/Contacts

Here it is:

/!\ FAILSAFE /!\ Mon May 10 09:32:43 -0700 2010
  Status: 500 Internal Server Error
  S1090 (0) [Microsoft][ODBC Driver Manager] Invalid string or buffer
length

Any suggestions?

Thanks,
Snowdall

Hi,

Can you please post your database.yml it seems like your gems are set up
correctly. I would check the odbc and database.yml settings.

this may help http://www.codecapers.com/post/A-Ruby-on-Rails-Tutorial-for-NET-Developers.aspx

Jason

guys,

It's interesting. Did the post below solve the problem?

guys,

It's interesting. Did the post below solve the problem?

Not for me. I am still working on getting the connection working,
although I have not spent too much time on it. I am using SQL Server
2005, though, and I know pretty much nothing about SQL Server so it's
being a little bit of a learning curve to set this up.

So far I have installed DBI and activerecord-sqlserver-adapter
(2.3.5). After I did that I tried to use mode ADO and got this:

NotImplementedError: Please use version 2.3.1 of the adapter for ADO
connections. Future versions may support ADO.NET.

So I installed 2.3.1 instead and got this when starting the console
(but it let me in):

c:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.5/lib/active_record/
connection_adapters/abstract/connection_specification.rb:76:in
`establish_connection':RuntimeError: Please install the sqlserver
adapter: `gem install activerecord-sqlserver-adapter` (no such file to
load -- deprecated)

and this when trying to instantiate a new record for a table:

ActiveRecord::ConnectionNotEstablished:
ActiveRecord::ConnectionNotEstablished from c:/ruby/lib/ruby/gems/1.8/
gems/activerecord-2.3.5/lib/active_record/connection_adapters/abstract/
connection_pool.rb:326:in `retrieve_connection'

Seeing this amount of "success" I switched back to 2.3.5 and changed
the mode to ODBC, but the DSN parameter gave me trouble. Now I think
the DSN is OK (not sure, though):

dsn: Driver={SQL Server};Server=<server_name>

where <server_name> is the server name showing in the Connection
Properties window off the DB Management application (Microsoft SQL
Server Management Studio Express).

I think the DSN is OK because of the error I am getting now, which
could indicate that I have passed the DSN problem but there is
something else going on. Now starting the console gives no errors but
I can't get to the tables (testing from the console with a 'Table.new'
command):

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

I have checked on error S1090 and this is what I found:

SQLExecute
    S1090
        Invalid string or buffer length. One of the following
situations:

            * Parameter pointer is null and length does not equal
SQL_NULL_DATA.
            * Parameter is not null and length is equal to
SQL_NULL_DATA.
            * Parameter is not null, length is negative, length is not
equal to SQL_NULL_DATA, and length is not equal to SQL_NTS.

I seems to me like some type of "configuration" error. If anybody has
an idea of what could be going on I would appreciate any help and if I
get a connection working I'll make sure I post here the steps it took
me to make it work.

Have you tried using JRuby with activerecord-jdbcmssql-adapter? We've
heard multiple stories from folks having better luck using JRuby to
connect to SQLServer than with the buggy ODBC driver.

/Nick

Have you tried using JRuby with activerecord-jdbcmssql-adapter? We've
heard multiple stories from folks having better luck using JRuby to
connect to SQLServer than with the buggy ODBC driver.

The thought has crossed my mind, especially since I already wrote some
time ago a Java app. that connected to SQL Server 2005 with no problem
whatsoever and I am guessing JRuby could be using Java "stuff". I
actually took a look at that code I wrote a while back to check out
the connection string. The thing is that I have never created a JRuby
application and I don't know if I want to go through the learning
curve. I haven't really looked at JRuby at all and this project needs
to get going pretty fast. Do you have any recommendations about JRuby
documentation? Any comments about the learning curve?

Thanks!

Have you tried using JRuby with activerecord-jdbcmssql-adapter? We've
heard multiple stories from folks having better luck using JRuby to
connect to SQLServer than with the buggy ODBC driver.

The thought has crossed my mind, especially since I already wrote some
time ago a Java app. that connected to SQL Server 2005 with no problem
whatsoever and I am guessing JRuby could be using Java "stuff". I
actually took a look at that code I wrote a while back to check out
the connection string. The thing is that I have never created a JRuby
application and I don't know if I want to go through the learning
curve. I haven't really looked at JRuby at all and this project needs
to get going pretty fast. Do you have any recommendations about JRuby
documentation? Any comments about the learning curve?

Thanks!

The experience should be identical to Ruby -- just substitute "jruby"
for "ruby" in your command lines and that's about it, really. Just
head over to jruby.org/downloads, grab a windows installer, and go.

The one difference is to "jruby -S gem activerecord-jdbcmssql-adapter"
to install JRuby's SQLServer support. You'll also want to review one
more step outlined here to run the "jdbc" generator:

http://blog.nicksieger.com/articles/2009/10/12/fresh-0-9-2-activerecord-jdbc-adapter-release

Once you've run that generator, use "mssql" for the adapter name in
database.yml.

Cheers,
/Nick

Hi Nick,

I just went for a little reading to http://jruby.org/ and although
writing code in JRuby might not be different than writing in Ruby it
seems that deploying the application might involve some other steps I
am not familiar with.

My applications run in intranets with very few users and the setup we
use is very simple: Mongrel as web service and deployment is just an
application folder copy away. I'd like to keep things as simple as
possible for now.

If I use JRuby, do I need to use GlashFish or can I still use Mongrel
as my web server? Do I need to deploy using a .war file or can I just
copy the application's folder as I am currently doing?

Thanks a lot.

You can continue to use Mongrel (or the Glassfish gem or Trinidad
(Tomcat) gem) with the same application folder layout you have now.

An app server/WAR file deployment is way overkill unless you need
some other aspect of JEE architecture.

FWIW,

Thanks Hassan (and Nick).

I am going to see if I can get something running quickly in JRuby.

The one difference is to "jruby -S gem activerecord-jdbcmssql-adapter"
to install JRuby's SQLServer support. You'll also want to review one
more step outlined here to run the "jdbc" generator:

Hi Nick,

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

jruby -S rails my_app

I get this message:

c:/jruby-1.5.0/lib/ruby/site_ruby/1.8/rubygems.rb:777:in
`report_activate_error': Could not find RubyGem rails (>= 0)
(Gem::LoadError)

???

I noticed that rubygems are coming from the 'site_ruby' instead of the
'gems' folder. I tried to remove the rubygems from 'site_ruby' to see
if that would work but there was no difference

I am kind of confused...

The one difference is to "jruby -S gem activerecord-jdbcmssql-adapter"
to install JRuby's SQLServer support. You'll also want to review one
more step outlined here to run the "jdbc" generator:

Hi Nick,

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.

jruby -S rails my_app

I get this message:

c:/jruby-1.5.0/lib/ruby/site_ruby/1.8/rubygems.rb:777:in
`report_activate_error': Could not find RubyGem rails (>= 0)
(Gem::LoadError)

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

/Nick

I just recently got a Rails 2.3.5 app up on SQL Server 2005 using ODBC
mode, and the DSN I ended up using was the name specified in the "Data
Sources" control panel as a system DSN. I also had no end of trouble
until I added the login info to the (supposedly optional) "Use this
username and password to connect to the DB for additional
configuration" [paraphrasing] area.

There was also the mess from running 32-bit Ruby on Windows Server
2008 - the regular control panel created a DSN that couldn't be seen
from Ruby. I *finally* found these directions:

http://www.boche.net/blog/index.php/2009/11/21/create-a-32-bit-vcenter-dsn-on-a-64-bit-operating-system/

That pointed me to the 32-bit control panel I needed. Not exactly the
easiest launch in the world...

--Matt Jones

Thanks for the info Matt.

I followed Nick's suggestions and instructions and using JRuby is
ending up being way easier than using ODBC with Ruby. I got a
connection up and running after I realized a couple of things and I am
right now playing with it. I was getting a weird error about IDENTITY
but I think I just figured out how to fix it.

When I finish I'll post here what I hope will be an easy guide to
using JRuby with SQL Server.