Connecting to Vertica from Ruby

Question

I'm trying to use Ruby to connect to Vertica analytics DB (from linux machine). I've verified that connection is possible using isql and unixodbc (DSN defined in /etc/odbc.ini, based more or less on this answer).

$ isql VerticaDSN
+--------------------------------------------+
| Connected!                                 |
|                                            |
| sql-statement                              |
| help [tablename]                           |
| quit                                       |
|                                            |
+--------------------------------------------+

I have also installed vertica, dbi and the ruby-odbc gems (using ruby 1.9.2). I'm failing to connect with all of these, and I'm wondering if I might be doing something wrong or missing a dependency.

using the vertica gem:

>> require 'vertica'
=> true
>> client = Vertica.connect({ :host => '10.0.0.15', :user => 'dbadmin', :password => 'pwd' })
Errno::ECONNREFUSED: Connection refused - connect(2)
    from /home/sa125/.rvm/gems/ruby-1.9.2-p290/gems/vertica-0.8.1/lib/vertica/connection:31:in `initialize'
    from /home/sa125/.rvm/gems/ruby-1.9.2-p290/gems/vertica-0.8.1/lib/vertica/connection:31:in `new'
    from /home/sa125/.rvm/gems/ruby-1.9.2-p290/gems/vertica-0.8.1/lib/vertica/connection:31:in `connection'
    from /home/sa125/.rvm/gems/ruby-1.9.2-p290/gems/vertica-0.8.1/lib/vertica/connection:24:in `initialize'
    from /home/sa125/.rvm/gems/ruby-1.9.2-p290/gems/vertica-0.8.1/lib/vertica.rb:11:in `new'
    from /home/sa125/.rvm/gems/ruby-1.9.2-p290/gems/vertica-0.8.1/lib/vertica.rb:11:in `connect'

using DBI / ODBC gems:

(using this)

>> require 'dbi'
=> true
>> require 'odbc'
=> true
>> ODBC.datasources
=> [#<ODBC::DSN:0x0000000284c6f8 @name="VerticaDSN", @descr="\"test database\"">]
>> dbc = ODBC.connect('VerticaDSN')
ArgumentError: string contains null byte
    from (irb):15:in `initialize'
    from (irb):15:in `connect'
    from (irb):15
>> dbh = DBI.connect('DBI:ODBC:VerticaDSN', 'dbadmin', 'pwd')
DBI::InterfaceError: Could not load driver (undefined method `driver_name' for ODBC:Module)
    from /home/sa125/.rvm/gems/ruby-1.9.2-p290/gems/dbi-0.4.5/lib/dbi.rb:318:in `rescue in load_driver'
    from /home/sa125/.rvm/gems/ruby-1.9.2-p290/gems/dbi-0.4.5/lib/dbi.rb:242:in `load_driver'
    from /home/sa125/.rvm/gems/ruby-1.9.2-p290/gems/dbi-0.4.5/lib/dbi.rb:160:in `_get_full_driver'
    from /home/sa125/.rvm/gems/ruby-1.9.2-p290/gems/dbi-0.4.5/lib/dbi.rb:145:in `connect'

Am I missing something here? thanks.

score 3 · Answer 1 · answered Feb 29 '12 at 05:32

Turns out upgrading the vertica gem to the --pre version solved the problem of using the first approach. The gem version available through rubygems is (at this time) 0.8.1, which didn't seem to work well. Based on the feedback I got from the gem's authors, I changed my Gemfile from this:

gem 'vertica'

to this:

gem 'vertica', '0.9.0.beta9' # current edge

Connecting to Vertica from Ruby

using the vertica gem:

using DBI / ODBC gems:

1 Answers1