Ironruby on Rails with SQL Azure

writeameer · September 1, 2010, 3:29am

Hello Everyone,

I’m trying to connect a vanilla rails application running on IronRuby to
SQLAzure. The rails app is running on my laptop trying to connect to SQL
Azure. When I point database.yml against my local instance, I’m able to
communicate with SQL Server:

development:
mode: ADONET
adapter: sqlserver
host: .
database: mydb
username: myuser
password: mypassword

But when I point to SQL Azure:

development:
mode: ADONET
adapter: sqlserver
host: nrvk7uv6tl.database.windows.net
database: mydb
username: myuser
password: mypassword

I get the error listed below. Has anyone had luck with Ironruby and SQL
Azure? I am able to connect to the SQL Azure instance using Enterprise
manger to verify the database is up and the user credentials are
correct. I can see from my netstat that myrails app is trying to connect
to the correct IP at port 1433 and has an established connection.

I’m not sure how to debug this further and would really appreciate
anyone pointing me in the right direction. Thanks for any help in
advance.

Ameer

e[4;36;1mEXECUTE (387.0ms)e[0m e[0;1mUSE [cloudoman]e[0m
/!\ FAILSAFE /!\ Wed Sep 01 10:40:33 +1000 2010
Status: 500 Internal Server Error
You have a nil object when you didn’t expect it!
You might have expected an instance of ActiveRecord::Base.
The error occurred while evaluating nil.[]
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.8/lib/active_record/connection_adapters/sqlserver_adapter.rb:231:in
database_year' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.8/lib/active_record/connection_adapters/sqlserver_adapter.rb:203:in initialize’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-sqlserver-adapter-2.3.8/lib/active_record/connection_adapters/sqlserver_adapter.rb:28:in
sqlserver_connection' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:in send’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:in
new_connection' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:245:in checkout_new_connection’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:188:in
checkout' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:in loop’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:184:in
checkout' C:/ironruby-1.1/lib/ruby/1.8/monitor.rb:242:in mon_synchronize’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:183:in
checkout' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:98:in connection’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:326:in
retrieve_connection' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_specification.rb:123:in retrieve_connection’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_specification.rb:115:in
connection' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/query_cache.rb:9:in cache’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/query_cache.rb:28:in
call' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/activerecord-2.3.8/lib/active_record/connection_adapters/abstract/connection_pool.rb:361:in call’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/actionpack-2.3.8/lib/action_controller/string_coercion.rb:25:in
call' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/rack-1.1.0/lib/rack/head.rb:9:in call’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/rack-1.1.0/lib/rack/methodoverride.rb:24:in
call' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/actionpack-2.3.8/lib/action_controller/params_parser.rb:15:in call’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/actionpack-2.3.8/lib/action_controller/session/cookie_store.rb:99:in
call' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/actionpack-2.3.8/lib/action_controller/failsafe.rb:26:in call’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/rack-1.1.0/lib/rack/lock.rb:11:in
call' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/rack-1.1.0/lib/rack/lock.rb:11:in synchronize’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/rack-1.1.0/lib/rack/lock.rb:11:in
call' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/actionpack-2.3.8/lib/action_controller/dispatcher.rb:114:in call’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/actionpack-2.3.8/lib/action_controller/reloader.rb:34:in
run' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/actionpack-2.3.8/lib/action_controller/dispatcher.rb:108:in call’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/rails-2.3.8/lib/rails/rack/static.rb:31:in
call' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/rack-1.1.0/lib/rack/urlmap.rb:45:in call’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/rack-1.1.0/lib/rack/urlmap.rb:41:in
each' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/rack-1.1.0/lib/rack/urlmap.rb:41:in call’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/rails-2.3.8/lib/rails/rack/log_tailer.rb:17:in
call' C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/rack-1.1.0/lib/rack/content_length.rb:13:in call’
C:/ironruby-1.1/lib/ironruby/gems/1.8/gems/rack-1.1.0/lib/rack/handler/webrick.rb:48:in
service' C:/ironruby-1.1/lib/ruby/1.8/webrick/httpserver.rb:104:in service’
C:/ironruby-1.1/lib/ruby/1.8/webrick/httpserver.rb:65:in run' C:/ironruby-1.1/lib/ruby/1.8/webrick/server.rb:173:in start_thread’
IronRuby.Libraries:0:in RubyThreadStart' IronRuby.Libraries:0:in b__0’
mscorlib:0:in ThreadStart_Context' mscorlib:0:in Run’
mscorlib:0:in Run' mscorlib:0:in ThreadStart’
e[4;36;1mEXECUTE (394.0ms)e[0m e[0;1mUSE [cloudoman]e[0m

writeameer · September 1, 2010, 4:01am

My guess is that SQL Azure doesn’t follow quite the same protocol as
normal
SQL Server. As I don’t have a SQL azure account, I can’t play around
with it
myself, but I’d suggest trying to get a simple command line version of
IronRuby up and running and see if you can use the raw sqlserver adapter
to
connect to SQL azure at all?

writeameer · September 1, 2010, 3:44pm

Hi there Orion,

Firstly, thanks very much for the prompt reply! I tried using IronRuby
to query against my local sql server and SQL Azure. I’m a Ruby noob but
I’ve listed below what I used.

I’m able to query against both SQL servers and obtain the time and
servername using Transact-SQL. As mentioned earlier, however, I was
unable to use rails to connect to SQL Azure.

The SQL Azure credential I’ve listed below is real and can be used for
testing.

Any other ideas?

test script:

require ‘mscorlib’
require ‘System.Data, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089’

include System::Data::SqlClient

#connection =
SqlConnection.new(‘Server=.;Database=dev;Uid=myuser;Pwd=MyP@ssw0rd’)
connection =
SqlConnection.new(‘Server=nrvk7uv6tl.database.windows.net;Database=cloudoman;Uid=myuser;Pwd=MyP@ssw0rd’)
connection.open

command = SqlCommand.new(“SELECT getdate() as my_date, @@SERVERNAME AS
my_name”,connection)

my_reader = command.execute_reader()
my_reader.each do |x|
puts “Server Name: #{x[‘my_name’].to_s}”
puts “Time: #{x[‘my_date’].to_s}”
end

my_reader.close
connection.close

Orion E. wrote:

My guess is that SQL Azure doesn’t follow quite the same protocol as
normal
SQL Server. As I don’t have a SQL azure account, I can’t play around
with it
myself, but I’d suggest trying to get a simple command line version of
IronRuby up and running and see if you can use the raw sqlserver adapter
to
connect to SQL azure at all?

writeameer · September 2, 2010, 1:54pm

Got it. There’s a small issue with sqlserver_adapter.rb and the way
Micrsoft Azure advertises it’s version.

There is a database_year function that tries to determine the version of
SQL Server from the output of the following query:

“SELECT @@version”

For Non Azure SQL Server databases, the output is similar to the
following:

“Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) …”

the database_year funtion grabs the SQL Server version from the 4 digits
following the word “Microsoft SQL Server”:

  DATABASE_VERSION_REGEXP     = /Microsoft SQL Server\s+(\d{4})/

  def database_year
    DATABASE_VERSION_REGEXP.match(database_version)[1].to_i
  end

In Azure, however, the query returns a different string:

“Microsoft SQL Azure (RTM) - 10.25.9386.0 …”

That database_year function, as a result returns a nil string and
breaks. As a test, I just changed the function to:

  def database_year
    2008
  end

and I was able to connect my rails application to SQL Azure. There just
needs to be a smarter way to ascertain the SQL Server version. Possible
match up the 10.XX version number with the SQL Server 2005/2008 etc
versions.

Thanks very much for the help.

Ameer

writeameer · September 1, 2010, 5:09pm

I was using this tutorial to get ironruby on rails running:

http://ironruby.net/documentation/rails

which uses the activerecord-sqlserver-adapter. I’m running rails 2.3.8.

Ameer.

writeameer · September 8, 2010, 4:17pm

Summarized my learnings here:

http://www.cloudartisan.com/2010/09/ironruby-on-rails-on-azure-part-2-of-3-–-connecting-your-rails-app-to-sqlazure/

Ameer D.
twitter.com/writeameer

Will G. wrote:

Should probably change the RegExp to match after the dash (10.x.x.x),
and
use that as the version, rather than, e.g., 2008, 2005, 2000, etc.

–
Will G.
http://hotgazpacho.org/

writeameer · April 2, 2011, 2:58am

The Latest v3 SQL Server Adapter now supports Azure
http://groups.google.com/group/rails-sqlserver-adapter/browse_thread/thread/50dc2108c4fda584?hl=en

You must use the latest DBLIB/TinyTDS connection mode which also means
you must use the latest FreeTDS compiled with OpenSSL. All the details
are here.

I realize some of this may be only informative for IronRuby in an
indirect way. It is also worth pointing out that FreeTDS/TinyTDS will
soon be available as precompiled libs for Windows with the
RailsInstaller project too.

HTH, Ken

writeameer · September 2, 2010, 6:11pm

Should probably change the RegExp to match after the dash (10.x.x.x),
and
use that as the version, rather than, e.g., 2008, 2005, 2000, etc.

–
Will G.
http://hotgazpacho.org/