• Rails and Microsoft Sql Server

    So you want to connect your rails application to Microsoft SQL Server. The following instructions will take you through setting this up quite simply. This setup is on a a Ubuntu 8.04 server connecting to SQL Server 2000 (we will use the Northwind sample database). The first thing you need to do is install the following gems:

    $ gem install dbi --version 0.4.0
    $ gem install dbd-odbc --version 0.2.4
    $ gem install rails-sqlserver-2000-2005-adapter -s https://gems.github.com

    More info can be found on the sqlserver adapter gem development page. This is just the first step.

    Now modify your ~/.profile to include the following environment settings:

    export ODBCINI=/etc/odbc.ini
    export ODBCSYSINI=/etc
    export FREETDSCONF=/etc/freetds/freetds.conf

    Reload by logging out then back in.

    We need to install some packages, including FreeTDS, unixodbc, and the ruby odbc library:

    $ sudo aptitude install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc libdbd-odbc-ruby

    Configure the newly installed FreeTDS with the settings for your MS Sqlserver:

    $ nano /etc/freetsd/freetsd.conf

    Add the following, make sure to use tabs (‘–tab–‘), not spaces for the settings:

    '--tab--'  host = (IP ADDRESS OR FQDN)
    '--tab--'  port = 1433
    '--tab--'  tds version = 7.0

    Save and exit. Note we used tds version 7.0, other tds versions are here.

    Test your connection using sqsh, ‘skwish’:

    $ sqsh -S MSSQLSERVER -U username -P password
    sqsh: Symbol `_XmStrings' has different size in shared object, consider re-linking
    sqsh-2.1 Copyright (C) 1995-2001 Scott C. Gray
    This is free software with ABSOLUTELY NO WARRANTY
    For more information type '\warranty'
    1> use Northwind
    2> go
    1> select top 1 CompanyName from Suppliers
    2> go
    [record returned]
    (1 row affected)
    1> quit

    The next step is to configure the ODBC, there are a few ways but this worked for us:

    $ nano /etc/odbcinst.ini

    Input the following:

    Description     = TDS driver (Sybase/MS SQL)
    Driver          = /usr/lib/odbc/libtdsodbc.so
    Setup           = /usr/lib/odbc/libtdsS.so
    CPTimeout       =
    CPReuse         =
    FileUsage       = 1

    Now configure the odbc.ini

    $ nano /etc/odbc.ini

    We used the following:

    Driver          = FreeTDS
    Description     = ODBC connection via FreeTDS
    Trace           = No
    Servername      = MSSQLSERVER
    Database        = Northwind

    Another test now before the final test, make sure you can connect to the database and run a query. First connect:

    $ isql -v NORTHWIND username password
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    SQL> use Northwind
    [][unixODBC][FreeTDS][SQL Server]Changed database context to 'Northwind'.
    SQLRowCount returns -1
    SQL> select top 1 CompanyName from Suppliers;
    [record returned]
    SQLRowCount returns 1
    1 rows fetched
    SQL> quit

    Finally, lets make sure ruby can connect, launch irb, once launched type the following:

    irb(main):001:0> require "dbi"
    => true
    irb(main):002:0> dbh = DBI.connect('dbi:ODBC:NORTHWIND', 'username', 'password')
    => #, @attr={}>, @trace_output=#,
    irb(main):003:0> quit

    A more complete test can be run by running a small script (albeit just a select). Create an rb file and add the following code:

    #!/usr/bin/env ruby
    require 'dbi'
    db = DBI.connect('dbi:ODBC:NORTHWIND', 'username', 'password')
    select = db.prepare('SELECT CompanyName FROM Suppliers')
    while rec = select.fetch do
      puts rec.to_s

    Save that file then execute it with ruby. Hopefully all is well.

    Modify your database.yml to connect to the Northwind sqlserver database and you will be all set:

    dsn: NORTHWIND
    username: user
    password: pass

    If everything seems to be failing to connect, make sure you can see your sqlserver and that you can connect. An easy way to test this is to telnet to the server or even just ping it:

    $ telnet IPADDRESS(or FQDN) PORT
    $ ping IPADDRESS (or FQDN)

    Your Ruby application should now have no problem connecting to the Microsoft SQL Server


    4 responses to “Rails and Microsoft Sql Server” RSS icon

    • You know, the thing about SQL is, that there is virtually nothing that can replace it.

      Does anyone know if a substitute exists for sql? I mean besides MS SQL and Oracle and all that jazz. Thanks.

    • These are all proposed alternatives to SQL:

      * .QL – object-oriented Datalog
      * 4D Query Language (4D QL)
      * Aldat Relational Algebra and Domain algebra
      * Datalog
      * Hibernate Query Language (HQL) – A Java-based tool that uses modified SQL
      * IBM Business System 12 (IBM BS12)
      * ISBL
      * Java Persistence Query Language (JPQL) – The query language used by the Java Persistence API in Java EE5
      * LINQ
      * Object Query Language
      * QBE (Query By Example) created by Moshè Zloof, IBM 1977
      * QLC – Query Interface to Mnesia, ETS, Dets, etc (Erlang programming language)
      * Quel introduced in 1974 by the U.C. Berkeley Ingres project.
      * Tutorial D
      * XQuery

    • Hey great article!

      I needed the above setup on my mac (Leopard), surprisingly the above instructions worked just fine for me. The only difference was the installation of FreeTDS, I got it using mac ports with

      sudo port install freetds +msql

      Also, ODBC is pre-installed on Macs so I didn’t need to bother with that step either. Thanks again.

    • Great article! I struggled with this due to my SQL Server having a named instance. e.g.\SQLEXPRESS. Once I set up a default instance everything was fine.


    1 Trackbacks / Pingbacks

    Leave a reply