postgresql - Need the ID of the current row inserted within a transaction -
within transaction i'm inserting row.
how can access , return id of inserted row. can see in code below(see under comment // return last inserted id.) tried use lastinsertedid() function, gives me error back.
btw, i'm using postgres.
what missing here? thx!
/** * creates order , returns id. */ func createorder(w http.responsewriter, r *http.request) (orderid int) { // begin. tx, err := db.begin() if err != nil { log.fatal(err) } // db query. sqlquery := `insert order_customer (customer_id) values ($1) returning id` // prepare. stmt, err := tx.prepare(sqlquery) if err != nil { log.fatal(err) return } // defer close. defer stmt.close() customeremail := validatesession(r) id := getidfromcustomer(customeremail) order := order{} order.customerid = id // exec. ret, err := stmt.exec(order.customerid) // rollback. if err != nil { tx.rollback() e := errors.new(err.error()) msg.warning = e.error() tpl.executetemplate(w, "menu.gohtml", msg) return } // return last inserted id. lastid, err := ret.lastinsertid() if err != nil { orderid = 0 } else { orderid = int(lastid) } // commit. tx.commit() return orderid } // createorder here working solution now, further improvement welcomed.
/** * creates order , returns id. */ func createorder(w http.responsewriter, r *http.request) (orderid int) { // begin. tx, err := db.begin() if err != nil { log.fatal(err) } // db query. sqlquery := `insert order_customer (customer_id) values ($1) returning id` // prepare. stmt, err := tx.prepare(sqlquery) if err != nil { log.fatal(err) return } // defer close. defer stmt.close() customeremail := validatesession(r) id := getidfromcustomer(customeremail) order := order{} order.customerid = id // exec. _, err = stmt.exec(order.customerid) // rollback. if err != nil { tx.rollback() e := errors.new(err.error()) msg.warning = e.error() tpl.executetemplate(w, "menu.gohtml", msg) return } // return last inserted id. //lastid, err := ret.lastinsertid() err = stmt.queryrow(order.customerid).scan(&orderid) if err != nil { orderid = 0 } // commit. tx.commit() return orderid } // createorder
this happens because postgresql driver using go doesn't supports lastinsertedid() function. didn't driver using have had issue working github.com/lib/pq.
the answer use queryrow insted of exec in original example. make sure using returning id on query , treat if select.
here example (i didn't test , might missing idea):
func createorder(w http.responsewriter, r *http.request) (orderid int) { // begin. tx, err := db.begin() if err != nil { log.fatal(err) } // db query. sqlquery := `insert order_customer (customer_id) values ($1) returning id` // prepare. stmt, err := tx.prepare(sqlquery) if err != nil { log.fatal(err) return } // defer close. defer stmt.close() customeremail := validatesession(r) id := getidfromcustomer(customeremail) order := order{} order.customerid = id // exec. var orderid int // or whatever type using err := stmt.queryrow(order.customerid).scan(&orderid) // rollback. if err != nil { //if goes wrong set orderid 0 in original code orderid = 0 tx.rollback() e := errors.new(err.error()) msg.warning = e.error() tpl.executetemplate(w, "menu.gohtml", msg) return } // commit. tx.commit() return orderid } // createorder
Comments
Post a Comment