Ruby ODBC with remote database

Question

I am working on an application that connects to a legacy database, Eloquence, through ODBC and SQL/R. I set up my server with UnixODBC and setup the drivers and datasources as follows:

File /etc/odbcinst.ini

[SQLR]
Description=SQLR for Elqouence
Driver=/opt/sqlr/lib/libsqlrodbc.so
Driver64=/opt/sqlr/lib64/libsqlrodbc64.so
FileUsage = 1

File /etc/odbc.ini

[reservations]
Description = SQLR datasource for RES database
Driver = SQLR
Database = res
Servername = eloq-dev
Port = 8003
UserName = sqlrodbc

I confirmed that I can connect to the datasource by running isql reservations and I ran a couple of queries to make sure. No issues. Then I connected my Ruby code up to the database using the ODBC gem and the following code:

require 'rdbi-driver-odbc'
RDBI.connect :ODBC, db: "reservations"

Which outputs the following error:

Unable to connect to host.
Host 127.0.0.1, Service sqlrodbc
errno 111: Connection refused
ODBC::Error: 08001 (3047) [unixODBC][Marxmeier][SQL/R ODBC Client]connection failure

I'm concerned that it's using 127.0.0.1 as the host even though the eloq-dev hostname is set in file /etc/hosts to a different address. I'm also concerned that isql works, but the ODBC gem doesn't.

Additionally, when I use the tcpdump command, the only output related to my connection is this:

tcpdump -i lo

tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on lo, link-type EN10MB (Ethernet), capture size 65535 bytes
18:38:39.688264 IP localhost.50447 > localhost.mcreport: Flags [S], seq 3355035364, win 43690, options [mss 65495,sackOK,TS val 1655798115 ecr 0,nop,wscale 7], length 0
18:38:39.688280 IP localhost.mcreport > localhost.50447: Flags [R.], seq 0, ack 3355035365, win 0, length 0

No packets are going out over the network at all.

I've also changed my code to use RDBI instead of Ruby-ODBC, but I have the same issue.

Answer 1

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.