Rails & unixODBC


#1

Hello,

I decided to embrace getting a new Rails application to see an
existing MSSQL database and trying to get unixODBC, freeTDS, and
ActiveRecord able to query my MSSQL database.

I’m following the procedure outlined at
http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServerFromRailsOnLinux
but my system (Ubuntu 8.10 - Intrepid Ibex, Rails 2.3, Ruby 1.8.7)
isn’t cooperating.

I get as far as able to use ‘sqsh’ to test the server-side connection
and able to run SQL, but ‘isql’ or the IRB aren’t able to access my
unixODBC DSN.

My /etc/odbc.ini:
[Server1]
Driver = FreeTDS
Description = SQLServer Database
Trace = No
Server = ip_of_server
Port = 1433
Database = DB_Name

My /etc/odbcinst.ini:
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
UsageCount = 2

My /usr/lib/odbc directory:
total 680
-rw-r–r-- 1 root root 9516 2008-09-01 03:14 libesoobS.so
-rw-r–r-- 1 root root 5360 2008-09-01 03:14 libmimerS.so
-rw-r–r-- 1 root root 67604 2008-09-01 03:14 libnn.so
-rw-r–r-- 1 root root 5336 2008-09-01 03:14 libodbcdrvcfg1S.so
-rw-r–r-- 1 root root 5328 2008-09-01 03:14 libodbcdrvcfg2S.so
-rw-r–r-- 1 root root 5388 2008-09-01 03:14 libodbcminiS.so
-rw-r–r-- 1 root root 9440 2008-09-01 03:14 libodbcmyS.so
-rw-r–r-- 1 root root 5332 2008-09-01 03:14 libodbcnnS.so
-rw-r–r-- 1 root root 5424 2008-09-01 03:14 libodbcpsqlS.so
-rw-r–r-- 1 root root 148972 2008-09-01 03:14 libodbctxt.so
-rw-r–r-- 1 root root 5352 2008-09-01 03:14 libodbctxtS.so
-rw-r–r-- 1 root root 9720 2008-09-01 03:14 liboplodbcS.so
-rw-r–r-- 1 root root 5332 2008-09-01 03:14 liboraodbcS.so
-rw-r–r-- 1 root root 5332 2008-09-01 03:14 libsapdbS.so
-rw-r–r-- 1 root root 341668 2008-08-01 18:28 libtdsodbc.so
-rw-r–r-- 1 root root 5320 2008-09-01 03:14 libtdsS.so

Output from IRB test:
irb(main):001:0> require “dbi”
=> true
irb(main):002:0> dbh = DBI.connect(‘dbi:ODBC:ivyweblv’, ‘railsuser’,
‘Pa$$w0rd’)
DBI::DatabaseError: S1000 (0) [unixODBC][FreeTDS][SQL Server]Unable to
connect to data source
from /usr/local/lib/site_ruby/1.8/DBD/ODBC/ODBC.rb:95:in
connect' from /usr/local/lib/site_ruby/1.8/dbi/dbi.rb:584:inconnect’
from /usr/local/lib/site_ruby/1.8/dbi/dbi.rb:384:in `connect’
from (irb):2
from :0

Output from isql test:
[ISQL]ERROR: Could not SQLConnect

I followed the troubleshooting section in the above RubyOnRails Wiki
page, but am not able to get unixODBC to see the freeTDS driver it
seems.

Thanks in advance,
Andy Pflueger


#2

andypflueger wrote:

Hello,

I decided to embrace getting a new Rails application to see an
existing MSSQL database and trying to get unixODBC, freeTDS, and
ActiveRecord able to query my MSSQL database.

I’m following the procedure outlined at
http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServerFromRailsOnLinux
but my system (Ubuntu 8.10 - Intrepid Ibex, Rails 2.3, Ruby 1.8.7)
isn’t cooperating.

I get as far as able to use ‘sqsh’ to test the server-side connection
and able to run SQL, but ‘isql’ or the IRB aren’t able to access my
unixODBC DSN.

My /etc/odbc.ini:
[Server1]
Driver = FreeTDS
Description = SQLServer Database
Trace = No
Server = ip_of_server
Port = 1433
Database = DB_Name

My /etc/odbcinst.ini:
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
UsageCount = 2

My /usr/lib/odbc directory:
total 680
-rw-r–r-- 1 root root 9516 2008-09-01 03:14 libesoobS.so
-rw-r–r-- 1 root root 5360 2008-09-01 03:14 libmimerS.so
-rw-r–r-- 1 root root 67604 2008-09-01 03:14 libnn.so
-rw-r–r-- 1 root root 5336 2008-09-01 03:14 libodbcdrvcfg1S.so
-rw-r–r-- 1 root root 5328 2008-09-01 03:14 libodbcdrvcfg2S.so
-rw-r–r-- 1 root root 5388 2008-09-01 03:14 libodbcminiS.so
-rw-r–r-- 1 root root 9440 2008-09-01 03:14 libodbcmyS.so
-rw-r–r-- 1 root root 5332 2008-09-01 03:14 libodbcnnS.so
-rw-r–r-- 1 root root 5424 2008-09-01 03:14 libodbcpsqlS.so
-rw-r–r-- 1 root root 148972 2008-09-01 03:14 libodbctxt.so
-rw-r–r-- 1 root root 5352 2008-09-01 03:14 libodbctxtS.so
-rw-r–r-- 1 root root 9720 2008-09-01 03:14 liboplodbcS.so
-rw-r–r-- 1 root root 5332 2008-09-01 03:14 liboraodbcS.so
-rw-r–r-- 1 root root 5332 2008-09-01 03:14 libsapdbS.so
-rw-r–r-- 1 root root 341668 2008-08-01 18:28 libtdsodbc.so
-rw-r–r-- 1 root root 5320 2008-09-01 03:14 libtdsS.so

Output from IRB test:
irb(main):001:0> require “dbi”
=> true
irb(main):002:0> dbh = DBI.connect(‘dbi:ODBC:ivyweblv’, ‘railsuser’,
‘Pa$$w0rd’)
DBI::DatabaseError: S1000 (0) [unixODBC][FreeTDS][SQL Server]Unable to
connect to data source
from /usr/local/lib/site_ruby/1.8/DBD/ODBC/ODBC.rb:95:in
connect' from /usr/local/lib/site_ruby/1.8/dbi/dbi.rb:584:inconnect’
from /usr/local/lib/site_ruby/1.8/dbi/dbi.rb:384:in `connect’
from (irb):2
from :0

Output from isql test:
[ISQL]ERROR: Could not SQLConnect

I followed the troubleshooting section in the above RubyOnRails Wiki
page, but am not able to get unixODBC to see the freeTDS driver it
seems.

Thanks in advance,
Andy Pflueger

Hi Andy,

I already do the same thing just like you and follow the link, but not
working. So I try to used the download link www.easysoft.com and working
perfectly in my linux accessing mssql/sqlserver windows;

/etc/odbc.ini

[SQLSERVER_SAMPLE]
Driver = Easysoft ODBC-SQL Server
Description = Easysoft SQL Server ODBC driver
Server = server.domain.com
Port = 1433
Database = northwind
User = sa
Password = password
Mars_Connection = No
Logging = 0
LogFile =
QuotedId = Yes
AnsiNPW = Yes
Language =
Version7 = No
ClientLB = No
Failover_Partner =
VarMaxAsLong = No
DisguiseWide = No
DisguiseLong = No
DisguiseGuid = No
Trusted_Connection = No
Trusted_Domain =
IPv6 = No

[SQLSERVER_SAMPLE_SSL]
Driver = Easysoft ODBC-SQL Server SSL
Description = Easysoft SQL Server ODBC driver
Server = server.domain.com
Port = 1433
Database = northwind
User = sa
Password = password
Mars_Connection = No
Logging = 0
LogFile =
QuotedId = Yes
AnsiNPW = Yes
Language =
Version7 = No
ClientLB = No
Encrypt = Yes
TrustServerCertificate = No
PrivateKeyFile =
CertificateFile =
Entropy =
Failover_Partner =
VarMaxAsLong = No
DisguiseWide = No
DisguiseLong = No
DisguiseGuid = No
Trusted_Connection = No
Trusted_Domain =

[SQLSERVER_PRODUCTION]
Driver = Easysoft ODBC-SQL Server
Description = SQL Server DSN created during installation
Server = myipaddress
Port =
User = myuser
Password = mypassword
Language =
Database = mssqldb
Logging = 0
LogFile =
QuotedId = Yes
AnsiNPW = Yes
Mars_Connection = No

Hope these will help you.


#3

Thanks for the response. I took your advice and installed EasySoft,
but even its giving me the [ISQL]ERROR: Could not SQLConnect response
when I attempt to connect to the DSN setup during EasySoft ODBC
installation. This could be that fact I already had an installation of
freeTDS installed and EasySoft ODBC found and used it. Perhaps I’ll
take Mario’s suggestion and compile freeTDS from source and test
EasySoft or unixODBC again.

Thanks,
Andy

On Feb 13, 5:07 am, Rake NewUser removed_email_address@domain.invalid


#4

I am able connect to SQL Server from OSX, Archlinux. I use this adapter:

http://github.com/rails-sqlserver/2000-2005-adapter/tree/master

I also recommend compiling FreeTDS manually as most of the ones provided
by Linux distros are out of date. Doing so would install to
/usr/local/lib

~/.freetds.conf
[global]
tds version = 8.0

~/.odbc.ini

[dev]
Driver=FreeTDS
Description=Dev Database
Trace=no
Server=10.0.12.141
Database=devmario

~/.odbcinst.ini

[FreeTDS]
Description = FreeTDS
Driver=/usr/local/lib/libtdsodbc.so

$RAILS_ROOT/config/database.yml

development:
adapter: sqlserver
mode: odbc
username: mgutz
password: ‘*******’
dsn: dev


#5

Hi Mario,

I’m curious how (if you got this error) was able to get around it?

DBI::DatabaseError: IM002 (0) [unixODBC][Driver Manager]Data source
name not found, and no default driver specified

I’ve setup the ~/.odbc.ini & ~/.odbcinst.ini config files (in addition
setup global entries in /etc/odbc.ini & /etc/odbcinst.ini) but still
can’t seem to get a record from my SQL database.

My database.yml:
development:
adapter: sqlserver
mode: odbc
dsn: ejtdev
username: myusername

On Feb 13, 10:59 am, Mario G. <rails-mailing-list@andreas-


#6

andypflueger wrote:

Thanks for the response. I took your advice and installed EasySoft,
but even its giving me the [ISQL]ERROR: Could not SQLConnect response
when I attempt to connect to the DSN setup during EasySoft ODBC
installation. This could be that fact I already had an installation of
freeTDS installed and EasySoft ODBC found and used it. Perhaps I’ll
take Mario’s suggestion and compile freeTDS from source and test
EasySoft or unixODBC again.

Thanks,
Andy

On Feb 13, 5:07�am, Rake NewUser removed_email_address@domain.invalid

fyi, i use unixODBC