Accessing Microsoft SQL Server from Ruby

Being able to access SQL Sever from Ruby allows constructing tools for reports and checks with data from Jira, Silk Test Manager, and other systems. However, accessing SQL Server with Ruby seems shrouded with mystery. This is partly due to a lack of documentation about installation of support for specific databases, partly due to a bias toward MySql as opposed Microsoft products, and partly due to fractured and scattered information about Ruby and SQL Server. In some cases, information on the Internet presupposes knowledge that the developer may not have. Therefore, this page provides detailed instructions.

• RubyForge Project Page for DBI
• DBI RDoc documentation
• Paul DuBoise, "Using the Ruby DBI Module"

These instructions assume that the following software is installed:

• SQL Server 2008
• Ruby 1.8
• Windows XP Professional

These instructions also assume you are working with version 0.4.2 of DBI and version 0.2.5 of DBD-ODBC.

These instructions may well work with other versions of the above software, but they have not been tested with these versions.

These procedures use a System Data Source Name. This type of DSN requires the SQL Server port (default port 1433) to be open on any firewall software on the computer, even if the database and the installation of the DSN are on the same computer.

Ruby DBI is a "database-independent interface for Ruby scripts" [Paul DuBois]. Paul DuBois's article "Using the Ruby DBI Module" is the standard documentation for DBI. For specific databases, DBI calls database specific drivers referred to as DBDs. Besides installing Ruby DBDs, you will need to install the DBD for ODBC. ODBC support is provided on every supported version of Windows. Later on, these instructions will discuss how to use the Windows capabilities to set up ODBC.

One source of confusion about using DBI with SQL Server is that there is a second possible driver called ADO. ADO is a later, in a long line of database access methods, from Microsoft. ADO seems harder to use because:

• the ADO DBD driver is not readily available from the current downloads. It can be found in the Zip file of the DBI, version 0.2.2.
• Once it is found, the ADO.rp Ruby script must be moved to the right directory.
• Once the ADO DBD driver is installed, you have to figure out how to invoke it properly.

I was never able to get it to work properly. Therefore, I have favored the ODBC driver.

Paul DuBois's documentation describes how to install DBI using the setup script. With version 0.4.0, DBI has installation with Ruby gems. Therefore, these instructions describe how to install DBI using gem.

Installation

Execute these steps to install DBI and DBD-ODBC:

1. Launch a command window.
2. Execute: gem install dbi
3. Execute: gem install dbd-odbc

The installation of DBI should also cause the installation of the deprecated gem.

Test

Execute these steps to test the installation of the DBI and DBD-ODBC gems:

1. Launch a command window.
2. Launch the irb: irb
3. Enter: require 'dbi'

If DBI is installed properly, the require statement will return true.

Next is setting up a ODBC data source on the computer. This is accomplished with a program provided with the Windows operating system.

Installation

1. From the Windows start menu, select Administrative Tools->Data Source (ODBC)
2. Select the System DSN tab.
   
3. Click the Add... button.
4. Select SQL Server from the list of drivers.
   
5. Click the Finish button.
6. In the Name field, enter a name for the dataset.
   
7. In the Description field, enter an optional description of the data source.
8. In the Server field, either select (local) if the database is on the local machine or enter the name of another server where the database resides.
9. Click the Next button.
10. In the next screen, select the method of authentication to be used in accessing SQL Server
    
11. Leave the check box Connect to SQL Server to obtain default settings for the additional configuration options.
12. Click the Next button.
13. In the next screen, check the Change the default database to check box and select the desired database.
    
    Note

    The master database is a SQL Server database used by the system. It is used here for demonstration purposes, but it is seldom of much interest for applications.
14. Click the Next button.
15. On the next screen, leave the default values and click the Finish button.
    
16. On the next screen, click the OK button to save the configuration.
    

Testing

To test the ODBC data source, click on the Test Data Source... button. The popup window should report a successful connection.

If the connection test fails, be sure that the SQL Server port (default port 1433) is open on the computer where the DSN is installed. If this port is open and the test still fails, use Microsoft SQL Server Management Studio to confirm the database connection, user id, and password.

Launch a command window and invoke the irb program. Execute the following commands:

require 'dbi'
conn = DBI.connect('DBI:ODBC:MYDSN', 'sa', 'XXXX')
conn.connected?

Substitute your user id for sa and your password for XXXX in the statement above. The first string may be case insensitive, but using upper case for DBI and ODBC is best. The string MYDSN should be replaced by your name of the Data Source Name. The connected? method should return true.