How to read a big Sql Server result streamed?

[wuarmin]

Hey,

I need to read a big Sql Server (tinytds) result streamed (not all results in memory). I cannot use paged_each with strategy :offset, because the data-result's order is ambiguous. I need a cursor-like approach.

sort|step
10 | 0
10 | 0
10 | 0
10 | 0
10 | 1
10 | 1
11 | 0

At tinytds doc I saw, the following, which would solve my issue:

result = client.execute("SELECT * FROM [super_big_table]")

client.sqlsent?   # = true
client.canceled?  # = false

result.each do |row|
  # Assume we break after 20 rows with 80 still pending.
  break if row["id"] > 20
end

client.sqlsent?   # = true
client.canceled?  # = false

With OCI Driver it would be something like that:

  cursor = con.exec(sql)
  rows = []
  cursor.fetch_hash do |row|
    rows << row
    if (rows.count == rows_per_fetch)
      yield rows
      rows = []
    end
  end
  yield rows unless rows.empty?

How to solve this with Sequel. Or how to access the underlying driver through Sequel?

Thanks and best regards

[jeremyevans]

You can use Database#synchronize to access the underlying connection:

DB.synchronize do |client|
  result = client.execute("SELECT * FROM [super_big_table]")
  # ...
end

[wuarmin]

Thank you! I solved it like that:

DB.synchronize do |client|
  result = client.execute("SELECT * FROM [super_big_table]")

  rows = []
  count = 0
  result.each do |row|
    rows << row
    count += 1
    if (count >= step_size)
      yield rows
      rows = []
      count = 0
    end
  end
  yield rows unless rows.empty?
end