Workiis: Connection from ruby to MS SQL Server

Quick setup guide for: debian, iodbc, ruby, mssql2k

apt-get install libdbi-ruby libdbd-odbc-ruby freetds-dev odbcinst1 iodbc

Installing freetds-dev and odbcinst1 should cause apt-get to offer you a choice of having freetds managed by odbcinst. Say yes. That will create a file /etc/odbcinst.ini

ysantoso@helen:~$ cat /etc/odbcinst.ini
[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
CPTimeout       =
CPReuse         =
FileUsage       = 1

Next thing to do is to setup ~/.freetds.conf. I started with the template at /etc/freetds/freetds.conf.

ysantoso@helen:~$ cat ~/.freetds.conf
[global]
        # Default TDS protocol version. 
        tds version = 4.2

        initial block size = 512

        swap broken dates = no

        swap broken money = no

        # Database server login method, if both server and domain
        # logins are enabled, domain login is tried first if a domain
        # is specified, and if that fails the server login will be
        # used.
        try server login = yes
        try domain login = no

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
;       dump file = /tmp/freetds.log
;       debug level = 10

        # If you get out of memory errors, it may mean that your
          client
        # is trying to allocate a huge buffer for a TEXT field.
        # (Microsoft servers sometimes pretend TEXT columns are
        # 4 GB wide!)   If you have this problem, try setting
        # 'text size' to a more reasonable limit
        text size = 64512


[FooServer]
host            =       fooserver.example.com
port            =       1433
tds version     =       7.0

Then we setup ~/.odbc.ini

ysantoso@helen:~$ cat ~/.odbc.ini
[ODBC Data Sources]
FooDSN = description about FooDSN

[FooDSN]
Driver          = /usr/lib/odbc/libtdsodbc.so
ServerName      = FooServer
Database        = Bar

Next, we test:

ysantoso@helen:~$ iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0205.0204

Enter ODBC connect string (? shows list): ?

DSN                              | Driver
------------------------------------------------------------------------------
FooDSN                           | FooDSN

Enter ODBC connect string (? shows list):
DSN=FooDSN;UID=sa;PWD=lookmanopassword

SQL>select * from tablebaz;
.
.
.
.

Alright, ODBC is setup. Let's try connecting from Ruby. There are two ways: one is to use the ODBC driver directly or using DBI.

I am not familiar with the ODBC driver's API. Looks similar to DBI, but I'm sure it differs in places. So, I just did a quick trial at it just to ensure that the DSN is seen.

irb(main):002:0> require 'odbc'
true
irb(main):005:0> ODBC.datasources
[#]

irb(main):006:0> require 'dbi'
true
irb(main):008:0> dbh=DBI.connect('dbi:ODBC:FooDSN', 'sa', 'lookmanopassword')
#, @attr={}>, @trace_output=#, @trace_mode=\ 2>
irb(main):015:0> dbh.select_one('select count(*) from websession')
[2495950]

(originally from http://microjet.ath.cx/WebWiki/2005.11.03_Connecting_From_Ruby_To_MSSQL.html)

Workiis

2005-11-03

Connection from ruby to MS SQL Server

No comments: