How to Connect Ruby to SQL Server from Linux and Unix

Ruby on Rails is an open source framework that provides the tools needed to rapidly construct a database-backed Web application.

With no previous Rails experience, we created a functional Rails application, using Linux as our Rails platform and SQL Server as our database backend, by running a few commands and editing a configuration file.

Download SQL Server driver for Ruby.

Access SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014 and SQL Server Express from Ruby

When developing this tutorial, we connected Ruby/Rails on UNIX and Linux to SQL Server 2000–2014 and Express databases.

Contents

Introduction

Ruby on Rails (also known as RoR or Rails) is an open source framework for developing database-backed web applications. Ruby is the object-oriented interpreted scripting language behind the Rails framework. The Rails framework allows developers, database administrators and system administrators to rapidly prototype and develop web applications. A simple application that provides a web frontend to a database table can be created by running a few commands and editing one configuration file.

Production Rails applications currently running on the web include 43things.com (shared to do lists), Backpack (information organiser and calendar), Basecamp (project management), Measure Map (blog analytics tool), Odeo (podcasting portal) and Strongspace (secure file storage and hosting).

Rails is operating system independent, and works with Linux, Windows, Unix and Mac OS X operating systems.

A Rails application may be developed with just a web server and a database. The Rails framework includes a built-in web server, WEBrick and may also be used with other web servers such as Apache, Internet Information Server (IIS) and Mongrel. The Rails framework is configured for the MySQL database by default, but includes adapters that provide support for several other databases. Supported databases include PostgreSQL, SQL Server, Oracle, Sybase, Firebird/InterBase and DB2. Because the database adapters have a database abstraction layer, Rails applications are not tightly coupled to the underlying database.

Rails applications use the Model-View-Controller (MVC) design pattern. The MVC design pattern has three separate components. The Model represents the data, the View represents how a user interacts with the application and the Controller contains the business logic that drives the application. In the Rails framework, the ActiveRecord component represents the Model. It maps database tables to Ruby objects allowing the data to be manipulated by the Controller and displayed by the View. The View provides the user interface for the application. Rails creates the View from template files (.rhtml) that contain HTML and Ruby code. The Controller interprets requests from the user, informing the Model or View to change as appropriate. For example, a user retrieves a record by selecting the action "Show" in a Rails application. To display the record, the controller first uses the corresponding ActiveRecord method, find, in the Model layer to fetch the data. The controller then renders the corresponding template file show.rthml to generate the page that displays the record.

Installing Ruby, RubyGems and Rails on Linux

The Ruby programming language is a prerequisite for Rails.

Installing Ruby

To check whether Ruby is installed, run:

ruby -v

If Ruby is installed, a message reporting the Ruby version number is displayed. When testing Ruby with Easysoft ODBC drivers, we used Ruby 1.8.6. If you get a "command not found" error, Ruby is not installed. To install Ruby:

  1. Download the Ruby distribution from the Ruby web site, unpack and cd into the directory created by unpacking the distribution file. For example:
    cd /tmp
    gunzip ruby-1.8.6-p12.tar.gz
    tar -xvf ruby-1.8.6-p12.tar
    cd ruby-1.8.6-p12
  2. Compile Ruby.
    ./configure
    make
    make test
    make install

Installing RubyGems and Rails

To install Rails, you use the RubyGems development system. RubyGems is a system for managing and installing Ruby code libraries, known as gems. Rails itself is composed of several gems, and once you have successfully installed RubyGems, you can proceed to install Rails. To install RubyGems:

  1. Download RubyGems from RubyGems web site, unpack and cd into the directory created by unpacking the distribution file. For example:
    gunzip rubygems-0.9.2.tgz
    tar -xvf rubygems-0.9.2.tar
    cd rubygems-0.9.2
  2. Install RubyGems:
    ruby setup.rb

When testing Rails with Easysoft ODBC drivers, we used Rails 1.2.2 and 2.0.2.

To install Rails:

The ActiveRecord Database Adapter

ActiveRecord is the Model part of Rails. ActiveRecord maps database tables to Ruby objects allowing the data to be manipulated by controllers and shown in views. ActiveRecord accesses a database through a database adapter. Rails ships with adapters for a number of databases.

The ActiveRecord Database Adapter for SQL Server

The SQL Server database adapter has two modes: ADO and ODBC. ADO mode allows Rails applications running on Windows to access SQL Server. ODBC mode also allows Rails applications running on non-Windows platforms to access SQL Server. In ODBC mode, the adapter connects to SQL Server through an ODBC driver. We used the database adapter with an Easysoft ODBC driver to connect a Rails application running on Linux to a SQL Server database.

Installing the SQL Server Database Adapter

Check whether the SQL Server database adapter is included with your Rails distribution:

gem contents activerecord | grep sqlserver-adapter

If sqlserver_adapter.rb is not in the output, the SQL Server database adapter is not installed. To install the adapter, do one of the following:

gem install activerecord-sqlserver-adapter-1.0.0.gem

–Or–

Copy sqlserver_adapter.rb from https://github.com/rails-sqlserver/activerecord-sqlserver-adapter/tree/master/lib/active_record/connection_adapters/ to the ActiveRecord database adapter directory. (This directory is shown in the output of gem contents activerecord and will be similar to /usr/local/lib/ruby/gems/1.8/gems/activerecord-2.0.2/lib/active_record/connection_adapters.)

SQL Server Database Adapter Prerequisites

In ODBC mode, the SQL Server adapter requires the Ruby/DBI ODBC driver, which depends on the Ruby ODBC module. To use Ruby ODBC with an Easysoft ODBC driver, build Ruby ODBC against the unixODBC driver manager, which is included in the driver distribution.

Install the software in this order on the Ruby on Rails server:

  1. Easysoft ODBC driver
  2. Ruby ODBC
  3. Ruby/DBI ODBC driver

Ruby ODBC

The Ruby ODBC module enables a Ruby script to access an ODBC data source.

To use Ruby ODBC, you need to install an ODBC driver on the Ruby machine.

Installing the Easysoft ODBC-SQL Server Driver

The Easysoft ODBC-SQL Server Driver is a SQL Server ODBC driver for Unix and Linux platforms.

  1. Download the Easysoft ODBC-SQL Server Driver for your application platform. (Registration required.)
  2. Install and license the Easysoft ODBC-SQL Server Driver on the machine where your application is installed.

    For installation instructions, see the Easysoft ODBC-SQL Server Driver documentation. Refer to the documentation to see which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH or SHLIB_PATH depending on the platform and linker).

  3. Create a Easysoft ODBC-SQL Server Driver data source in /etc/odbc.ini that connects to the SQL Server database you want to access. For example:
    [MY_DSN]
    Driver                  = Easysoft ODBC-SQL Server
    Server                  = my_machine\SQLEXPRESS
    User                    = my_domain\my_user
    Password                = my_password
    # If the database you want to connect to is the default
    # for the SQL Server login, omit this attribute
    Database                = Northwind
  4. Use isql to test the new data source. For example:
    cd /usr/local/easysoft/unixODBC/bin
    ./isql -v MY_DSN

    At the prompt, type "help" to display a list of tables. To exit, press return in an empty prompt line.

Installing and Testing Ruby ODBC

We tested Easysoft ODBC drivers with Ruby ODBC 0.9994 and Ruby ODBC 0.9995.

  1. Download the Ruby ODBC distribution from the Ruby ODBC web site, unpack and cd into the directory created by unpacking the distribution file. For example:
    gunzip ruby-odbc-0.9994.tar.gz
    tar -xvf ruby-odbc-0.9994.tar
    cd ruby-odbc-0.9994
  2. To build Ruby ODBC against the unixODBC driver manager that ships with your Easysoft ODBC driver, run extconf.rb with the --with-odbc-dir option. Use the option to specify the driver manager installation directory. By default, this is /usr/local/easysoft/unixODBC.
    ruby extconf.rb --with-odbc-dir=/usr/local/easysoft/unixODBC
    make
    make install
  3. Test your Easysoft ODBC data source with Ruby ODBC. For example:
    ruby test.rb MY_DSN

    To use test.rb, your data source needs to connect to a database in which you can create and drop tables.

Note If you get an error similar to the following when running test.rb:

WARNING: no ODBC driver manager found.
connect............../test/00connect.rb:1:in `initialize':
INTERN (0) [RubyODBC]Cannot allocate SQLHENV (ODBC::Error)
        from ./test/00connect.rb:1:in `connect'

Check that you have set LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH or SHLIB_PATH (depending on your platform and linker) as described in the Easysoft documentation. If your environment is set correctly and you still get this error, try including the --disable-dlopen option when running ruby extconf.rb. When you specify this option, the Ruby ODBC shared object, odbc.so, is linked against the unixODBC driver manager specified by --with-odbc-dir. For example:

ruby extconf.rb --with-odbc-dir=/usr/local/easysoft/unixODBC --disable-dlopen
make clean
make
make install
ldd /usr/local/lib/ruby/site_ruby/1.8/i686-linux/odbc.so
    libodbcinst.so.1 => /usr/local/easysoft/unixODBC/lib/libodbcinst.so.1
    libodbc.so.1 => /usr/local/easysoft/unixODBC/lib/libodbc.so.1
    .
    .
    .

The Ruby/DBI ODBC Driver

The Ruby/DBI module provides a database independent interface for accessing relational databases from within Ruby. Ruby/DBI has a two layer architecture. The database interface (DBI) layer provides a set of common access methods that are used the same way regardless of the underlying database. The database driver (DBD) layer is database dependent. Each driver provides access to a particular database, translating between the DBI layer and the database. The Ruby/DBI ODBC driver provides access to databases for which an ODBC driver is available.

Installing and Testing the Ruby/DBI ODBC Driver

The Ruby ODBC module and an ODBC driver are prerequisties for the Ruby/DBI ODBC Driver

We tested Easysoft ODBC drivers with Ruby/DBI 0.1.1.

  1. Download the Ruby/DBI distribution from the Ruby/DBI web site, unpack and cd into the directory created by unpacking the distribution file. For example:
    gunzip dbi-0.1.1.tar.gz
    tar -xvzf dbi-0.1.1.tar
    cd ruby-dbi
  2. Install the Ruby/DBI ODBC driver:
    ruby setup.rb config --with=dbi,dbd_odbc
    ruby setup.rb setup
    ruby setup.rb install
  3. To test the Ruby/DBI ODBC driver with your Easysoft ODBC data source, create a Ruby script named ruby-dbi-odbc-example and add these lines
    require 'dbi'
    
    # Replace MY_DSN with the name of your ODBC data
    # source. Replace and dbusername with dbpassword with
    # your database login name and password.
    DBI.connect('dbi:ODBC:MY_DSN', 'dbusername', 'dbpassword') do | dbh |
       # Replace mytable with the name of a table in your database.
       dbh.select_all('select * from mytable') do | row |
          p row
       end
    end
    
  4. Run ruby-dbi-odbc-example:
    ruby ruby-dbi-odbc-example

Example Ruby on Rails Web Application

To help you get started quickly, this section shows how to use Rails scaffolding to build a database-backed Rails web application. The scaffold mechanism automatically generates Model, View and Controller code to produce a web application that can create/read/update/delete records in a particular database table.

Rails 2.x

  1. Use an appropriate database utility (for example, mysqladmin, SQL*Plus or osql) to create a database named myrailsapp_development.

    We used isql (included in the Easysoft ODBC driver distribution) to create a SQL Server database:

    /usr/local/easysoft/unixODBC/bin/isql -v MY_DSN
    create database myrailsapp_development
  2. On the machine where Ruby on Rails is installed, use the rails command to create a new Rails application named myrailsapp:
    rails myrailsapp
  3. Change to the myrailsapp directory:
    cd myrailsapp
  4. Generate "scaffolds" for your table. Scaffolds provide the ability to view, create, update, and delete records in the underlying table. For example:
    script/generate scaffold Person LastName:string FirstName:string \
    Title:string Address:string City:string
  5. Open config/database.yml in a text editor. Edit the development connection properties to specify the appropriate adapter for your database and any other settings needed to connect to the myrailsapp_development database.

    By default, Rails assumes a MySQL database backend. The default connections use the mysql database adapter therefore. To connect to a SQL Server database backend from Rails on Linux, we used the sqlserver database adapter in odbc mode:

    # MY_DSN is an ODBC data source that connects to the
    # myrailsapp_development database. db_login is a
    # SQL Server login that has permission to create
    # tables in myrailsapp_development. 
    development:
      adapter: sqlserver
      mode: odbc
      dsn: MY_DSN
      username: db_login
      password: db_password
  6. Open db/migrate/001_create_people.rb in a text editor. Use the self.up and self.down methods to create and drop the people table:
    class CreatePeople < ActiveRecord::Migration
      def self.up
        create_table :people do |t|
          t.column :LastName, :string, :limit => 20
          t.column :FirstName, :string, :limit => 10
          t.column :Title, :string, :limit => 30
          t.column :Address, :string, :limit => 60
          t.column :City, :string, :limit => 15
        end
      end
    
      def self.down
        drop_table :people
      end
    end
    
  7. Create the People table by running the migration’s self.up method. To do this, type:
    rake db:migrate
  8. Open test/fixtures/people.yml in a text editor. Replace the default entries with these sample records:
    # Read about fixtures at http://ar.rubyonrails.org/classes/Fixtures.html
    one:
      id: 1
      LastName: Davolio
      FirstName: Nancy
      Title: Ms.
      Address: 507 - 20th Ave. E.Apt. 2A
      City: Seattle
    two:
      id: 2
      LastName: Fuller
      FirstName: Andrew
      Title: Dr.
      Address: 908 W. Capital Way
      City: Tacoma
    three:
      id: 3
      LastName: Leverling
      FirstName: Janet
      Title: Ms.
      Address: 722 Moss Bay Blvd.
      City: Kirkland
    four:
      id: 4
      LastName: Peacock
      FirstName: Margaret
      Title: Mrs.
      Address: 4110 Old Redmond Rd.
      City: Redmond
    five:
      id: 5
      LastName: Buchanan
      FirstName: Steven
      Title: Mr.
      Address: 14 Garrett Hill
      City: London
  9. Insert the test data into the People table:
    rake db:fixtures:load
  10. With the software installed, the database table created and populated, and the Rails application configured, the application is ready to run. From the myrailsapp directory, run:
    script/server

    This command starts WEBrick, a Ruby web server that is included with the Rails distribution.

  11. In a web browser, go to:

    http://localhost:3000/people

    If your web browser is not running on the same server as Rails, use this URL: http://railshost:3000/people/list. Replace railshost with the host name or IP address of the Rails server.

Rails 1.x

  1. Use an appropriate database utility (for example, mysqladmin, SQL*Plus or osql) to create a database named myrailsapp_development.

    We used isql (included in the Easysoft ODBC driver distribution) to create a SQL Server database:

    /usr/local/easysoft/unixODBC/bin/isql -v MY_DSN
    create database myrailsapp_development

    Rails applications use three different databases -- one for development, one for testing and one for production. myrailsapp_development will be the development database for the example application in this tutorial. The database uses the default naming convention for Rails development databases: appname_development.

  2. On the machine where Ruby on Rails is installed, use the rails command to create a new Rails application named myrailsapp:
    rails myrailsapp

    A simplified version of the application directory structure rails creates when you run this command is shown here:

    myrailsapp
        app                 # Contains the majority of the application code
            controllers
            helpers
            models
            views
        config
            database.yml    # Used to configure database connections
            routes.rb       # Used to define the page that will serve
                            # as the index to the application
        db
            migrate         # Contains scripts used to manage changes
                            # to the database schema (migrations).
        log
            development.log # Logs every action Rails does -- useful
                            # for error tracking
    
        public              # Web server root directory. Contains
                            # static content and web resources.
            images
            javascripts
            stylesheets
        test                # Unit, functional and integration tests.
            fixtures        # Test data files (fixtures).
            functional
            integration
            mocks
            unit
  3. Change to the myrailsapp directory:
    cd myrailsapp
  4. Open config/database.yml in a text editor. Edit the development connection properties to specify the appropriate adapter for your database and any other settings needed to connect to the myrailsapp_development database.

    By default, Rails assumes a MySQL database backend. The default connections use the mysql database adapter therefore. To connect to a SQL Server database backend from Rails on Linux, we used the sqlserver database adapter in odbc mode:

    # MY_DSN is an ODBC data source that connects to the
    # myrailsapp_development database. db_login is a
    # SQL Server login that has permission to create
    # tables in myrailsapp_development. 
    development:
      adapter: sqlserver
      mode: odbc
      dsn: MY_DSN
      username: db_login
      password: db_password

    Rails uses the settings defined in database.yml to connect to the database backend for the web application. Database.yml contains three database connections, development, test and production, which correspond to three runtime environments. Creating a testing or production release application is beyond the scope of this article, so editing just the development section is sufficient for the example application.

    By its design, Rails allows extremely rapid development of web applications. One of the ways Rails achieves this is by requiring application developers to follow set coding standards and naming conventions. Conventions that relate to database tables include:

    • Tables are named using the plural form of the Model they represent. For example, an "employee" Model maps to an "employees" table and a "person" Model maps to a "people" table.

      To find out how Ruby pluralises a word, use the Pluralization Tester for the Ruby on Rails.

    • Tables contain an auto-incremented integer primary key column named id.
  5. To create a table in myrailsapp_development, use an ActiveRecord migration. To do this, first create the migration:
    script/generate migration CreatePeople

    In Rails, all database schema changes occur in a migration. A migration is a Ruby class that either makes one logical change to a database or reverses that change. For example, adding or dropping a table, column or index. The file name for each migration begins with a unique number. For example, 001_create_people.rb or 002_create_jobs.rb. When you apply a migration, Rails checks to see what version of the schema exists in the database (Rails creates and updates a column named schema_info for this purpose). Rails then runs all migrations whose number is greater than the current version. You can also migrate backwards to an older version of the schema.

  6. Open db/migrate/001_create_people.rb in a text editor. Use the self.up and self.down methods to create and drop the people table:
    class CreatePeople < ActiveRecord::Migration
      def self.up
        create_table :people do |t|
          t.column :LastName, :string, :limit => 20
          t.column :FirstName, :string, :limit => 10
          t.column :Title, :string, :limit => 30
          t.column :Address, :string, :limit => 60
          t.column :City, :string, :limit => 15
        end
      end
    
      def self.down
        drop_table :people
      end
    end
    

    The method self.up is used when migrating to a new version of the database schema, self.down is used to roll back any changes. The self.down method is executed when a schema is reverted to an earlier version. For example, reverting the initial schema to version 0 (rake db:migrate VERSION=0) would drop the people table.

    The id column that Rails expects to be present in a table is created automatically, so there is no need to include the column in self.up.

  7. Create the People table by running the migration’s self.up method. To do this, type:
    rake db:migrate
  8. Generate "scaffolds" for your table. Scaffolds provide the ability to view, create, update, and delete records in the underlying table. For example:
    script/generate scaffold Person

    To get an idea of how much code is provided by the automatically generated scaffolds, run rake stats immediately before and after running the scaffold command.

    Note that the example command uses the singular "Person" rather than the plural "People" in the corresponding table name. By default, Rails expects table names to be plural. In the Model, View and Controller code scaffold generates, Rails automatically maps singular object names to a plural database table name.

  9. Open test/fixtures/people.yml in a text editor. Replace the default entries with these sample records:
    # Read about fixtures at http://ar.rubyonrails.org/classes/Fixtures.html
    one:
      id: 1
      LastName: Davolio
      FirstName: Nancy
      Title: Ms.
      Address: 507 - 20th Ave. E.Apt. 2A
      City: Seattle
    two:
      id: 2
      LastName: Fuller
      FirstName: Andrew
      Title: Dr.
      Address: 908 W. Capital Way
      City: Tacoma
    three:
      id: 3
      LastName: Leverling
      FirstName: Janet
      Title: Ms.
      Address: 722 Moss Bay Blvd.
      City: Kirkland
    four:
      id: 4
      LastName: Peacock
      FirstName: Margaret
      Title: Mrs.
      Address: 4110 Old Redmond Rd.
      City: Redmond
    five:
      id: 5
      LastName: Buchanan
      FirstName: Steven
      Title: Mr.
      Address: 14 Garrett Hill
      City: London

    A Rails fixture is a file that contains data you want to test against. people.yml is a fixture for the myrailsapp application. In the Rails testing framework, each test loads the test data in your fixtures at the beginning of a test case. Then, the test case makes changes to the database and tests the results of those changes. Finally, Rails rolls those changes back to return the database to the state that existed before the test ran.

    Although this article does not describe how to test a Rails application -- the fixture is only used to populate the People table -- having the records defined in a fixture means that the data is available to any test cases that you create.

    people.yml is a YAML file, a language that is used to describe structured data. In YAML, structure is shown through indentation (one or more spaces not tabs). Make sure that lines in people.yml do not contain trailing spaces.

  10. Insert the test data into the People table:
    rake db:fixtures:load
  11. With the software installed, the database table created and populated, and the Rails application configured, the application is ready to run. From the myrailsapp directory, run:
    script/server

    This command starts WEBrick, a Ruby web server that is included with the Rails distribution.

  12. In a web browser, go to:

    http://localhost:3000/people/list

    If your web browser is not running on the same server as Rails, use this URL: http://railshost:3000/people/list. Replace railshost with the host name or IP address of the Rails server.

    The Rails application displays all the records in the People table. To display the details view for an individual record, click Show. To update a record, click Edit. To add a new record, click New person. To remove a record, click Destroy.

Ajax Scaffolds

Asynchronous JavaScript + XML (Ajax) allows web pages to get updated while a user is doing a task rather than after a user has completed a task. For example, consider a web application that lets a user update items in a list. An Ajax version of the application would allow in-place editing, replacing a list entry with a form, and then replacing that form with an updated list entry. This dramatically increases the responsiveness of the user interface and makes it feel much more like a desktop application. In contrast, a non-Ajax version of the application would force a new page view for the user to update a list entry.

We used the Ajax Scaffold Generator gem to produce an Ajax version of our example Rails application.

  1. Download the Ajax Scaffold Generator gem.
  2. cd into the directory where you downloaded the gem and then install the Ajax Scaffold Generator. For example:
    gem install ajax_scaffold_generator-3.1.11.gem
  3. Follow the instructions in Example Ruby on Rails Web Application apart from step 8. Replace the command shown in step 8 with:
    script/generate ajax_scaffold Person

Datetime fields

If you are using a table with datetime fields, and get "Fractional truncation" messages, contact for an updated version of the Easysoft ODBC-SQL Server Driver.

Rails Resources

References for this Article