missing row_set with mutiple result sets

[SteveAlexander]

I'm making a query of two stored procedures.

result = client.execute(queries)
result_sets = result.each
puts JSON.pretty_generate result_sets

This gives me back the results I expect, no matter whether the second stored procedure yields a single row or more than one row.

The data structure is:

[
  [
    { row SP1 1 },
    { row SP1 2 }
  ],
  [
    { row SP2 1 },
    { row SP2 2 },
  ]
]

I can also use each with blocks.

result = client.execute(queries)
puts 'rows first time through'
result.each do |row|
  puts JSON.pretty_generate row
end
puts 'rows second time through'
result.each do |row_set|
  puts 'new row_set: #{row_set.length}'
  row_set.each do |row|
    puts JSON.pretty_generate row
  end
end

This gives me output like:

rows first time through
{ row SP1 1 },
{ row SP1 2 },
{ row SP2 1 },
{ row SP2 2 }

rows second time through
new row_set: 2
{ row SP1 1 }
{ row SP1 2 }

new row_set: 2 { row SP2 1 } { row SP2 2 }

This is all as I would expect.

BUT: When the second stored procedure yields a single row, I get the following.

rows first time through
{ row SP1 1 },
{ row SP1 2 },
{ row SP2 1 }

rows second time through
new row_set: 2
{ row SP1 1 }
{ row SP1 2 }

The row_set for SP2 is missing.

What I would expect is:

rows first time through
{ row SP1 1 },
{ row SP1 2 },
{ row SP2 1 }

rows second time through
new row_set: 2
{ row SP1 1 }
{ row SP1 2 }
new row_set: 1
{ row SP2 1 }

If I try the same, with the second stored procedure containing zero rows, I get the following:

rows first time through
{ row SP1 1 },
{ row SP1 2 }

rows second time through

What I would expect is:

rows first time through
{ row SP1 1 },
{ row SP1 2 }

rows second time through
new row_set: 2
{ row SP1 1 }
{ row SP1 2 }
new row_set: 0

tiny_tds (1.0.4) using TDS version 7.3 ruby 2.3.1p112 Version: freetds v1.00

[metaskills]

Thanks, the second example before your "BUT:..." does not seem to be inline with what I expect in the docs readme (https://github.com/rails-sqlserver/tiny_tds#readme). Please see the section that starts with "The result object can handle multiple result sets...".

Also, is your client using the columns cache or not?

[SteveAlexander]

The default is to not use the columns cache, I think.

To be sure, I've changed each

result.each do

to

result.each(cache_rows: true) do

The output is the same.

(edited — got my true/false the wrong way around in the code example!)

[SteveAlexander]

In the docs:

result = client.execute(sql)

result.each do |rowset|
  # First time data loading, yields each row from each set.
  # 1st: {"id"=>11}
  # 2nd: {"bigint"=>-9223372036854775807}
  # 3rd: {"bigint"=>9223372036854775806}
end

result.each do |rowset|
  # Second time over (if columns cached), yields each set.
  # 1st: [{"id"=>11}]
  # 2nd: [{"bigint"=>-9223372036854775807}, {"bigint"=>9223372036854775806}]
end

Let's say I switch around the order of the two queries in the example. It would become like this:

result = client.execute(sql)

result.each do |rowset|
  # First time data loading, yields each row from each set.
  # 1st: {"bigint"=>-9223372036854775807}
  # 2nd: {"bigint"=>9223372036854775806}
  # 3rd: {"id"=>11}
end

result.each do |rowset|
  # Second time over (if columns cached), yields each set.
  # 1st: [{"bigint"=>-9223372036854775807}, {"bigint"=>9223372036854775806}]
  # 2nd: [{"id"=>11}]
end

I think my code below does the same thing, but the line equivalent to

# 2nd: [{"id"=>11}]

never appears.

In my code:

result = client.execute(queries)

result.each do |row|
  puts row
  # 1st: Stored procedure 1, row 1 : {'key1a': 'value1a', 'key2a': 'value2a', etc.}
  # 2nd: Stored procedure 1, row 2: {'key1b': 'value1b', 'key2b': 'value2b', etc.}
  # 3rd: Stored procedure 2, row 1: {'key1c': 'value1c', 'key2c': 'value2c', etc.}
end

result.each do |row_set|
  puts row_set
  # 1st: [ {'key1a': 'value1a', 'key2a': 'value2a', etc.}, {'key1b': 'value1b', 'key2b': 'value2b', etc.} ]
end

If I reverse the order of the stored procedures in my query, I get this instead:

result = client.execute(queries)

result.each do |row|
  puts row
  # 1st: Stored procedure 2, row 1: {'key1c': 'value1c', 'key2c': 'value2c', etc.}
  # 2nd: Stored procedure 1, row 1 : {'key1a': 'value1a', 'key2a': 'value2a', etc.}
  # 3rd: Stored procedure 1, row 2: {'key1b': 'value1b', 'key2b': 'value2b', etc.}
end

result.each do |row_set|
  puts row_set
  # 1st: [ {'key1c': 'value1c', 'key2c': 'value2c', etc.} ]
  # 2nd: [ {'key1a': 'value1a', 'key2a': 'value2a', etc.}, {'key1b': 'value1b', 'key2b': 'value2b', etc.} ]
end

The only difference is that I have changed the order of stored procedures.

In the first case, where it does not do what I expect, and what I believe the docs indicate, there is a stored procedure that yields 2 results followed by a stored procedure that yields 1 result.

In the second case, where it does what I expect, and what I believe the docs indicate, there is a stored procedure that yields 1 result followed by a stored procedure that yields 2 results.