This document is an adaptation from HowtoConnectToMicrosoftSQLServerFromRailsOnLinux to FreeBSD. It basically indicates the port names to use and the correct path names. I have tested these config on FreeBSD 6.1
This document was built in hopes to make getting SQL Server talking to Rails on FreeBSD as painless as possible. It can be overwhelming if you dont know exactly what you need, so I’ve tried to lay it out. Feel free to add anything you feel was not explained in great enough detail.
There are 4 variables i have captialized which should be self explanatory, but if not:
- YOUR_DB_DEFINITION_NAME – Pick something and stick with it. Used for FreeTDS and as your DSN in unixODBC. I used the same for both to keep it simple.
- YOUR_ACTUAL_DB_NAME – The actual name of the database you are trying to connect to
- USERNAME – The username for that database
- PASSWORD – The password for that database
Replace these in the places they show up below.
Assumed:
- Ruby 1.8.5 (/usr/ports/lang/ruby18)
- Rails 1.1.6 (/usr/ports/www/rubygem-rails)
Required:
- FreeTDS >= 0.63 (/usr/ports/databases/freetds)
- unixODBC >= 2.2.11 (/usr/ports/databases/unixODBC)
- Ruby DBI >= 0.0.23 (/usr/ports/databases/ruby-dbi)
- Ruby ODBC >= 0.996 (/usr/ports/databases/ruby-dbd_odbc)
First things first
Become root
FreeTDS:
cd /usr/ports/databases/freetds && make install
# or
portinstall databases/freetds
depending on your version of FreeBSD, you might also have to do
cd /usr/ports/databases/freetds-msdblib && make install
# or
portinstall databases/freetds-msdblib
edit FreeTDS config file
cp /usr/local/etc/freetds.conf.dist /usr/local/etc/freetds.conf
- vi /usr/local/etc/freetds.conf
Add a section to the bottom of the file:
[YOUR_DB_DEFINITION_NAME]
host = 192.168.1.101 (change this to the right one for you)
port = 1433
tds version = 4.2 (might be 8.0, check the docs for the version
of SQL Server you are running)
# tsql -S YOUR_DB_DEFINITION_NAME -U USERNAME -P PASSWORD
You should see and do the following to make sure all went well:
locale is "en_US.ISO-8859-15"
locale charset is "ISO-8859-15"
1> use YOUR_ACTUAL_DB_NAME
2> go
1> select * from users (do this for a table that exists in your db)
2> go
id login
1 user1
3 user2 (make sure you get correct output)
1> quit
If everything went well, you can go back in to freetds.conf and clean it up if you wish, then move on…
unixODBC:
I installed this via apt-get, so I dont have command line instructions and you are on your own for this one.
cd /usr/ports/databases/unixODBC && make install
# or
portinstall databases/unixODBC
After you’ve got it installed:
/usr/local/etc/odbc.ini:
[YOUR_DB_DEFINITION_NAME]
Driver = TDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = YOUR_DB_DEFINITION_NAME
Database = YOUR_ACTUAL_DB_NAME
/usr/local/etc/odbcinst.ini:
[TDS]
Description = FreeTDS
Driver = /usr/local/lib/libtdsodbc.so
UsageCount = 1
Try it out:
- isql YOUR_DB_DEFINITION_NAME USERNAME PASSWORD
———————————————————-
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
———————————————————-
SQL> select * from users; (again, do this for a table that exists in your db)
—————————————————————-+
| id | login |
—————————————————————-+
| 1 | user1 |
| 3 | user2 |
—————————————————————-+
2 rows affected
2 rows returned
SQL> quit
NOTE: If you have problems connecting read my notice at the end of this page
If all went well, move on…
Ruby ODBC:
cd /usr/ports/databases/unixODBC && make install
# or
portinstall databases/unixODBC
Ruby DBI ODBC driver:
NOTE: make sure not to confuse ruby-dbd_odbc with ruby-odbc
cd /usr/ports/databases/ruby-dbd_odbc && make install
# or
portinstall databases/ruby-dbd_odbc
Test everything:
Heres a quick way to see if ruby can talk to SQL Server provided by Mark Imbriaco:
# irb1.8
irb(main):001:0> require "dbi"
=> true
irb(main):004:0> dbh = DBI.connect('dbi:ODBC:YOUR_DB_DEFINITION_NAME', 'USERNAME', 'PASSWORD')
=> #<DBI::DatabaseHandle:0xb7d28688 @trace_output=#<IO:0xb7d79064>, @trace_mode=2,
@handle=#<DBI::DBD::ODBC::Database:0xb7d28480 @attr={},
@handle=#<ODBC::Database:0xb7d284a8>>>
irb(main):005:0> quit
If everything went well, go make it work in Rails:
Setup Rails
database.yml:
development:
adapter: sqlserver
mode: odbc
dsn: YOUR_DB_DEFINITION_NAME
username: USERNAME
password: PASSWORD
Add other sections in the same manner.
SQL Server working with Rails on FreeBSD!
Great thanks to Mark Imbriaco for his work on adding ODBC support, and the others involved in the SQL Server updates.
Troubleshooting
If you get an error along the lines of ‘[iODBC] [Driver Manager]Specified driver could not be loaded’, it might help to set the Driver value in /usr/local/etc/odbc.ini to the path of libtdsodbc.so.
—Thom Lawrence
My odbc and \FreeTd files were not accessable/readable by user in neither /etc nor /usr/local/etc After setting appropriate permsions everything started to work smoothly.
— GregoryBluvshteyn (2006-6-21)
In order to get this to work on a RHEL4 box, the ruby-odbc driver was looking for headers that did not exist. I installed unixODBC-2.2.8-2.3.0.2.i386.rpm and unixODBC-devel-2.2.8-2.3.0.2.i386.rpm in order to compile ruby-odbc. This configuration worked fine with SQL Server 2005. Note that mssql2k5 uses TDS 8.0, the same as sql2k.
— Jeremie Blais (2006-7-18)
category: Howto
This document is an adaptation from HowtoConnectToMicrosoftSQLServerFromRailsOnLinux to FreeBSD. It basically indicates the port names to use and the correct path names. I have tested these config on FreeBSD 6.1
This document was built in hopes to make getting SQL Server talking to Rails on FreeBSD as painless as possible. It can be overwhelming if you dont know exactly what you need, so I’ve tried to lay it out. Feel free to add anything you feel was not explained in great enough detail.
There are 4 variables i have captialized which should be self explanatory, but if not:
- YOUR_DB_DEFINITION_NAME – Pick something and stick with it. Used for FreeTDS and as your DSN in unixODBC. I used the same for both to keep it simple.
- YOUR_ACTUAL_DB_NAME – The actual name of the database you are trying to connect to
- USERNAME – The username for that database
- PASSWORD – The password for that database
Replace these in the places they show up below.
Assumed:
- Ruby 1.8.5 (/usr/ports/lang/ruby18)
- Rails 1.1.6 (/usr/ports/www/rubygem-rails)
Required:
- FreeTDS >= 0.63 (/usr/ports/databases/freetds)
- unixODBC >= 2.2.11 (/usr/ports/databases/unixODBC)
- Ruby DBI >= 0.0.23 (/usr/ports/databases/ruby-dbi)
- Ruby ODBC >= 0.996 (/usr/ports/databases/ruby-dbd_odbc)
First things first
Become root
FreeTDS:
cd /usr/ports/databases/freetds && make install
# or
portinstall databases/freetds
depending on your version of FreeBSD, you might also have to do
cd /usr/ports/databases/freetds-msdblib && make install
# or
portinstall databases/freetds-msdblib
edit FreeTDS config file
cp /usr/local/etc/freetds.conf.dist /usr/local/etc/freetds.conf
- vi /usr/local/etc/freetds.conf
Add a section to the bottom of the file:
[YOUR_DB_DEFINITION_NAME]
host = 192.168.1.101 (change this to the right one for you)
port = 1433
tds version = 4.2 (might be 8.0, check the docs for the version
of SQL Server you are running)
# tsql -S YOUR_DB_DEFINITION_NAME -U USERNAME -P PASSWORD
You should see and do the following to make sure all went well:
locale is "en_US.ISO-8859-15"
locale charset is "ISO-8859-15"
1> use YOUR_ACTUAL_DB_NAME
2> go
1> select * from users (do this for a table that exists in your db)
2> go
id login
1 user1
3 user2 (make sure you get correct output)
1> quit
If everything went well, you can go back in to freetds.conf and clean it up if you wish, then move on…
unixODBC:
I installed this via apt-get, so I dont have command line instructions and you are on your own for this one.
cd /usr/ports/databases/unixODBC && make install
# or
portinstall databases/unixODBC
After you’ve got it installed:
/usr/local/etc/odbc.ini:
[YOUR_DB_DEFINITION_NAME]
Driver = TDS
Description = ODBC connection via FreeTDS
Trace = No
Servername = YOUR_DB_DEFINITION_NAME
Database = YOUR_ACTUAL_DB_NAME
/usr/local/etc/odbcinst.ini:
[TDS]
Description = FreeTDS
Driver = /usr/local/lib/libtdsodbc.so
UsageCount = 1
Try it out:
- isql YOUR_DB_DEFINITION_NAME USERNAME PASSWORD
———————————————————-
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
———————————————————-
SQL> select * from users; (again, do this for a table that exists in your db)
—————————————————————-+
| id | login |
—————————————————————-+
| 1 | user1 |
| 3 | user2 |
—————————————————————-+
2 rows affected
2 rows returned
SQL> quit
NOTE: If you have problems connecting read my notice at the end of this page
If all went well, move on…
Ruby ODBC:
cd /usr/ports/databases/unixODBC && make install
# or
portinstall databases/unixODBC
Ruby DBI ODBC driver:
NOTE: make sure not to confuse ruby-dbd_odbc with ruby-odbc
cd /usr/ports/databases/ruby-dbd_odbc && make install
# or
portinstall databases/ruby-dbd_odbc
Test everything:
Heres a quick way to see if ruby can talk to SQL Server provided by Mark Imbriaco:
# irb1.8
irb(main):001:0> require "dbi"
=> true
irb(main):004:0> dbh = DBI.connect('dbi:ODBC:YOUR_DB_DEFINITION_NAME', 'USERNAME', 'PASSWORD')
=> #<DBI::DatabaseHandle:0xb7d28688 @trace_output=#<IO:0xb7d79064>, @trace_mode=2,
@handle=#<DBI::DBD::ODBC::Database:0xb7d28480 @attr={},
@handle=#<ODBC::Database:0xb7d284a8>>>
irb(main):005:0> quit
If everything went well, go make it work in Rails:
Setup Rails
database.yml:
development:
adapter: sqlserver
mode: odbc
dsn: YOUR_DB_DEFINITION_NAME
username: USERNAME
password: PASSWORD
Add other sections in the same manner.
SQL Server working with Rails on FreeBSD!
Great thanks to Mark Imbriaco for his work on adding ODBC support, and the others involved in the SQL Server updates.
Troubleshooting
If you get an error along the lines of ‘[iODBC] [Driver Manager]Specified driver could not be loaded’, it might help to set the Driver value in /usr/local/etc/odbc.ini to the path of libtdsodbc.so.
—Thom Lawrence
My odbc and \FreeTd files were not accessable/readable by user in neither /etc nor /usr/local/etc After setting appropriate permsions everything started to work smoothly.
— GregoryBluvshteyn (2006-6-21)
In order to get this to work on a RHEL4 box, the ruby-odbc driver was looking for headers that did not exist. I installed unixODBC-2.2.8-2.3.0.2.i386.rpm and unixODBC-devel-2.2.8-2.3.0.2.i386.rpm in order to compile ruby-odbc. This configuration worked fine with SQL Server 2005. Note that mssql2k5 uses TDS 8.0, the same as sql2k.
— Jeremie Blais (2006-7-18)
category: Howto