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