0

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

  • using the mysql2 adapter
  • using the MULTIPLE_STATEMENTS flag to execute multiple statements

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!

user2490003
  • 10,706
  • 17
  • 79
  • 155

1 Answers1

1

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.

Jeremy Evans
  • 11,959
  • 27
  • 26
  • If I recall you are the creator of the Sequel gem, correct? a) Thanks for the development you put into it and b) any plans to incorporate this feature in the future? – user2490003 Jan 15 '14 at 23:19
  • No. It's basically impossible to support correctly given the Sequel API (what if table_1 and table_2 have different schemas?). – Jeremy Evans Jan 17 '14 at 16:08