Download ODBC driver for Ruby Connect Ruby on Rails applications on Linux and UNIX to SQL Server.

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 back end, by running a few commands and editing a configuration file.

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 Backpack (information organiser and calendar), Basecamp (project management) and Strongspace (secure file storage and hosting).

Rails is operating system independent, and works with Linux, Windows, UNIX, and macOS 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
Download ODBC driver for Ruby Connect Ruby on Rails applications on Linux and UNIX to SQL Server.

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 SQL Server ODBC driver

The Easysoft SQL Server ODBC driver is a SQL Server ODBC driver for UNIX and Linux platforms.

  1. Download the Easysoft SQL Server ODBC driver for your application platform.
  2. Install and license the SQL Server ODBC driver on the machine where your application is installed.

    For installation instructions, refer to the SQL Server ODBC driver documentation. Refer to the documentation to find out 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 SQL Server ODBC 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, enter 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 ODBC 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 ODBC 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 prerequisites 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
Download ODBC driver for Ruby Connect Ruby on Rails applications on Linux and UNIX to SQL Server.

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, or 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, or 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 back end. The default connections use the mysql database adapter therefore. To connect to a SQL Server database back end 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's 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.

Download ODBC driver for Ruby Connect Ruby on Rails applications on Linux and UNIX to SQL 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 back end. The default connections use the mysql database adapter therefore. To connect to a SQL Server database back end 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 back end 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 find out 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, enter: 
    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, choose 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're using a table with datetime fields, and get Fractional truncation messages, contact the Easysoft Support team () for an updated version of the SQL Server ODBC driver.

Rails resources

References for this article

Download ODBC driver for Ruby Connect Ruby on Rails applications on Linux and UNIX to SQL Server.