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.
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.