CODEWITHSUNDEEP
mysqlrubymysql2sequel
user2490003
user2490003

Reputation: 11920

Retrieving multiple datasets using the Sequel gem

I'm using the Sequel gem in the most basic manner possible - connecting to a local database and extracting the results.

The one catch is that my code relies on me being able to execute multiple query statements at once (splitting them up is not an option).

I am

Below is my script. 

require 'sequel'

conn = Sequel.connect(:adapter => 'mysql2', :database=>'my_test_db', :user => 'some_user', :password => 'xxxx', :host => 'localhost', :flags => ::Mysql2::Client::MULTI_STATEMENTS)

# res will be a Sequel::Mysql2::Dataset object
res = conn["select * from table_1; select * from table_2;"]

I can easily enough get the results of the first query (selecting from table_1) by simply doing res.all OR res.each{ |r| puts r }

My problem is, how do I get the next set of results (selecting from table_2) ? Is there some way to store/cache the existing result and move on to the next dataset?

Attempting to do run res.all again results in an error

Sequel::DatabaseDisconnectError: Mysql2::Error: Commands out of sync; you can't run this command now
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/adapters/mysql2.rb:77:in `query'
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/adapters/mysql2.rb:77:in `block in _execute'
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/database/logging.rb:33:in `log_yield'
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/adapters/mysql2.rb:77:in `_execute'
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/adapters/shared/mysql_prepared_statements.rb:34:in `block in execute'
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/database/connecting.rb:229:in `block in synchronize'
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/connection_pool/threaded.rb:104:in `hold'
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/database/connecting.rb:229:in `synchronize'
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/adapters/shared/mysql_prepared_statements.rb:34:in `execute'
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/dataset/actions.rb:795:in `execute'
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/adapters/mysql2.rb:181:in `execute'
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/adapters/mysql2.rb:152:in `fetch_rows'
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/dataset/actions.rb:143:in `each'
  from /Users/lefthandpisces/.rvm/gems/ruby-1.9.3-p448/gems/sequel-4.6.0/lib/sequel/dataset/actions.rb:46:in `all'
  from (irb):14
  from /Users/lefthandpisces/.rvm/rubies/ruby-1.9.3-p448/bin/irb:12:in `<main>'

Thanks!

Upvotes: 0

Views: 476

Answers (1)

Jeremy Evans
Jeremy Evans

Reputation: 12149

You can't do this with the mysql2 adapter. I believe the mysql adapter supports it, but even then it's a bad idea. Use separate datasets or a single dataset with a UNION.

Upvotes: 1

Related Questions