Site navigation
Connect Ruby on Rails applications on Linux and UNIX to SQL Server.
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.
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.
The Ruby programming language is a prerequisite for Rails.
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:
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
./configure make make test make install
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:
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
ruby setup.rb
When testing Rails with Easysoft ODBC drivers, we used Rails 1.2.2 and 2.0.2.
To install Rails:
gem install rails --include-dependencies
If you access the Internet through an HTTP proxy, use the HTTP_PROXY environment variable to specify your proxy server. For example:
HTTP_PROXY=http://my_proxy_server:8080 export HTTP_PROXY
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 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.
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.)
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:
Connect Ruby on Rails applications on Linux and UNIX to SQL Server.
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.
The Easysoft SQL Server ODBC driver is a SQL Server ODBC driver for UNIX and Linux platforms.
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).
/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
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.
We tested Easysoft ODBC drivers with Ruby ODBC 0.9994 and Ruby ODBC 0.9995.
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
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
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 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.
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.
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
ruby setup.rb config --with=dbi,dbd_odbc ruby setup.rb setup ruby setup.rb install
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
endruby-dbi-odbc-example:
ruby ruby-dbi-odbc-example
Connect Ruby on Rails applications on Linux and UNIX to SQL Server.
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.
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 command to create a new Rails application named myrailsapp:
rails myrailsapp
myrailsapp directory:
cd myrailsapp
script/generate scaffold Person LastName:string FirstName:string \ Title:string Address:string City:string
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
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
endself.up method. To do this, type:
rake db:migrate
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
People table:
rake db:fixtures:load
myrailsapp directory, run:
script/server
This command starts WEBrick, a Ruby web server that's included with the Rails distribution.
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.
Connect Ruby on Rails applications on Linux and UNIX to SQL Server.
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.
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
myrailsapp directory:
cd myrailsapp
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:
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.
id.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.
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
endThe 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.
People table by running the migration's self.up method. To do this, enter:
rake db:migrate
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.
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.
People table:
rake db:fixtures:load
script/server
This command starts WEBrick, a Ruby web server that is included with the Rails distribution.
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.
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.
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
script/generate ajax_scaffold Person
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.
Connect Ruby on Rails applications on Linux and UNIX to SQL Server.