CODEWITHSUNDEEP
sql-serverapigostructgo-gorm
Raja Ganji
Raja Ganji

Reputation: 101

Handle multiple resultsets from sql in GO lang using GORM

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.

Upvotes: 4

Views: 3720

Answers (3)

gstvg
gstvg

Reputation: 927

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
    }

}

Upvotes: 4

Raja Ganji
Raja Ganji

Reputation: 101

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.

Upvotes: 5

Anton
Anton

Reputation: 1956

You'll need to do it in two separate requests.

var dualtable DualTable
db.Raw("select * from table1").Find(&dualtable.table1)
db.Raw("select * from table2").Find(&dualtable.table2)

Updated:

You could also embed table1 and table2 in a struct and pass that struct to Scan:

type DualTable struct {
    Table1 //embedded
    Table2 //embedded
}
var dt []DualTable
db.Raw(/*you query here*/).Scan(&dt)

Upvotes: 2

Related Questions