Handle multiple resultsets from sql in GO lang using GORM

Question

The previous similar questions dates back to 2012 which had no solution so I had to ask afresh.

struct type DualTable{
  table1 []Table1
  table2 []Table2
  }

  struct type Table1{
  A string
  B string
  }

  struct type Table2{
  P string
  Q string
  }

  var dualtable []DualTable
  var table1 []Table1
  var table2 []Table2

  func main(){
  //trial 1 :failed as i get only table1 result
  db.Raw("select * from table1 select * from table2").Scan(&table1).Scan(&table2)

    //trial 2 :failed as i get only table2 result
  db.Raw("select * from table2 select * from table1").Scan(&table1).Scan(&table2)

  //trial 3 : failed as got nothing
  db.Raw("select * from table1 select * from table2").Scan(&dualtable)
}

As you can see what I'm trying to do.
I'm trying to get both tables results in the DualTable struct
But only the first query seems to run.

The actual code consists of extremely long structs and is confidential so I can't post it here.

Answer 1

I wanted to answer my own question as i did not find a solution here but somehow found the solution to this question,

Until 2016 You could'nt do it.

But this situation has been highlighted to GO developers as this might occur mostly in the time of executiong stored procedures that emit multiple result sets.

ALL DETAILS HERE: https://go-review.googlesource.com/c/go/+/30592/

Short summary: The query first gives you the first result set and you can fetch the result from it. Once your done with it. Use

result.NextResultSet()

This will simply switch from fist query result set to the next result set. Then you can fetch the result from second query.

Also, GORM does not have this functionality as far as my knowledge goes. So i skipped using GORM ORM.

Answer 2

GORM v1 doesn't handle this natively, but you can get the query result underlying *sql.Rows, call db.ScanRows with table 1, call rows.NextResultSet and db.ScanRows with table 2

var table1 []Table1
var table2 []Table2

func main()

    rows, err := db.Raw("select * from table1; select * from table2").Rows()

    err = db.ScanRows(rows, &table1)

    if rows.NextResultSet() {
        err = db.ScanRows(rows, &table2)
    } else {
        //only one result set was returned, handle this case
    }

}