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)
CL https://golang.org/cl/30592 mentions this issue.
I would expect any error to cause NextResultSet() to return false, so yeah.
An alternative, more minimal option maybe?
Example:
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.