go: database/sql: add support for returning multiple result sets

Many databases support returning multiple result sets. RDBMS that support this include MySQL, Postgresql, MS SQL Server, Oracle.

An example query that demonstrates this is:

select * from app.Users;
select * from app.Dancers;

The above would be expected to return two data sets, each with their own arity.

The above example is simple to see the effects, but is not motivating. A more motivating example usually includes some degree of procedural logic where multiple arities should be preserved and returned separately. Example (1) Select data to form a matrix on the client:

    // Get Sample data rows.
    // Get SampleResultValue rows.
    // Get distinct list of Locus used data rows.
    sql.WriteString(`
    declare @Sample table (ID bigint);
    declare @Locus table (ID bigint);

    insert into @Sample
    select s.ID
    from
        data.Sample s
        join data.OrganizationLOB ol on ol.ID = s.OrganizationLOB
    where
        s.ID in (select ID from @SampleSelect)
        and s.Deleted = 0
    ;

    select
        s.ID,
        ls.SampleIdentifier,
        ls.ExternalIdentifier,
        s.Outcome,
        s.DateCollected
    from
        data.Sample s
        join datav.LabelSample ls on ls.Sample = s.ID
    where
        s.ID in (select ID from @Sample)

    select
        srv.Sample, srv.Value, srv.Interpretation, ResultValueType = rvt.ID
    from
        data.SampleResultValue srv
        join data.ResultValueType rvt on rvt.ID = srv.ResultValueType
    where
        srv.Sample in (select ID from @Sample)
    ;

    select
        rvt.ID, Name = l.Name + ' ' + rft.Name
    from
        data.ResultValueType rvt
        join enum.ResultFieldType rft on rft.ID = rvt.FieldType
        join data.Locus l on l.ID = rvt.Locus
    where
        rvt.ID in (
            select distinct
                srv.ResultValueType
            from
                data.SampleResultValue srv
            where
                srv.Sample in (select ID from @Sample)
        )
    order by
        rvt.SortOrder
    ;
    `)

Example (2) make an update, but return the updated data to the client so it can update its UI.

    res, err := Query(db.Conn, &rdb.Command{
        Converter: conv,
        Sql: `
insert into data.SamplePanel (
    Sample, Panel,
    DateDue,
    OrderGroup,
    Deleted, TimeCreated, AccountCreated, TimeUpdated, AccountUpdated
    )
output ` + listColumns(db, "inserted.", "data.SamplePanel") + `
select
    s.ID, sgp.Panel,
    data.WorkDayAdd(s.DateReceived, data.OrganizationPanelTAT(@Today, ol.Organization, sgp.Panel)),
    1,
    0, @Now, @Account, @Now, @Account
from
    data.SampleGroupPanel sgp
    join data.Sample s on s.SampleGroup = sgp.SampleGroup
    join data.OrganizationLOB ol on ol.ID = s.OrganizationLOB
where
    s.ID = @Sample
;

--See also PostUpdate
insert into data.SampleLocus (
    Sample, Locus,
    TimeCreated, AccountCreated, TimeUpdated, AccountUpdated
    )
output ` + listColumns(db, "inserted.", "data.SampleLocus") + `
select distinct
    @Sample, pl.Locus,
    @Now, @Account, @Now, @Account
from
    data.PanelLocus pl
    join data.SamplePanel sp on sp.Panel = pl.Panel
where
    sp.Sample = @Sample
;

select
    s.ID, s.Price
from
    datav.Sample s
where
    s.ID = @Sample
;
    `,
    },
        rdb.Param{Name: "Sample", Type: rdb.Integer, Value: sample},
        rdb.Param{Name: "Account", Type: rdb.Integer, Value: userInfo.ID},
        rdb.Param{Name: "Now", Type: rdb.Time, Value: time.Now().UTC()},
        rdb.Param{Name: "Today", Type: rdb.TypeDate, Value: time.Now()},
    )

About this issue

Original URL
State: closed
Created 9 years ago
Comments: 30 (16 by maintainers)

Most upvoted comments

CL https://golang.org/cl/30592 mentions this issue.

gopherbot on Oct 6, 2016

I would expect any error to cause NextResultSet() to return false, so yeah.

if !rows.NextResultSet() {
  return errors.wrap(rows.Err(), "expected another result set")
}

freeformz on Aug 12, 2016

An alternative, more minimal option maybe?

// NextResultSet returns the next result set from the query
// NextResultSet always returns a non-nil value.
// Errors are deferred until *Rows.Scan method is called.
func (r *Rows) NextResultSet() *Rows

Example:

func DoQuery() error {
  rows, err := db.Query("query with 2 result sets")
  if err != nil {
    return err
  }
  defer rows.Close()
  // Handle result set 1
  for rows.Next() {
    err := rows.Scan(...)
  }
  if err := rows.Err(); err != nil {
    return err
  }
  rows = rows.NextResultSet()
  defer rows.Close()
  for rows.Next() {
    err := rows.Scan(...)
    if err == sql.ErrNoRows {
      //Application error
    }
  }
  return rows.Err()

This is not perfect, and I’m not even sure it’s possible, but it looks like it could be as Rows owns the connection.

freeformz on Aug 11, 2016