Rails & unixODBC

rubyonrails-talk
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:in `connect'         from /usr/local/lib/site_ruby/1.8/dbi/dbi.rb:384:in `connect'         from (irb):2

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:in `connect'         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

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

4

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

5

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 <rails-mailing-l...@andreas-s.net>

fyi, i use unixODBC