Forum: Ruby on Rails Rails & unixODBC

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
andypflueger (Guest)
on 2009-02-12 21:24
(Received via mailing list)
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/HowtoConne...
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
Rake N. (Guest)
on 2009-02-13 12:07
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/HowtoConne...
> 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.
Mario G. (Guest)
on 2009-02-13 16:51
I am able connect to SQL Server from OSX, Archlinux. I use this adapter:

http://github.com/rails-sqlserver/2000-2005-adapte...

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
andypflueger (Guest)
on 2009-02-13 17:53
(Received via mailing list)
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>
Mario G. (Guest)
on 2009-02-13 17:59
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
andypflueger (Guest)
on 2009-02-13 19:08
(Received via mailing list)
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-
This topic is locked and can not be replied to.