4

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Dave Long
  • 9,569
  • 14
  • 59
  • 89
  • As an aside, consider using [Sequel](http://sequel.jeremyevans.net) to give you an ORM layer. It works nicely with ODBC and removes the need to use DBM-specific queries making your code more portable. – the Tin Man Jun 22 '15 at 17:18
  • the Tin Man: That is, in my opinion, a totally irrelevant comment to my issue. My use case involved a couple of read only queries against the legacy database and does not necessitate the overhead of adding an ORM layer. – Dave Long Jun 22 '15 at 17:41
  • It's a suggestion trying to help. Take it or leave it. Your response is oddly defensive. – the Tin Man Jun 22 '15 at 17:53
  • the Tin Man: I'm not trying to be defensive in my response. My struggle is that, without knowing my use case, you suggested that I use Sequel. Had I been new to programming, I would probably haven taken that without question and added unnecessary overhead to my application. If you want, I can talk more on chat, or some other method. Any defensiveness comes from my struggle that the development community is forgetting that databases are very powerful and usable, even without ORM. – Dave Long Jun 22 '15 at 17:59
  • While also not answer I have had a lot of luck (after much trial and error with other gems) using [`rdbi`](https://github.com/RDBI/rdbi) and [`rdbi-driver-odbc`](https://github.com/semmons99/rdbi-driver-odbc) to make odbc connections to sql as well as even access (mdb) databases. This does not have an ORM and you can write manual queries fairly easily. In a few applications I have actually managed to take it as far as writing my own light-weight ORM's to facilitate basic functionality without the bloat of the community versions. – engineersmnky Jun 22 '15 at 19:05
  • engineersmnky: Thank you for the recommendation, but it looks like underneath, RDBI's ODBC driver is just built on the ODBC gem which is already failing for me. – Dave Long Jun 22 '15 at 19:09
  • While you are right that it sits on top of `ruby-odbc` they have made quite a few improvements. Mac is built on BSD but that does not mean that it is the same under the hood. Also have you tried actually supplying credentials? rather than just the dsn I have had this issue too – engineersmnky Jun 22 '15 at 19:22
  • engineersmnky: I just tried connected with the credentials with no luck. – Dave Long Jun 22 '15 at 19:23
  • I would recommend trying `rdbi` like I said I have been very successful with it and I have had issues with using `ruby-odbc` directly. Now I only use `rdbi` because it works. Might be a bit of additional overhead but its far less than adding an ORM like activerecord or Sequel and in my opinion a little more overhead for less aggravation is worth it. – engineersmnky Jun 22 '15 at 19:25
  • No luck with RDBI either. – Dave Long Jun 22 '15 at 19:36
  • This seems to have nothing to do with `ruby-odbc` then but rather the way you have the server configured. according to SQL/R `/etc/opt/sqlr2/odbc.dsn` file is very important as it configured the dsn names. I do not have very much experience with this but I would walk though the doc steps from [Marxmeier](http://www.marxmeier.com/sqlr_getting_started.html) Sorry I can't be more help – engineersmnky Jun 22 '15 at 20:21
  • Engineersmnky: thank you for the help. I found one issue that I'm waiting on Marxmeier support for, but I'm confused as to why the connection would work in isql, but not Ruby. – Dave Long Jun 23 '15 at 01:49
  • Can you share the `strace_output.txt` after running `strace -o strace_output.txt ruby -e "require 'rdbi-driver-odbc'; RDBI.connect :ODBC, db: 'reservations'"`? – Eugene Petrov Jul 08 '15 at 02:35
  • http://eloquence.marxmeier.com/mlist/archive2k/2003/11/0002.html – Mircea Jul 08 '15 at 19:05
  • Eloquence DSN connections _only_ works if $HOME/.odbc.ini or $ODBCINI exists and contains appropiate DSN entries. That is, if ODBCINI is not set or only system-wide /etc/odbc.ini is created, Eloquence DSN connections doesnt works although DSN connections name is read from /etc/odbc.ini – Mircea Jul 08 '15 at 19:06
  • isql probably has a fallback and does something that the ruby gem does not do / does not know how to do. – Mircea Jul 08 '15 at 19:07

1 Answers1

2

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Dave Long
  • 9,569
  • 14
  • 59
  • 89