Support for multiple result sets

[ekstrom]

It would be useful to have support for multiple result sets.

Example code:

query = "SELECT 1 AS 'first_result_set'; SELECT 2 AS 'second_result_set';"
Tds.query!(pid, query, [])

Expected result:

[%Tds.Result{columns: ["first_result_set"], num_rows: 1, rows: [[1]]}, %Tds.Result{columns: ["second_result_set"], num_rows: 1, rows: [[2]]}]

Actual result:

%Tds.Result{columns: ["first_result_set"], num_rows: 1, rows: [[1]]}

[mjaric]

Last year I refactored decoder part, it reads and keeps all results until it must hadoff result to user code. Reason is that ecto expects only single result, so Tds.Protocol pulls out last result here.

Since ecto adapter does not use Tds.query/3 nor Tds.query/4 it is possible to pass additional flag in options that should tell internally Tds.Protocol module to rather return all results. For instance, here it could be something like:

  def query(pid, statement, params, opts \\ []) do
    query = %Query{statement: statement}
    opts = 
      opts
      |> Keyword.put_new(:parameters, params)
      |> Keyword.put(:resultset, true) # <-----   THE FLAG!

    case DBConnection.prepare_execute(pid, query, params, opts) do
      {:ok, _query, result} -> {:ok, result}
      {:error, err} -> {:error, err}
    end
  end

Then it could pack among all results additional info, like messages server returned (info, error ...), num_results, and result set count.

It is worth mentioning that results are in reverse order so they need to be Enum.reversed before they are handed to user code.

[mjaric]

One more note, since unit tests also expect single Tds.Result, all must be refactored to pass with this change

[josevalim]

Perhaps the best option is to introduce a separate function, such as query_multi or query_many or similar. So users can explicitly opt-in into it.

[mjaric]

@josevalim, @ekstrom , I think this is going to work for any multi batch script for MSSQL Server. It even follows inserts without output. We were lucky I refactored decoder last winter so this was easy to expose

[mjaric]

@ekstrom BTW, please point your fork to tds master and run test, but there shouldn't be anything broken

[ekstrom]

Tested it and it works as expected. Thanks for the good work 😄

[mjaric]

Released in v2.1.1.

Thank you!