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.  While TinyTDS can be installed using the gem install command, the version that is installed by default does not currently support connectivity to SQL Database (it will work fine for SQL Server.)  In order to communicate with SQL Database, TinyTDS requires a version of FreeTDS that has been compiled with OpenSSL support.  While providing a version of this via gem install is on the TODO list (https://github.com/rails-sqlserver/tiny_tds,) you can currently compile your own version of FreeTDS to enable SQL Database connectivity.

To Build FreeTDS on Windows 7

• Ruby 1.9.2-p180 from http://rubyinstaller.org/
• DevKit-tdm-32-4.5.1-20101214-1400-sfx.exe from http://rubyinstaller.org/
• OpenSSL 0.9.8h (GnuWin32 binary distribution)
• LibIconv 1.9.2 (GnuWin32 binary distribution)

To build FreeTDS with OpenSSL support

1. After installing Ruby, follow the DevKit installation and test steps at https://github.com/oneclick/rubyinstaller/wiki/Development-Kit.
2. Download a current build of FreeTDS from FreeTDS.org. I used freetds-0.91rc.
3. Download and install a Win32 version of OpenSSL and LibIconv. I used the GnuWin32 binary distribution that includes the libraries and header files, however other distributions may also work. Alternatively you may wish to download the source and build your own versions.
4. Install or compile OpenSSL and LibIconv on your system, and then extract the FreeTDS source. To extract the FreeTDS package, you will need a utility that understands the .tgz format, such as 7Zip.
5. In the directory where you have unzipped FreeTDS, find the ‘Configure’ file and edit it. NOTE: Notepad will not correctly recognize the linefeeds at the end of each line, so you may want to use a utility such as Notepad++ so that the file is more readable while editing it.
   Search for –lssl –lcrypto. These should occur on a line similar to the following:

   NETWORK_LIBS="$NETWORK_LIBS –lssl –lcrypto"

   For my environment, I had to change this line to

   NETWORK_LIBS="$NETWORK_LIBS –lssl32 –leay32 –lwsock32 –lgdi32"

   in order to successfully compile the project.
6. From the DevKit folder, launch msys.bat. This will launch an sh.exe command window.
7. Change directories to the location where you extracted the FreeTDS source. Note that while cd d: may work, the actual path reflected by this shell is a UNIX style path of /d. When specifying directories for the ./configure command later, I recommend specifying the paths using the /driveletter/folder format.
8. From the FreeTDS source directory, run the following command:

   ./configure –prefix=<path to install FreeTDS to> —with-libiconv-prefix=<path to iconv> —with-openssl=<path to ssl>

   For example, if libiconv was installed to c:\libiconv and OpenSSL was installeld to c:\OpenSSL, the command would be

   ./configure –prefix=/c/freetds –with-libiconv-prefix=/c/libiconv –with-openssl=/c/openssl

9. After the configuration process completes, run the following command: make
10. Once the make process completes, run make install. This should copy the FreeTDS libraries to the directory specified by –prefix= during configuration.
11. Add the <freetds install directory>\bin folder to the system path environment. This will allow the system to find the FreeTDS dll’s. NOTE: you must also add the OpenSSL\bin folder to the system path.

To build tiny_tds using the local FreeTDS library

gem install tiny_tds -platform=ruby -- --with-freetds-dir=<FreeTDS installation folder>

typedef tds_sysdep_int64_type DBBIGINT;

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