Click here to change the language (ja-JP).  

This article discusses the methods of connecting to Windows Azure SQL Database from the Ruby language. While this article discusses several gems that can be used to connect to SQL Database, it is by no means a comprehensive listing of all gems that provide this functionality.

Initial Preparation

• Ruby 1.8.7, 1.9.2, or 1.9.3
• Windows Azure Platform subscription
• SQL Database
     
  • Firewall settings that allow connectivity from your client IP address

Ruby Database Connectivity

For connectivity to SQL Database, we will be using the Ruby ODBC, TinyTDS, and Ruby OData gems.  While there are other database connectivity methods available for the Ruby language, not all provide connectivity to SQL Database.

All three gems can be installed through the gem command (http://docs.rubygems.org/read/book/2) by issuing the following commands:

gem install ruby-odbc
gem install tiny_tds
gem install ruby_odata

Using Ruby ODBC

Ruby ODBC provides access to ODBC data sources, including SQL Database. This gem relies on your systems ODBC drivers for connectivity, so you must first ensure you have a working ODBC connection to your SQL Database.  Perform the following steps to configure and test an ODBC connection:

To Configure ODBC on Windows

1. From the start menu, enter ‘ODBC’ in the Search field. This should return a Data Sources (ODBC) program; select this entry.
2. In the Data Sources program, select the User DSN tab, and then click Add.
3. Select SQL Server Native Client 10.0, and then click Finish.
4. Enter a name for this DSN, enter the fully qualified DNS name for your SQL Database, and then click Next.
5. Select ‘With SQL Server authentication’ and enter the login ID and password you created when your database was provisioned. The username must be entered in the following format: ‘username@servername.database.windows.net’. When finished, click Next.
6. Select ‘Change the default database to’ and then select a database other than master. When finished, click Next.
7. Check ‘Use strong encryption for data’ to ensure that data passed between your client and SQL Database is encrypted.  When finished, click Finish.
8. Click Test Data Source to ensure that you can connect.

To Connect to SQL Database using Ruby ODBC

The following code is an example of using Ruby ODBC to connect to a SQL Database specified by a DSN named ‘azure’, perform a select against the ‘names’ table, and return the value of the ‘fname’ field.

require 'odbc'

sql='select * from [names]'
datasource='azure'
username='user@servername.database.windows.net'
password='password'

ODBC.connect(datasource,username,password) do |dbc|
results = dbc.run(sql)
results.fetch_hash do |row|
puts row['fname']
end
end

Active Record can also use Ruby ODBC to connect to a SQL Database.  The following is an example database.yml for using an ODBC connection and the activerecord-sqlserver-adapter gem.

development:
adapter: sqlserver
mode: ODBC
dsn: Driver={SQL Server};Server=servername.database.windows.net;Uid=user@servername.database.windows.net;Pwd=password;Database=databasename
azure: true

CREATE CLUSTERED INDEX [idx_schema_migrations_version] ON [schema_migrations] ([version])

Using TinyTDS

TinyTDS does not rely on ODBC to connect to SQL Database; instead it directly uses the FreeTDS library.  TinyTDS is probably the fastest of the methods to connect to SQL Database from Ruby, and is easy to use; just install it and give it the connection string. It is also cross-platform, so you can use it on Windows, OS X, Linux, etc.

Note: Previous versions were a bit of a complicated install, as you had to build OpenSSL, then build FreeTDS against that, then TinyTDS against that. But with the latest version you can download and install a version that takes care of all that for you.

To install TinyTDS

Use the following command to install the pre-compiled version of TinyTDS, which includes support for securely connecting to SQL Database:

gem install tiny_tds --pre

This will install the precompiled 0.6.3-rc2 version (as of August 5th, 2015.) If you want to specify the version in a Gemfile, use:

gem "tiny_tds", "0.6.3-rc2"

To connect to SQL Database using TinyTDS

The following code is an example of connecting to SQL Database using the tiny_tds gem:

require 'tiny_tds'

client=TinyTds::Client.new(:username=>’user’, :password=> ‘password’, :dataserver=>’servername.database.windows.net', :port=>1433, :database=>’databasename’, :azure=>true)
results=client.execute("select * from [names]")
results.each do |row|
puts row
end

Tiny_tds can also be used with ActiveRecord. The following is an example database.yml for using a dblib connection to SQL Database using the tiny_tds gem.

development:
adapter: sqlserver
mode: dblib
dataserver: 'servername.database.windows.net'
database: databasename
username: user
password: password
timeout: 5000
azure: true

CREATE CLUSTERED INDEX [idx_schema_migrations_version] ON [schema_migrations] ([version])

Using Ruby OData

The Ruby OData gem allows you to connect to an OData service. OData is a RESTful method of accessing data over the internet, using standards such as JSON, AtomPub, and HTTP.  For more information on OData, see http://www.odata.org/.

To configure SQL Database for OData

1. Using your browser, navigate to https://www.sqlazurelabs.com/ and select the OData link. You must sign in with the login associated with your Windows Azure subscription.
2. Enter the name of the SQL Database server that your database resides on, along with the administrator login and password. Select Connect to continue.
3. Select a database, and then check 'Enable OData'.
4. Either select an account to use for anonymous access, or click Add to add a federated user.  You will finally be presented with the URL for the new OData service.

To connect to the OData service using Ruby OData

The following code will connect to an OData service that contains a database named 'testdb'.  The code will then select rows from a table named 'People', and will display the contents of the 'fname' and 'email' fields.

require 'ruby_odata'

svc=OData::Service.new "https://odata.sqlazurelabs.com/OData.svc/v0.1/servername/testdb"
svc.People
people=svc.execute
people.each {|person| puts "#{person.fname} can be contacted at #{person.email}" }

References

• https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/wiki/Using-Azure
• http://www.java2s.com/Code/Ruby/Database/ODBCconnection.htm
• http://metaskills.net/2010/10/18/tinytds-a-modern-simple-and-fast-freetds-library-for-ruby-using-db-library/
• http://blogs.visoftinc.com/2010/06/12/Introducing-a-Ruby-OData-Client-Library/

See Also

• Windows Azure SQL Database TechNet Wiki Articles Index