Share via

Connect to Windows Azure SQL Database from Ruby Applications

  • Article

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.

NOTE: The procedures listed in this article may not work on all operating systems due to availability of ODBC drivers, differences in compilation process, etc. Currently this article contains information based on the Windows 7 and 8 operating systems and the Windows Azure web or worker role hosting environment.

 

Initial Preparation

This article assumes that you are familiar with the Ruby language. It also assumes that you have the following:

  • 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

NOTE: For more information on Ruby, visit http://www.ruby-lang.org/. For more information on the Windows Azure Platform, specifically for getting started with SQL Database, see http://social.technet.microsoft.com/wiki/contents/articles/getting-started-with-the-sql-azure-database.aspx.

 

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

NOTE: As of August 5th, 2015, you should use the --pre flag when installing TinyTDS on Windows, as this will install a version that contains the bits you need to communicate with SQL Database.

 

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

NOTE: user, password, databasename and servername in the above examples must be replaced with valid values for your SQL Database.

NOTE: All tables in SQL Database require a clustered index. If you receive an error stating that tables without a clustered index are not supported, add a :primary_key field.

NOTE: If you are using an older version of Ruby on Rails, Active Record, or the activerecord-sqlserver-adapter gem, you may receive an error when running migration (rake db:migrate). You can run the following command against your SQL Database to manually create a clustered index for this table after receiving this error:

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

After creating the clustered index, rerun the migration and it should succeed.

 

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=> , :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

NOTE: All tables in SQL Database require a clustered index. If you receive an error stating that tables without a clustered index are not supported, add a :primary_key field.

NOTE: If you are using an older version of Ruby on Rails, Active Record, or the activerecord-sqlserver-adapter gem, you may receive an error when running migration (rake db:migrate). You can run the following command against your SQL Database to create a clustered index for this table after receiving
this error:

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

After creating the clustered index, rerun the migration and it should succeed.

 

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

OData support for SQL Database is currently a Community Technical Preview (CTP) and can be accessed at http://www.sqlazurelabs.com.  To enable OData for an existing database, perform the following steps:

  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

See Also