Ruby Sequel (SQlite3) 'SELECT' error

Question

I'm using a ruby helper to extract data from an SQLite3 database using sequel ORM. Here is the code:

#!/usr/bin/env ruby
# encoding: UTF-8

require_relative 'greekdate'
require 'sequel'

module Pharmacy
    class Open
        def initialize
            db = Sequel.sqlite("../lib/drama.sql")
            @address = db[:addressbook]
            @ov = db[:overnight]
            @grday = GRDay::MDate.new
        end                         

        def display
            data = @address.first(id: get_id()) # ERROR HERE
            p data[:name]
        end

        private
        def get_id
            mod_date = @grday.get[:mod_date]
            @ov.each do |entry|
                return entry[:pharmacy_id] if entry[:date].to_s == mod_date
            end
        end
    end
end

I am calling the display method. The error I'm getting is:

/Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sqlite3-1.3.7/lib/sqlite3/database.rb:91:in `initialize': SQLite3::SQLException: only a single result allowed for a SELECT that is part of an expression (Sequel::DatabaseError)
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sqlite3-1.3.7/lib/sqlite3/database.rb:91:in `new'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sqlite3-1.3.7/lib/sqlite3/database.rb:91:in `prepare'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sqlite3-1.3.7/lib/sqlite3/database.rb:263:in `query'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sequel-4.0.0/lib/sequel/adapters/sqlite.rb:179:in `block (2 levels) in _execute'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sequel-4.0.0/lib/sequel/database/logging.rb:33:in `log_yield'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sequel-4.0.0/lib/sequel/adapters/sqlite.rb:179:in `block in _execute'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sequel-4.0.0/lib/sequel/database/connecting.rb:229:in `block in synchronize'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sequel-4.0.0/lib/sequel/connection_pool/threaded.rb:104:in `hold'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sequel-4.0.0/lib/sequel/database/connecting.rb:229:in `synchronize'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sequel-4.0.0/lib/sequel/adapters/sqlite.rb:172:in `_execute'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sequel-4.0.0/lib/sequel/adapters/sqlite.rb:122:in `execute'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sequel-4.0.0/lib/sequel/dataset/actions.rb:794:in `execute'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sequel-4.0.0/lib/sequel/adapters/sqlite.rb:356:in `fetch_rows'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sequel-4.0.0/lib/sequel/dataset/actions.rb:144:in `each'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sequel-4.0.0/lib/sequel/dataset/actions.rb:584:in `single_record'
    from /Users/atma/.rvm/gems/ruby-1.9.3-p194/gems/sequel-4.0.0/lib/sequel/dataset/actions.rb:202:in `first'
    from test.rb:17:in `display'
    from test.rb:32:in `<main>'

I'm not sure why this. At first I thought that I should somehow close and re-open the connection, but the error says "only a single result allowed for a SELECT that is part of an expression" and I was getting a single result until yesterday, I see no reason for me to get 2 results since I'm invoking table.first(id: X) .

Any ideas and explanations would be more than welcome :-)

Thanks for your time,

PA

You should put your code in the question, not linked. It will stay on SO longer. As to your question, I believe that if no match is found in get_id(), the method will return the query db[:overnight] (because that is the behaviour of each). This is likely a bug either way, and could be causing your problem — Neil Slater
– Neil Slater, Commented Jul 5, 2013 at 8:08
Usually you would add a .where clause to the query, rather than implement the search in Ruby. Could you please include your code in the question? — Neil Slater
– Neil Slater, Commented Jul 5, 2013 at 8:17
Hi Neil, I put the code in the question you are right though. I was using 2 SQLite3 files one for testing and one for production, I mixed them up ending with empty records. I'll just put an exception to notiy be that the DB is empty (no id returned) and I'll be fine! Thanks! — patm
– patm, Commented Jul 5, 2013 at 8:20

Jeremy Evans · Accepted Answer · 2013-07-05 16:04:19Z

1

Your get_id method returns @ov if no entry in @ov has the given date, as that is the return value of @ov.each. You probably want something like:

def display
    if id = get_id
      data = @address.first(id: id)
      p data[:name]
    end
end

private
def get_id
    mod_date = @grday.get[:mod_date]
    @ov.each do |entry|
        return entry[:pharmacy_id] if entry[:date].to_s == mod_date
    end
    nil
end

Note that display is a poor choice for a method name, since Object#display is already defined by ruby.

answered Jul 5, 2013 at 16:04

Jeremy Evans

12.2k31 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

patm Over a year ago

True, I switched that overnight_display to avoid silly conflicts. I added an rescue clause to get a graceful error and understandable error when no entry is matched (which should never happen anyway).

Collectives™ on Stack Overflow

Ruby Sequel (SQlite3) 'SELECT' error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related