Forum: IronRuby Ironruby on Rails with SQL Azure

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



  EXECUTE (387.0ms)   USE [cloudoman]
/!\ 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 `<CreateThread>b__0'
    mscorlib:0:in `ThreadStart_Context'
    mscorlib:0:in `Run'
    mscorlib:0:in `Run'
    mscorlib:0:in `ThreadStart'
  EXECUTE (394.0ms)   USE [cloudoman]

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?

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

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.

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)   ..<blah 
blah>"

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   ..<blah blah>"

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

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 Green
http://hotgazpacho.org/

Summarized my learnings here:

http://www.cloudartisan.com/2010/09/ironruby-on-ra...

Ameer Deen
twitter.com/writeameer

Will Green 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 Green
> http://hotgazpacho.org/

The Latest v3 SQL Server Adapter now supports Azure
http://groups.google.com/group/rails-sqlserver-ada...

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.

http://github.com/rails-sqlserver/activerecord-sql...
http://github.com/rails-sqlserver/activerecord-sql...

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