1// +build go1.10 2 3package mssql_test 4 5import ( 6 "database/sql" 7 "flag" 8 "fmt" 9 "log" 10) 11 12// This example shows the usage of Connector type 13func ExampleLastInsertId() { 14 flag.Parse() 15 16 if *debug { 17 fmt.Printf(" password:%s\n", *password) 18 fmt.Printf(" port:%d\n", *port) 19 fmt.Printf(" server:%s\n", *server) 20 fmt.Printf(" user:%s\n", *user) 21 } 22 23 connString := makeConnURL().String() 24 if *debug { 25 fmt.Printf(" connString:%s\n", connString) 26 } 27 28 db, err := sql.Open("sqlserver", connString) 29 if err != nil { 30 log.Fatal("Open connection failed:", err.Error()) 31 } 32 defer db.Close() 33 34 // Create table 35 _, err = db.Exec("create table foo (bar int identity, baz int unique);") 36 if err != nil { 37 log.Fatal(err) 38 } 39 defer db.Exec("if object_id('foo', 'U') is not null drop table foo;") 40 41 // Attempt to retrieve scope identity using LastInsertId 42 res, err := db.Exec("insert into foo (baz) values (1)") 43 if err != nil { 44 log.Fatal(err) 45 } 46 n, err := res.LastInsertId() 47 if err != nil { 48 log.Print(err) 49 // Gets error: LastInsertId is not supported. Please use the OUTPUT clause or add `select ID = convert(bigint, SCOPE_IDENTITY())` to the end of your query. 50 } 51 log.Printf("LastInsertId: %d\n", n) 52 53 // Retrieve scope identity by adding 'select ID = convert(bigint, SCOPE_IDENTITY())' to the end of the query 54 rows, err := db.Query("insert into foo (baz) values (10); select ID = convert(bigint, SCOPE_IDENTITY())") 55 if err != nil { 56 log.Fatal(err) 57 } 58 var lastInsertId1 int64 59 for rows.Next() { 60 rows.Scan(&lastInsertId1) 61 log.Printf("LastInsertId from SCOPE_IDENTITY(): %d\n", lastInsertId1) 62 } 63 64 // Retrieve scope identity by 'output inserted`` 65 var lastInsertId2 int64 66 err = db.QueryRow("insert into foo (baz) output inserted.bar values (100)").Scan(&lastInsertId2) 67 if err != nil { 68 log.Fatal(err) 69 } 70 log.Printf("LastInsertId from output inserted: %d\n", lastInsertId2) 71} 72