0

Using PHP sql pdo driver i can send multiple statements into a single query and get multiple row sets.

$sql = "insert into foo () values () ; insert into bar () values (), ()";

$stmt = $dbh->prepare($sql);
$stmt->execute();

do {
    echo "row count: ".$stmt->rowCount()."\n";
} while ($stmt->nextRowset());

I am looking forward to achieve the same thing in Go.

I've tried below snippet to update a table. But when printing res.Next() and res.NextResultSet(), both are false while the rows are updated after my checking. So I cannot iterate the resultset to get the count.

tx, _ := conn.StartTransaction(ctx)
sqlstr := `update table1 set user_name = "d1" where user_id = 1;
update table1 set user_name = "d2" where user_id = 2;`
res, err := tx.Query(sqlstr)
if err != nil {
  fmt.Println(err.Error())
}
fmt.Println(res.Next())
fmt.Println(res.NextResultSet())
dlwlrma
  • 19
  • 2
  • 2
    It's in the documentation: https://pkg.go.dev/database/sql#example-DB.Query-MultipleResultSets -- if you have problems with the example please include the code you've tried and explain the problem you encountered. – mkopriva Aug 27 '21 at 06:40
  • @mkopriva, I've updated the question. thanks. – dlwlrma Aug 27 '21 at 17:15
  • 1
    I'm afraid for count it's not that simple. To get the count of rows affected by a query, you MUST use the `Exec` method and then invoke the [`RowsAffected`](https://pkg.go.dev/database/sql@go1.17#Result.RowsAffected) method on the result. However with `Exec` there is NO support for multi-set queries. So that means you'd have to `Exec` each query separately. The `Query` function is intended for retrieving actual rows, not count-of-affected-rows. – mkopriva Aug 27 '21 at 17:24
  • I have a lot of update stmt to execute. To improve the performance, I have to batch them together. I found this link: https://stackoverflow.com/q/50664648 seems to be able to do so. It has a `RETURNING *` but when I tried that, mysql says the query syntax is not valid. – dlwlrma Aug 27 '21 at 17:33
  • 1
    `RETURNING` is a postgresql thing. It is not implemented by mysql. – mkopriva Aug 27 '21 at 17:35

0 Answers0