Migrating from Windows/Cygwin to Solaris x86

[clovold]

I'm kinda stuck. I'm migrating from Cygwin under windows to Solaris
x86 and cannot get the database access to work. I've been struggling
for days and my frustration level is very high at this point.

old system:

Apache
Mongrel_cluster
Ruby 1.8.6
Rails 1.2.3
sqlserver using ADO.rb

new system:

Apache
Mongrel_cluster
Ruby 1.8.6
Rails 1.2.3 (trying to keep as much the same to start with)
sqlserver using odbc (?)

I've tried to install what I can, but I'm not sure if I have the whole
ODBC stack installed right. I installed Ruby/Rails using Coolstack
from Sun.

on the working Cygwin install my connection info looked like this:

production:
adapter: sqlserver
database: pfs
username: user
password: password
host: 10.44.8.11

Right now it looks something like this (and I suspect it is wrong):

production:
adapter: sqlserver
mode: ODBC
dsn: Driver={SQL Server};Server=
(10.44.8.11);Database=pfs;Uid=user;Pwd=password
database: pfs
username: user
password: password
host: 10.44.8.11

Anyway, I'm now at this point:

DBI::DatabaseError (INTERN (0) [RubyODBC]Cannot allocate SQLHENV):
/opt/coolstack/lib/ruby/gems/1.8/gems/dbd-odbc-0.2.4/lib/dbd/odbc/
driver.rb:36:in `connect'

So, the 'Driver={SQL Server} part of the dsn line doesn't mean
anything to me. Is this wrong?

My brain is so fried and my head is swimming after all the wrong paths
I have tried. any ideas?


Thanks,

Craig L.

[Ken Collins]

Hey there Craig,

I'd recommend limiting the scope of the troubleshooting to one
particular env. Windows and POSIX systems are going to be slightly
different setups. So to help you come to a conclusion quicker, I'd
recommend we stick with the POSIX system, your solaris box.

To that end, have you installed (1) the recommended gems DBI 0.4.1 and
DBD-ODBC 0.2.4. (2) rb-odbc (3) unixODBC (4) FreeTDS (5) configured
FreeTDS with an odbc.ini DNS for your server? The gems are pretty
easy. The hard part for some is the part of the stack for rb-odbc,
unixODBC, and FreeTDS. There are tons of walk thrus on that via a
Google search and there are post topics on this list that go thru it
too. I'm sure the list can help on these details to.

After that... your dsn in your database.yml will simple be the same
name as the dsn in your odbc.ini file for your unixODBC/FreeTDS install.

- Ken

[Craig Lovold]

Ken,

 

I hate bugging you, but I’m wondering if there is a current walkthrough somewhere on configuring ODBC?  I found an old page:

 

http://oldwiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServerFromRailsOnLinux

 

but after the freeTDS setup they modify the /etc/odbcint.ini and add references to some libraries that don’t exit on my box:

 

Driver          = /usr/lib/odbc/libtdsodbc.so

Setup           = /usr/lib/odbc/libtdsS.so

 

I have freeTDS set up to a point where I can connect to my database with tsql and get into the database.  I just don’t know how to get it set up past that.

 

Are there any resources out there that you know of?

 

Thanks,

 

Craig L.

 

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
_____________________________________________________________________

[Craig Lovold]

OK, so now I have freeTDS and unixODBC working. I can connect to the
database properly from tsql and isql.

odbcinst.ini:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = /opt/csw/lib/libtdsodbc.so
Setup = /opt/csw/lib/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1

odbc.ini:

[PFSSQLSERVER]
Driver = FreeTDS
Description = ODBC connection via FreeTDS
Trace = Yes
Servername = pfssqlserver
Database = pfs


In database.yml I have:

production:
adapter: sqlserver
mode: ODBC
dsn:

Driver={PFSSQLSERVER};Server=(10.44.8.11);Database=pfs;Uid=user;Pwd=pass
word


What's left to test? Why do I still get:

DBI::DatabaseError (INTERN (0) [RubyODBC]Cannot allocate SQLHENV):

/opt/coolstack/lib/ruby/gems/1.8/gems/dbd-odbc-0.2.4/lib/dbd/odbc/driver
.rb:36:in `connect'

/opt/coolstack/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/dbi/handles/driver.r
b:33:in `connect'


What does that even mean? Why is this so painful?

Thanks,

Craig L.

[Ken Collins]

Craig,

Sorry your having issues. I'm no system administrator or package
builder/installation master but I have had my issues with this stack
too. Thanksfully on my system using MacPorts, installing unixODBC, rb-
odbc, FreeTDS goes something like this.

sudo port install unixODBC
sudo port install rb-odbc
sudo port install freetds

I double check that FreeTDS is set to use unixODBC

-=>> tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v0.82
freetds.conf directory: /opt/local/etc/freetds
MS db-lib source compatibility: no
Sybase binary compatibility: no
Thread safety: yes
iconv library: yes
TDS version: 5.0
iODBC: no
unixodbc: yes

I then create a ODBC inst file in /opt/local/etc/odbcinst.ini using
the distro examples. Mine looks like this.

[FreeTDS]
Decscription = FreeTDS driver for SQLServer
Driver = /opt/local/lib/libtdsodbc.so
Setup = /opt/local/lib/libtdsodbc.so
FileUsage = 1

Then I edit the /opt/local/etc/odbc.ini file to include something like
this.

[myDbDsn]
Driver=FreeTDS
Description=Rails Connection To myDb
Servername=mc2000
Server=mc2000
Port=1433
Database=myDb

Lastly my database.yml looks something like this. Others like to use
DSN'less strings to. Flavor to taste.

development:
adapter: sqlserver
mode: ODBC
username: sa
password: password
pool: 5
timeout: 5000
dsn: myDbDsn

If I had to setup the package dependencies outside my mac ports system
on a standard unix box, I'd go nuts like you. It is not my strong
point. I did google your error and there seems to a great amount of
resources on that specific error. The first links said something akin
to Check that you have set LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH or
SHLIB_PATH, but this is mostly greek to me.

http://www.google.com/search?client=safari&rls=en&q=%5BRubyODBC%5DCannot+allocate+SQLHENV&ie=UTF-8&oe=UTF-8
http://www.easysoft.com/developer/languages/ruby/rails.html


- Ken

[Scott Jacobsen]

What does that even mean?  Why is this so painful?

I think it is painfu because there are a lot of layers to this database stack and getting it going is different on every platform. There's no centralized documentation. Basically there's documentation if someone was nice enough to write it up in their blog.

What if we all documented the installation procedure we use on the 2000-2005-adapter wiki at Github? I may need to get this stack going on Redhat soon and would be happy to document how I did it when I figure it out.

[Erik Bryn]

On Thu, Aug 27, 2009 at 11:40 AM, Craig Lovold <clo...@lifetouch.com> wrote:

What does that even mean?  Why is this so painful?

 

Unlike most of the other ActiveRecord database adapters, we don't communicate directly with the DB from our driver. We have several layers in between.

ActiveRecord <-> Ruby-DBI <-> Ruby-ODBC <-> FreeTDS <-> SQL Server

- Erik

[Ken Collins]

Yea... I would totally do a Mac OS documentation. I've done it enough times by now that it's easily rememberable.

 - Ken

[Joe Rafaniello]

I have shared this before but here is my instructions I use for linux... Some are redhat specific but they should be the same or very similar as long as you have any dev packages needed by the compiled code.

Feel free to cleanup/post this if a wiki page is setup.

** Database setup::
1) Install MS SQL 2005 Developer Edition

DB_DEFINITION_NAME: DSN: MY_CONNECTION
DB_NAME: MY_DB
USERNAME: sa
PASSWORD: mypassword

SQL Server Configuration Manager-> Network Configuration -> Protocols for XXX
Enable the TCP/IP Protocol (to allow remote connections)

** Rails machine setup:
Requires:
ruby, rails, and any gems you want
rails-sqlserver-2000-2005-adapter, dbi and dbd-odbc  gems as per the adapter's readme
freetds-0.82
ruby-odbc-0.999 (0.9997 is now available at http://www.ch-werner.de/rubyodbc/ruby-odbc-0.9997.tar.gz)
unixODBC-2.2.12 (2.2.14 is now available at http://www.unixodbc.org/unixODBC-2.2.14.tar.gz)

** Pre-req: Perhaps uninstall any freetds or unixodbc version to avoid conflicts

** If Redhat:
Check if unixODBC or freetds are installed:
rpm -q -a |grep unix
rpm -q -a |grep free

Uninstall them if installed:
ex: rpm -e freetds-0.64-1.el5.rf
ex: rpm -e unixODBC-2.2.11-7.1

1) Edit Profile
Log in as root
nano /etc/profile

export ODBCINI=/etc/odbc.ini
export ODBCSYSINI=/etc
export FREETDSCONF=/etc/freetds/freetds.conf

2) Create a location for the downloaded and extracted files.  I use /usr/local/src

3) Install unixODBC (2.2.12)
cd /usr/local/src
wget http://www.unixodbc.org/unixODBC-2.2.12.tar.gz
gunzip unixODBC*.tar.gz
tar xvf unixODBC*.tar
cd unixODBC*
./configure -prefix=/usr/local -sysconfdir=/etc -disable-gui
make
make install

4) Install FreeTDS (0.82)
cd /usr/local/src
wget ftp://ftp.ibiblio.org/pub/Linux/ALPHA/freetds/stable/freetds-stable.tgz
tar zvxf freetds-stable.tgz
cd freetds*/
./configure --with-odbc=/usr/local/lib
make
make install

# If the make fails due to libarary locations, the configure options which did not work
# for me but were the defaults provided are below:
#    ./configure --with-unixodbc=/usr/local

4b) Test FreeTDS
  nano /usr/local/etc/freetds.conf

  Update text size:
  #  Added limit of 20 MB - FreeTDS truncates anything
  #  beyond this without raising an error
  text size = 2097152
 
    [MY_CONNECTION]
        host = 192.168.5.5
        port = 1433
        tds version = 7.0

    # tds version of 8.0 or higher seems to cause problems on 64 bit linux

    tsql -S MY_CONNECTION -U sa -P mypassword

    locale is "en_US.ISO-8859-15"
    locale charset is "ISO-8859-15"
    1> use MY_DB
    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

5) Create DB Definition:

/etc/odbc.ini:

[MY_CONNECTION]

Driver          = FreeTDS
Description     = ODBC connection via FreeTDS

Trace           = No
Servername      = MY_CONNECTION
Database        = MY_DB

/etc/odbcinst.ini:

[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)

Driver          = /usr/local/lib/libtdsodbc.so
Setup           = /usr/local/lib/libtdsS.so

CPTimeout       =
CPReuse         =
FileUsage       = 1

5a) Set the LD_LIBRARY_Path to avoid load errors
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH

This addresses the following issues:
 - isql in 5b: error while loading shared libraries: libodbc.so.1 (no such file or directory)
 - Ruby DBI in 7b : no ODBC driver manager found.

5b) Test DB definition

isql MY_CONNECTION sa mypassword

# isql MY_CONNECTION sa mypassword
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * users;

SQL> quit


6) Install ruby odbc
    cd /usr/local/src
  wget http://www.ch-werner.de/rubyodbc/ruby-odbc-0.9997.tar.gz
    tar zvxf ruby-odbc-0.9997.tar.gz
    tar zvxf ruby-odbc-0.9997
    cd ruby-odbc*
    ruby extconf.rb
    make
    make install

(odbc.so /usr/local/lib/ruby/site_ruby/1.8/i686-linux)

7 Install the dbi, dbd-odbc and rails-sqlserver-2000-2005-adapter gems as described in the adapter's readme...

7b) Test DBI connection:
# irb1.8

 require 'rubygems'
 require 'dbi'
 dbh = DBI.connect('dbi:ODBC:MY_CONNECTION', 'sa', 'mypassword')
 sth = dbh.execute("select * from users")
 sth.finish

8) configure database.yml
development/production/test:
  adapter: sqlserver
  mode: odbc
  dsn: MY_DB
  username: sa
  password: mypassword

9) Rake the DB