1// Copyright 2013 The Go Authors. All rights reserved.
2// Use of this source code is governed by a BSD-style
3// license that can be found in the LICENSE file.
4
5package sql_test
6
7import (
8	"context"
9	"database/sql"
10	"fmt"
11	"log"
12	"strings"
13	"time"
14)
15
16var (
17	ctx context.Context
18	db  *sql.DB
19)
20
21func ExampleDB_QueryContext() {
22	age := 27
23	rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)
24	if err != nil {
25		log.Fatal(err)
26	}
27	defer rows.Close()
28	names := make([]string, 0)
29
30	for rows.Next() {
31		var name string
32		if err := rows.Scan(&name); err != nil {
33			// Check for a scan error.
34			// Query rows will be closed with defer.
35			log.Fatal(err)
36		}
37		names = append(names, name)
38	}
39	// If the database is being written to ensure to check for Close
40	// errors that may be returned from the driver. The query may
41	// encounter an auto-commit error and be forced to rollback changes.
42	rerr := rows.Close()
43	if rerr != nil {
44		log.Fatal(rerr)
45	}
46
47	// Rows.Err will report the last error encountered by Rows.Scan.
48	if err := rows.Err(); err != nil {
49		log.Fatal(err)
50	}
51	fmt.Printf("%s are %d years old", strings.Join(names, ", "), age)
52}
53
54func ExampleDB_QueryRowContext() {
55	id := 123
56	var username string
57	var created time.Time
58	err := db.QueryRowContext(ctx, "SELECT username, created_at FROM users WHERE id=?", id).Scan(&username, &created)
59	switch {
60	case err == sql.ErrNoRows:
61		log.Printf("no user with id %d\n", id)
62	case err != nil:
63		log.Fatalf("query error: %v\n", err)
64	default:
65		log.Printf("username is %q, account created on %s\n", username, created)
66	}
67}
68
69func ExampleDB_ExecContext() {
70	id := 47
71	result, err := db.ExecContext(ctx, "UPDATE balances SET balance = balance + 10 WHERE user_id = ?", id)
72	if err != nil {
73		log.Fatal(err)
74	}
75	rows, err := result.RowsAffected()
76	if err != nil {
77		log.Fatal(err)
78	}
79	if rows != 1 {
80		log.Fatalf("expected to affect 1 row, affected %d", rows)
81	}
82}
83
84func ExampleDB_Query_multipleResultSets() {
85	age := 27
86	q := `
87create temp table uid (id bigint); -- Create temp table for queries.
88insert into uid
89select id from users where age < ?; -- Populate temp table.
90
91-- First result set.
92select
93	users.id, name
94from
95	users
96	join uid on users.id = uid.id
97;
98
99-- Second result set.
100select
101	ur.user, ur.role
102from
103	user_roles as ur
104	join uid on uid.id = ur.user
105;
106	`
107	rows, err := db.Query(q, age)
108	if err != nil {
109		log.Fatal(err)
110	}
111	defer rows.Close()
112
113	for rows.Next() {
114		var (
115			id   int64
116			name string
117		)
118		if err := rows.Scan(&id, &name); err != nil {
119			log.Fatal(err)
120		}
121		log.Printf("id %d name is %s\n", id, name)
122	}
123	if !rows.NextResultSet() {
124		log.Fatalf("expected more result sets: %v", rows.Err())
125	}
126	var roleMap = map[int64]string{
127		1: "user",
128		2: "admin",
129		3: "gopher",
130	}
131	for rows.Next() {
132		var (
133			id   int64
134			role int64
135		)
136		if err := rows.Scan(&id, &role); err != nil {
137			log.Fatal(err)
138		}
139		log.Printf("id %d has role %s\n", id, roleMap[role])
140	}
141	if err := rows.Err(); err != nil {
142		log.Fatal(err)
143	}
144}
145
146func ExampleDB_PingContext() {
147	// Ping and PingContext may be used to determine if communication with
148	// the database server is still possible.
149	//
150	// When used in a command line application Ping may be used to establish
151	// that further queries are possible; that the provided DSN is valid.
152	//
153	// When used in long running service Ping may be part of the health
154	// checking system.
155
156	ctx, cancel := context.WithTimeout(ctx, 1*time.Second)
157	defer cancel()
158
159	status := "up"
160	if err := db.PingContext(ctx); err != nil {
161		status = "down"
162	}
163	log.Println(status)
164}
165
166func ExampleDB_Prepare() {
167	projects := []struct {
168		mascot  string
169		release int
170	}{
171		{"tux", 1991},
172		{"duke", 1996},
173		{"gopher", 2009},
174		{"moby dock", 2013},
175	}
176
177	stmt, err := db.Prepare("INSERT INTO projects(id, mascot, release, category) VALUES( ?, ?, ?, ? )")
178	if err != nil {
179		log.Fatal(err)
180	}
181	defer stmt.Close() // Prepared statements take up server resources and should be closed after use.
182
183	for id, project := range projects {
184		if _, err := stmt.Exec(id+1, project.mascot, project.release, "open source"); err != nil {
185			log.Fatal(err)
186		}
187	}
188}
189
190func ExampleTx_Prepare() {
191	projects := []struct {
192		mascot  string
193		release int
194	}{
195		{"tux", 1991},
196		{"duke", 1996},
197		{"gopher", 2009},
198		{"moby dock", 2013},
199	}
200
201	tx, err := db.Begin()
202	if err != nil {
203		log.Fatal(err)
204	}
205	defer tx.Rollback() // The rollback will be ignored if the tx has been committed later in the function.
206
207	stmt, err := tx.Prepare("INSERT INTO projects(id, mascot, release, category) VALUES( ?, ?, ?, ? )")
208	if err != nil {
209		log.Fatal(err)
210	}
211	defer stmt.Close() // Prepared statements take up server resources and should be closed after use.
212
213	for id, project := range projects {
214		if _, err := stmt.Exec(id+1, project.mascot, project.release, "open source"); err != nil {
215			log.Fatal(err)
216		}
217	}
218	if err := tx.Commit(); err != nil {
219		log.Fatal(err)
220	}
221}
222
223func ExampleDB_BeginTx() {
224	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
225	if err != nil {
226		log.Fatal(err)
227	}
228	id := 37
229	_, execErr := tx.Exec(`UPDATE users SET status = ? WHERE id = ?`, "paid", id)
230	if execErr != nil {
231		_ = tx.Rollback()
232		log.Fatal(execErr)
233	}
234	if err := tx.Commit(); err != nil {
235		log.Fatal(err)
236	}
237}
238
239func ExampleConn_ExecContext() {
240	// A *DB is a pool of connections. Call Conn to reserve a connection for
241	// exclusive use.
242	conn, err := db.Conn(ctx)
243	if err != nil {
244		log.Fatal(err)
245	}
246	defer conn.Close() // Return the connection to the pool.
247	id := 41
248	result, err := conn.ExecContext(ctx, `UPDATE balances SET balance = balance + 10 WHERE user_id = ?;`, id)
249	if err != nil {
250		log.Fatal(err)
251	}
252	rows, err := result.RowsAffected()
253	if err != nil {
254		log.Fatal(err)
255	}
256	if rows != 1 {
257		log.Fatalf("expected single row affected, got %d rows affected", rows)
258	}
259}
260
261func ExampleTx_ExecContext() {
262	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
263	if err != nil {
264		log.Fatal(err)
265	}
266	id := 37
267	_, execErr := tx.ExecContext(ctx, "UPDATE users SET status = ? WHERE id = ?", "paid", id)
268	if execErr != nil {
269		if rollbackErr := tx.Rollback(); rollbackErr != nil {
270			log.Fatalf("update failed: %v, unable to rollback: %v\n", execErr, rollbackErr)
271		}
272		log.Fatalf("update failed: %v", execErr)
273	}
274	if err := tx.Commit(); err != nil {
275		log.Fatal(err)
276	}
277}
278
279func ExampleTx_Rollback() {
280	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
281	if err != nil {
282		log.Fatal(err)
283	}
284	id := 53
285	_, err = tx.ExecContext(ctx, "UPDATE drivers SET status = ? WHERE id = ?;", "assigned", id)
286	if err != nil {
287		if rollbackErr := tx.Rollback(); rollbackErr != nil {
288			log.Fatalf("update drivers: unable to rollback: %v", rollbackErr)
289		}
290		log.Fatal(err)
291	}
292	_, err = tx.ExecContext(ctx, "UPDATE pickups SET driver_id = $1;", id)
293	if err != nil {
294		if rollbackErr := tx.Rollback(); rollbackErr != nil {
295			log.Fatalf("update failed: %v, unable to back: %v", err, rollbackErr)
296		}
297		log.Fatal(err)
298	}
299	if err := tx.Commit(); err != nil {
300		log.Fatal(err)
301	}
302}
303
304func ExampleStmt() {
305	// In normal use, create one Stmt when your process starts.
306	stmt, err := db.PrepareContext(ctx, "SELECT username FROM users WHERE id = ?")
307	if err != nil {
308		log.Fatal(err)
309	}
310	defer stmt.Close()
311
312	// Then reuse it each time you need to issue the query.
313	id := 43
314	var username string
315	err = stmt.QueryRowContext(ctx, id).Scan(&username)
316	switch {
317	case err == sql.ErrNoRows:
318		log.Fatalf("no user with id %d", id)
319	case err != nil:
320		log.Fatal(err)
321	default:
322		log.Printf("username is %s\n", username)
323	}
324}
325
326func ExampleStmt_QueryRowContext() {
327	// In normal use, create one Stmt when your process starts.
328	stmt, err := db.PrepareContext(ctx, "SELECT username FROM users WHERE id = ?")
329	if err != nil {
330		log.Fatal(err)
331	}
332	defer stmt.Close()
333
334	// Then reuse it each time you need to issue the query.
335	id := 43
336	var username string
337	err = stmt.QueryRowContext(ctx, id).Scan(&username)
338	switch {
339	case err == sql.ErrNoRows:
340		log.Fatalf("no user with id %d", id)
341	case err != nil:
342		log.Fatal(err)
343	default:
344		log.Printf("username is %s\n", username)
345	}
346}
347
348func ExampleRows() {
349	age := 27
350	rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)
351	if err != nil {
352		log.Fatal(err)
353	}
354	defer rows.Close()
355
356	names := make([]string, 0)
357	for rows.Next() {
358		var name string
359		if err := rows.Scan(&name); err != nil {
360			log.Fatal(err)
361		}
362		names = append(names, name)
363	}
364	// Check for errors from iterating over rows.
365	if err := rows.Err(); err != nil {
366		log.Fatal(err)
367	}
368	log.Printf("%s are %d years old", strings.Join(names, ", "), age)
369}
370