CODEWITHSUNDEEP
rubydbi
user1134160
user1134160

Reputation:

How to use Ruby DBI's 'select_all' vs 'execute-fetch/each-finish'

Here is my sample code for using DBI:

dbh = DBI.connect("DBI:Mysql:host=#{server};database=mysql", user, pass)
rows = dbh.select_all("SHOW TABLES")

The rows printed look like:

[["user"], ["user"], ["user"], ["user"], ["user"], ["user"], ["user"], ["user"],
 ["user"], ["user"], ["user"], ["user"], ["user"], ["user"], ["user"], ["user"],
 ["user"]]

This is printing the last table in a MySQL database, but the total number of records is proper.

If I do this using execute-fetch/each-finish sequence, something like:

sth = dbh.execute("SHOW TABLES")
sth.each do |row|
  rows << row[0]
end
sth.finish

It gives me proper results like:

["columns_priv", "db", "func", "help_category", "help_keyword", "help_relation",
 "help_topic", "host", "proc", "procs_priv", "tables_priv", "time_zone", "time_z
one_leap_second", "time_zone_name", "time_zone_transition", "time_zone_transitio
n_type", "user"]

Upvotes: 3

Views: 2324

Answers (2)

DNNX
DNNX

Reputation: 6255

I guess that dbh.select_all returns an instance of Enumerator, which yields the same row on each iteration. See this pseudocode to understand what I mean:

def select_all(query)
  db_row = Row.new
  reader = @connection.execute_reader(query)
  Enumerator.new do |yielder|
    until reader.end?
      db_row.populate_from(reader)
      yielder.yield db_row
      reader.next!
    end
  end
end

Thus, if you're using select_all without block, an Enumerator will be returned, which basically yields the same db_row object.

This is just a guess, but, I believe the truth is nearby.

The source code fetch method definition says that I was wrong, because @row is duplicated on each iteration. Well, probably the error is somewhere up the stack.

Upvotes: 1

dsz
dsz

Reputation: 5212

The same is also happenning when I query an MS SQL DB using DBI.connect("DBI:ODBC:Driver={SQL Server};...")

My work around was to forcably convert the DBI::Row to an array:

sth = dbh.execute "..."
begin
  return sth.map{ |row| row.to_a }
ensure
  sth.finish
end

I'll bet $1,000 that the issue is to do with buffer reuse - a possible 'performance' enhancement that has had underirable side effects!

Upvotes: 2

Related Questions