Fetch mysql prepared statement as an hash array

Question

I am struggling with ruby ​​mysql gem and preparing statements.
I want to end up with the same as with each_hash in the result, but it is not supported when preparing statements. So I came with this terrible mess.

  stmt = @db.prepare("SELECT mat_id, name, qty FROM materials WHERE mat_id = ? ") #those 3 lines hurt my eyes res = stmt.execute(params[:id]) mat_id, name, qty = res.bind_result(Integer, String, Integer).fetch @material = [mat_id: mat_id, name: name, qty: qty] 

There should be a better way to get results and get an array of hashes.

The best mysql stone might be the correct answer. ORM NOT.

Answer 1

When I see the comments, I will still send the Sequel link as an answer:

http://sequel.rubyforge.org/

You do not have to use the model part of Sequel. In fact, the docs have the entire section on SQL addicts:

http://sequel.rubyforge.org/rdoc/files/doc/sql_rdoc.html

Request example:

 DB.fetch("SELECT * FROM albums WHERE name LIKE ?", 'A%') do |row| puts row[:name] end 

Answer 2

According to http://tmtm.org/en/mysql/ruby/, the results have a "each_hash" method, but statements do not. What a pain in the ass ...

 #A proxy for the statement class class Stmt def each_hash fields = @target.result_metadata.fetch_fields.map do |f| f.name.to_sym end @target.execute.each do |x| hash = {} fields.zip(x).each do |pair| hash[pair[0]] = pair[1] end yield hash end end def initialize(target) @target = target end def method_missing(name, *args, &block) @target.send(name, *args, &block) end end 

Now you can do this:

 Stmt.new(@db.prepare(...).execute(...)).each_hash do |x| puts x end 

and you can scroll each line as a hash.

I still have not tested this for several performances

Answer 3

Oneliner!

 Hash[stmt.result_metadata.fetch_fields.map(&:name).zip( stmt.fetch )] 

Or more reliable

 row = stmt.fetch Hash[stmt.result_metadata.fetch_fields.map(&:name).zip( row )] if row