db.fetch_raw return array of arrays [columns, rows]?

[ccoffey]

If I execute a query with Sequel, is there a way to get back the raw results? Instead of an array of objects?

db = Sequel.connect(adapter: :snowflake, drvconnect: drvconnect)
result = db.fetch("SELECT EMAIL, NAME, ID FROM users LIMIT 3").all
=> [
  {:email=>"[email protected]", :name=>"Joe", :id=>1},
  {:email=>"[email protected]", :name=>"John", :id=>2},
  {:email=>"[email protected]", :name=>"Jane", :id=>3}
]

What I really want is:

[
  ["EMAIL", "NAME", "ID], 
  ["joe@test.com", "Joe", 1],
  ["john@test.com", "John", 2],
  ["jane@test.com", "Jane", 3],
]

Two problems with transforming the existing output to the output I actually want:

1. The column names have been converted to symbols (lost their origional formatting)
2. The order of the columns has been lost. If I do result.first.keys then I do get back [:email, :name, :id] however in general, I don't think there is any guarantee that these will match the order that the user requested them in (EMAIL, NAME, ID)?

I've read the docs but have not seen anything like db.fetch_raw which might return the results as I want them. Any suggestions?

[jeremyevans]

By default, Sequel yields a hash for each row (via each). With all, that's combined into an array (an array of hashes). If you want an array of arrays, you can use map([:email, :name, :id]).

[ccoffey]

But this would require me to know the symbol names and their correct order 😕

I think I am going to have to use the underlying odbc gem. The following is basically what I want:

db = ::ODBC::Database.new.drvconnect(drvconnect)

result = db.run("SELECT EMAIL, NAME, ID FROM users LIMIT 3")

columns = result.columns.keys
=> ["EMAIL", "NAME", "ID"]

rows = result.to_a
=> [
  ["[email protected]", "Joe", "1"],
  ["[email protected]", "John", "2"],
  ["[email protected]", "Jane", "3"]
]