Simple Remote Odbc Database Connection with Ruby

simple remote ODBC database connection with ruby

I think what you are looking for is Ruby DBI.

http://ruby-dbi.rubyforge.org/

Here is an example of using DBI for ODBC connectivity:

# Require in the DBI files<br />
require 'DBI'

# create an ODBC connection instance<br />
dbi_conn = DBI.connect('DBI:ODBC:datasource','your_username','your_password')

# query tables available  <br />
dbi_conn.tables

# returns an array with the results from a table TABLE:<br />
array_out = dbi_conn.select_all('SELECT * FROM TABLE')

some more in-depth info:

http://www.kitebird.com/articles/ruby-dbi.html

Or, alternatively, you can use ruby-odbc:

http://odbc-rails.rubyforge.org/

Ruby ODBC with remote database

My issue was ultimately twofold. I was connecting to Eloquence and SQL/R over a VPN connection which wasn't as stable as I thought and so connections were dropping as a result.

The other issue was that SQL/R uses Server instead of ServerName and Service instead of Port in the odbc.ini file.

Once I stabilized my VPN and fixed the odbc.ini file I was able to connect without issue.

Getting ODBC connection results as ruby object

It looks like it's returning an array of objects, instead of just one. If you only want a single object, just add .first to the result.

Remote MSSQL/ODBC Syncing With Rails

I would advise you to create a ruby script that can be scheduled to do the data retrieving.
In order to connect to the MSSQL database, please take a look at this simple project I've created.

Then you only need to code the data you want to retrieve and the way you store it.

I prefer the approach of being decoupled from your rails application, although you can use a scheduler like rufus-scheduler or sidekiq and run it with your application.

Connecting to SQL Server with ActiveRecord

This what I used:

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

Installation

First, you will need Ruby DBI and Ruby ODBC. To my knowledge the ADO DBD for DBI is no longer supported. The installation below is not a comprehensive walk thru on how to get all the required moving parts like FreeTDS installed and/or configured. It will also assume gem installations of both the dependent libraries and the adapter itself.

It should be noted that this version of the adapter was developed using both the ancient 0.0.23 version of DBI up to the current stable release of 0.4.0. Because later versions of DBI will be changing many things, IT IS HIGHLY RECOMMENDED that you max your install to version 0.4.0 which the examples below show. For the time being we are not supporting DBI versions higher than 0.4.0. The good news is that if you were using a very old DBI with ADO, technically this adapter will still work for you, but be warned your path is getting old and may not be supported for long.

$ gem install dbi --version 0.4.0
$ gem install dbd-odbc --version 0.2.4
$ gem install rails-sqlserver-2000-2005-adapter -s http://gems.github.com

From here: http://lambie.org/2008/02/28/connecting-to-an-mssql-database-from-ruby-on-ubuntu/

Firstly, update your ~/.profile to include the following:

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

Then reload your .profile, by logging out and in again.

Secondly, on Ubuntu 7.10 Server I needed to install some packages.

mlambie@ubuntu:~$ sudo aptitude install unixodbc unixodbc-dev freetds-dev sqsh tdsodbc 

With FreeTDS installed I could configure it like this:

mlambie@ubuntu:/etc/freetds$ cat freetds.conf
[ACUMENSERVER]
  host = 192.168.0.10
  port = 1433
  tds version = 7.0

The important thing here is ACUMENSERVER, which is the DSN that I’ll use when connecting to the database. The host, and port are self-explanatory, and it’s worth noting that I had to use 7.0 specifically as the tds version.

Testing FreeTDS is not too hard:

mlambie@ubuntu:~$ sqsh -S ACUMENSERVER -U username -P password
sqsh: Symbol `_XmStrings' has different size in shared object, consider re-linking
sqsh-2.1 Copyright (C) 1995-2001 Scott C. Gray
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
1> use acumen
2> go
1> select top 1 firstname, lastname from tblClients
2> go

[record returned]

(1 row affected)
1> quit

Next up it’s necessary to configure ODBC:

mlambie@ubuntu:/etc$ cat odbcinst.ini
[FreeTDS]
Description     = TDS driver (Sybase/MS SQL)
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
CPTimeout       =
CPReuse         =
FileUsage       = 1

mlambie@ubuntu:/etc$ cat odbc.ini
[ACUMENSERVER]
Driver          = FreeTDS
Description     = ODBC connection via FreeTDS
Trace           = No
Servername      = ACUMENSERVER
Database        = ACUMEN

I then tested the connection with isql:

mlambie@ubuntu:~$ isql -v ACUMENSERVER username password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> use ACUMEN
[][unixODBC][FreeTDS][SQL Server]Changed database context to 'Acumen'.
[ISQL]INFO: SQLExecute returned SQL_SUCCESS_WITH_INFO
SQLRowCount returns -1
SQL> select top 1 firstname from tblClients;

[record returned]

SQLRowCount returns 1
1 rows fetched
SQL> quit

OK, so we’ve got ODBC using FreeTDS to connect to a remote MSSQL server. All that’s left is to add Ruby into the mix.

mlambie@ubuntu:~$ sudo aptitude install libdbd-odbc-ruby

The last thing to test is that Ruby can use DBI and ODBC to hit the actual database, and that’s easy to test:

mlambie@ubuntu:~$ irb
irb(main):001:0> require "dbi" 
=> true
irb(main):002:0> dbh = DBI.connect('dbi:ODBC:ACUMENSERVER', 'username', 'password')
=> #<DBI::DatabaseHandle:0xb7ac57f8 @handle=#<DBI::DBD::ODBC::Database:0xb7ac5744
@handle=#<odbc::database:0xb7ac576c>, @attr={}>, @trace_output=#</odbc::database:0xb7ac576c><io:0xb7cbff54>,
@trace_mode=2>
irb(main):003:0> quit

And a more complete test (only with SQL SELECT, mind you):

#!/usr/bin/env ruby

require 'dbi'
db = DBI.connect('dbi:ODBC:ACUMENSERVER', 'username', 'password')
select = db.prepare('SELECT TOP 10 firstname FROM tblClients')
select.execute
while rec = select.fetch do
  puts rec.to_s
end
db.disconnect
</io:0xb7cbff54>

From here (to fix the odbc lib being in the wrong place):
http://ubuntuforums.org/showthread.php?t=433435&page=2

libtdsodbc.so
with freeTDS (freetds-dev, tdsodbc), you can either edit the path in the odbcinst.ini file for the [FreeTDS] driver section OR cp the /usr/lib/odbc/libtdsodbc.so into /usr/lib/libtdsodbc.so.

either way works when accessing mssql from the prompt

isql -v $dsn $user $passwd

i found this to be useful

http://www.unixodbc.org/doc/FreeTDS.html#Configuration

And then in the database.yml file:

development:
  adapter: sqlserver
  mode: odbc
  dsn: dsn_name
  username: my_username
  password: my_password

Can't connect to local MySQL server through socket. I want to connect to REMOTE db

So, the problem is in empty environment var ENV["HOST"].

When host, passed to ActiveRecord is nil or equl to string localhost - AR will try to connect to db using socket.

I have a custom ODBC driver residing on a remote machine, anyway I can share it with my local machine?

The OpenLink Multi-Tier ODBC-ODBC Bridge has been available for over 15 years and will enable the bridging between the Windows 7 client and Windows 2003 Server machine where the current ODBC Driver exists.

---

Related Topics