Have you ever had to connect to SQL Server with ActiveRecord? Is this possible? Can anyone provide some starting points?
This what I used:
From here: http://github.com/rails-sqlserver/2000-2005-adapter/tree/master
Installation
First, you will need Ruby DBI and Ruby ODBC. To my knowledge the ADO DBD for DBI is no longer supported. The installation below is not a comprehensive walk thru on how to get all the required moving parts like FreeTDS installed and/or configured. It will also assume gem installations of both the dependent libraries and the adapter itself.
It should be noted that this version of the adapter was developed using both the ancient 0.0.23 version of DBI up to the current stable release of 0.4.0. Because later versions of DBI will be changing many things, IT IS HIGHLY RECOMMENDED that you max your install to version 0.4.0 which the examples below show. For the time being we are not supporting DBI versions higher than 0.4.0. The good news is that if you were using a very old DBI with ADO, technically this adapter will still work for you, but be warned your path is getting old and may not be supported for long.
$ gem install dbi --version 0.4.0
$ gem install dbd-odbc --version 0.2.4
$ gem install rails-sqlserver-2000-2005-adapter -s http://gems.github.com
From here: http://lambie.org/2008/02/28/connecting-to-an-mssql-database-from-ruby-on-ubuntu/
Firstly, update your ~/.profile to include the following:
export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/etc/freetds/freetds.conf
Then reload your .profile, by logging out and in again.
Secondly, on Ubuntu 7.10 Server I needed to install some packages.
mlambie@ubuntu:~$ sudo aptitude install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc
With FreeTDS installed I could configure it like this:
mlambie@ubuntu:/etc/freetds$ cat freetds.conf
[ACUMENSERVER]
host = 192.168.0.10
port = 1433
tds version = 7.0
The important thing here is ACUMENSERVER, which is the DSN that I’ll use when connecting to the database. The host, and port are self-explanatory, and it’s worth noting that I had to use 7.0 specifically as the tds version.
Testing FreeTDS is not too hard:
mlambie@ubuntu:~$ sqsh -S ACUMENSERVER -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 acumen
2> go
1> select top 1 firstname, lastname from tblClients
2> go
[record returned]
(1 row affected)
1> quit
Next up it’s necessary to configure ODBC:
mlambie@ubuntu:/etc$ cat 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
mlambie@ubuntu:/etc$ cat odbc.ini
[ACUMENSERVER]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = ACUMENSERVER
Database = ACUMEN
I then tested the connection with isql:
mlambie@ubuntu:~$ isql -v ACUMENSERVER username password
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> use ACUMEN
[][unixODBC][FreeTDS][SQL Server]Changed database context to 'Acumen'.
[ISQL]INFO: SQLExecute returned SQL_SUCCESS_WITH_INFO
SQLRowCount returns -1
SQL> select top 1 firstname from tblClients;
[record returned]
SQLRowCount returns 1
1 rows fetched
SQL> quit
OK, so we’ve got ODBC using FreeTDS to connect to a remote MSSQL server. All that’s left is to add Ruby into the mix.
mlambie@ubuntu:~$ sudo aptitude install libdbd-odbc-ruby
The last thing to test is that Ruby can use DBI and ODBC to hit the actual database, and that’s easy to test:
mlambie@ubuntu:~$ irb
irb(main):001:0> require "dbi"
=> true
irb(main):002:0> dbh = DBI.connect('dbi:ODBC:ACUMENSERVER', 'username', 'password')
=> #<DBI::DatabaseHandle:0xb7ac57f8 @handle=#<DBI::DBD::ODBC::Database:0xb7ac5744
@handle=#<odbc::database:0xb7ac576c>, @attr={}>, @trace_output=#</odbc::database:0xb7ac576c><io:0xb7cbff54>,
@trace_mode=2>
irb(main):003:0> quit
And a more complete test (only with SQL SELECT, mind you):
#!/usr/bin/env ruby
require 'dbi'
db = DBI.connect('dbi:ODBC:ACUMENSERVER', 'username', 'password')
select = db.prepare('SELECT TOP 10 firstname FROM tblClients')
select.execute
while rec = select.fetch do
puts rec.to_s
end
db.disconnect
</io:0xb7cbff54>
From here (to fix the odbc lib being in the wrong place): http://ubuntuforums.org/showthread.php?t=433435&page=2
libtdsodbc.so
with freeTDS (freetds-dev, tdsodbc), you can either edit the path in the odbcinst.ini file for the [FreeTDS] driver section OR cp the /usr/lib/odbc/libtdsodbc.so into /usr/lib/libtdsodbc.so.
either way works when accessing mssql from the prompt
isql -v $dsn $user $passwd
i found this to be useful
http://www.unixodbc.org/doc/FreeTDS.html#Configuration
And then in the database.yml file:
development:
adapter: sqlserver
mode: odbc
dsn: dsn_name
username: my_username
password: my_password
These are the steps i've compiled for Centos 5.3. It took me a lot of trial and error to get this working. It's from a completely clean Centos installation.
Install EPEL:
rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-3.noarch.rpm
Install ruby, rubygems, freetds, unixODBC, development tools:
yum install gcc
yum install freetds
yum install ruby-devel
yum install unixODBC-devel
yum install ruby rubygems
Install rails:
gem install rails
Install DB related gems:
gem install dbd-odbc
gem install rails-sqlserver-2000-2005-adapter -s http://gems.github.com
Download, build and install ruby-odbc:
wget http://www.ch-werner.de/rubyodbc/ruby-odbc-0.9997.tar.gz
tar zxvf ruby-odbc-0.9997.tar.gz
cd ruby-odbc-0.9997
ruby extconf.rb
make
make install
Final gem list:
# gem list
*** LOCAL GEMS ***
actionmailer (2.3.2)
actionpack (2.3.2)
activerecord (2.3.2)
activeresource (2.3.2)
activesupport (2.3.2)
dbd-odbc (0.2.4)
dbi (0.4.1)
deprecated (2.0.1)
rails (2.3.2)
rails-sqlserver-2000-2005-adapter (2.2.17)
rake (0.8.7)
You can use various tools like isql to test your ODBC connection. Brian's post covers this in nice detail so I won't repeat. In rails you need a database.yml that looks something like this:
development:
adapter: sqlserver
mode: odbc
dsn: dsnName
username: username
password: password
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With