Creating select_one_hash() and select_all_hash() methods for Ruby DBI
2011-11-08 21:04:43
In using Ruby's DBI, I was looking for a way to return the results as a hash, rather than an array, to help me manage the result.
fetch_hash() was useful, in place of fetch()
But then I discovered select_one() and select_all() that are great substitutes for execute(), fetch() and all the associated code.
Unfortunately, there isn't a similar alternative for fetch_hash() - well not one that I have found - I must admit that I have not read all of the documentation and Ruby documentation isn't always complete or up-to-date. As a developer, I understand the pain of having to agonise over explaining the intricacies of something that you built to 'just work'.
I have consulted the documentation at http://ruby-dbi.rubyforge.org/rdoc/index.html, shuffled the code into new methods and come up with something that I believe (but cannot guarantee will work). It's not fully tested, but make of it, what you will.
As a word of caution, there is no guarantee that this won't break future versions of the DBI, especially if those method names are used; so you should test it after every update and you may also want to assign unique method names; e.g. YourName_select_one_hash
class DBI::DatabaseHandle
def select_one_hash(stmt, *bindvars)
raise InterfaceError, "Database connection was already closed!" if @handle.nil?
row = nil
execute(stmt, *bindvars) do |sth|
row = sth.fetch_hash
end
row
end
def select_all_hash(stmt, *bindvars, &p)
raise InterfaceError, "Database connection was already closed!" if @handle.nil?
rows = nil
execute(stmt, *bindvars) do |sth|
if block_given?
sth.each(&p)
else
rows = sth.fetch_all_hash
end
end
return rows
end
end
class DBI::StatementHandle
def fetch_all_hash
raise InterfaceError, "Statement was already closed!" if @handle.nil?
raise InterfaceError, "Statement must first be executed" unless @fetchable
cols = column_names
fetched_rows = []
begin
while row = fetch_hash do
fetched_rows.push(row)
end
rescue Exception
end
@handle.cancel
@fetchable = false
return fetched_rows
end
end
fetch_hash() was useful, in place of fetch()
But then I discovered select_one() and select_all() that are great substitutes for execute(), fetch() and all the associated code.
Unfortunately, there isn't a similar alternative for fetch_hash() - well not one that I have found - I must admit that I have not read all of the documentation and Ruby documentation isn't always complete or up-to-date. As a developer, I understand the pain of having to agonise over explaining the intricacies of something that you built to 'just work'.
I have consulted the documentation at http://ruby-dbi.rubyforge.org/rdoc/index.html, shuffled the code into new methods and come up with something that I believe (but cannot guarantee will work). It's not fully tested, but make of it, what you will.
As a word of caution, there is no guarantee that this won't break future versions of the DBI, especially if those method names are used; so you should test it after every update and you may also want to assign unique method names; e.g. YourName_select_one_hash
class DBI::DatabaseHandle
def select_one_hash(stmt, *bindvars)
raise InterfaceError, "Database connection was already closed!" if @handle.nil?
row = nil
execute(stmt, *bindvars) do |sth|
row = sth.fetch_hash
end
row
end
def select_all_hash(stmt, *bindvars, &p)
raise InterfaceError, "Database connection was already closed!" if @handle.nil?
rows = nil
execute(stmt, *bindvars) do |sth|
if block_given?
sth.each(&p)
else
rows = sth.fetch_all_hash
end
end
return rows
end
end
class DBI::StatementHandle
def fetch_all_hash
raise InterfaceError, "Statement was already closed!" if @handle.nil?
raise InterfaceError, "Statement must first be executed" unless @fetchable
cols = column_names
fetched_rows = []
begin
while row = fetch_hash do
fetched_rows.push(row)
end
rescue Exception
end
@handle.cancel
@fetchable = false
return fetched_rows
end
end