src/database/sql/example_test.go - go - Git at Google

 // Copyright 2013 The Go Authors. All rights reserved.
 // Use of this source code is governed by a BSD-style
 // license that can be found in the LICENSE file.

 package sql_test

 import (
 	"context"
 	"database/sql"
 	"fmt"
 	"log"
 	"strings"
 	"time"
 )

 var (
 	ctx context.Context
 	db  *sql.DB
 )

 func ExampleDB_QueryContext() {
 	age := 27
 	rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)
 	if err != nil {
 		log.Fatal(err)
 	}
 	defer rows.Close()
 	names := make([]string, 0)

 	for rows.Next() {
 		var name string
 		if err := rows.Scan(&name); err != nil {
 			// Check for a scan error.
 			// Query rows will be closed with defer.
 			log.Fatal(err)
 		}
 		names = append(names, name)
 	}
 	// If the database is being written to ensure to check for Close
 	// errors that may be returned from the driver. The query may
 	// encounter an auto-commit error and be forced to rollback changes.
 	rerr := rows.Close()
 	if rerr != nil {
 		log.Fatal(err)
 	}

 	// Rows.Err will report the last error encountered by Rows.Scan.
 	if err := rows.Err(); err != nil {
 		log.Fatal(err)
 	}
 	fmt.Printf("%s are %d years old", strings.Join(names, ", "), age)
 }

 func ExampleDB_QueryRowContext() {
 	id := 123
 	var username string
 	var created time.Time
 	err := db.QueryRowContext(ctx, "SELECT username, created_at FROM users WHERE id=?", id).Scan(&username, &created)
 	switch {
 	case err == sql.ErrNoRows:
 		log.Printf("no user with id %d\n", id)
 	case err != nil:
 		log.Fatalf("query error: %v\n", err)
 	default:
 		log.Printf("username is %q, account created on %s\n", username, created)
 	}
 }

 func ExampleDB_ExecContext() {
 	id := 47
 	result, err := db.ExecContext(ctx, "UPDATE balances SET balance = balance + 10 WHERE user_id = ?", id)
 	if err != nil {
 		log.Fatal(err)
 	}
 	rows, err := result.RowsAffected()
 	if err != nil {
 		log.Fatal(err)
 	}
 	if rows != 1 {
 		log.Fatalf("expected to affect 1 row, affected %d", rows)
 	}
 }

 func ExampleDB_Query_multipleResultSets() {
 	age := 27
 	q := `
 create temp table uid (id bigint); -- Create temp table for queries.
 insert into uid
 select id from users where age < ?; -- Populate temp table.

 -- First result set.
 select
 	users.id, name
 from
 	users
 	join uid on users.id = uid.id
 ;

 -- Second result set.
 select
 	ur.user, ur.role
 from
 	user_roles as ur
 	join uid on uid.id = ur.user
 ;
 	`
 	rows, err := db.Query(q, age)
 	if err != nil {
 		log.Fatal(err)
 	}
 	defer rows.Close()

 	for rows.Next() {
 		var (
 			id   int64
 			name string
 		)
 		if err := rows.Scan(&id, &name); err != nil {
 			log.Fatal(err)
 		}
 		log.Printf("id %d name is %s\n", id, name)
 	}
 	if !rows.NextResultSet() {
 		log.Fatalf("expected more result sets: %v", rows.Err())
 	}
 	var roleMap = map[int64]string{
 		1: "user",
 		2: "admin",
 		3: "gopher",
 	}
 	for rows.Next() {
 		var (
 			id   int64
 			role int64
 		)
 		if err := rows.Scan(&id, &role); err != nil {
 			log.Fatal(err)
 		}
 		log.Printf("id %d has role %s\n", id, roleMap[role])
 	}
 	if err := rows.Err(); err != nil {
 		log.Fatal(err)
 	}
 }

 func ExampleDB_PingContext() {
 	// Ping and PingContext may be used to determine if communication with
 	// the database server is still possible.
 	//
 	// When used in a command line application Ping may be used to establish
 	// that further queries are possible; that the provided DSN is valid.
 	//
 	// When used in long running service Ping may be part of the health
 	// checking system.

 	ctx, cancel := context.WithTimeout(ctx, 1*time.Second)
 	defer cancel()

 	status := "up"
 	if err := db.PingContext(ctx); err != nil {
 		status = "down"
 	}
 	log.Println(status)
 }

 func ExampleDB_Prepare() {
 	projects := []struct {
 		mascot  string
 		release int
 	}{
 		{"tux", 1991},
 		{"duke", 1996},
 		{"gopher", 2009},
 		{"moby dock", 2013},
 	}

 	stmt, err := db.Prepare("INSERT INTO projects(id, mascot, release, category) VALUES( ?, ?, ?, ? )")
 	if err != nil {
 		log.Fatal(err)
 	}
 	defer stmt.Close() // Prepared statements take up server resources and should be closed after use.

 	for id, project := range projects {
 		if _, err := stmt.Exec(id+1, project.mascot, project.release, "open source"); err != nil {
 			log.Fatal(err)
 		}
 	}
 }

 func ExampleTx_Prepare() {
 	projects := []struct {
 		mascot  string
 		release int
 	}{
 		{"tux", 1991},
 		{"duke", 1996},
 		{"gopher", 2009},
 		{"moby dock", 2013},
 	}

 	tx, err := db.Begin()
 	if err != nil {
 		log.Fatal(err)
 	}
 	defer tx.Rollback() // The rollback will be ignored if the tx has been committed later in the function.

 	stmt, err := tx.Prepare("INSERT INTO projects(id, mascot, release, category) VALUES( ?, ?, ?, ? )")
 	if err != nil {
 		log.Fatal(err)
 	}
 	defer stmt.Close() // Prepared statements take up server resources and should be closed after use.

 	for id, project := range projects {
 		if _, err := stmt.Exec(id+1, project.mascot, project.release, "open source"); err != nil {
 			log.Fatal(err)
 		}
 	}
 	if err := tx.Commit(); err != nil {
 		log.Fatal(err)
 	}
 }

 func ExampleConn_BeginTx() {
 	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
 	if err != nil {
 		log.Fatal(err)
 	}
 	id := 37
 	_, execErr := tx.Exec(`UPDATE users SET status = ? WHERE id = ?`, "paid", id)
 	if execErr != nil {
 		_ = tx.Rollback()
 		log.Fatal(execErr)
 	}
 	if err := tx.Commit(); err != nil {
 		log.Fatal(err)
 	}
 }

 func ExampleConn_ExecContext() {
 	// A *DB is a pool of connections. Call Conn to reserve a connection for
 	// exclusive use.
 	conn, err := db.Conn(ctx)
 	if err != nil {
 		log.Fatal(err)
 	}
 	defer conn.Close() // Return the connection to the pool.
 	id := 41
 	result, err := conn.ExecContext(ctx, `UPDATE balances SET balance = balance + 10 WHERE user_id = ?;`, id)
 	if err != nil {
 		log.Fatal(err)
 	}
 	rows, err := result.RowsAffected()
 	if err != nil {
 		log.Fatal(err)
 	}
 	if rows != 1 {
 		log.Fatalf("expected single row affected, got %d rows affected", rows)
 	}
 }

 func ExampleTx_ExecContext() {
 	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
 	if err != nil {
 		log.Fatal(err)
 	}
 	id := 37
 	_, execErr := tx.ExecContext(ctx, "UPDATE users SET status = ? WHERE id = ?", "paid", id)
 	if execErr != nil {
 		if rollbackErr := tx.Rollback(); rollbackErr != nil {
 			log.Fatalf("update failed: %v, unable to rollback: %v\n", execErr, rollbackErr)
 		}
 		log.Fatalf("update failed: %v", execErr)
 	}
 	if err := tx.Commit(); err != nil {
 		log.Fatal(err)
 	}
 }

 func ExampleTx_Rollback() {
 	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
 	if err != nil {
 		log.Fatal(err)
 	}
 	id := 53
 	_, err = tx.ExecContext(ctx, "UPDATE drivers SET status = ? WHERE id = ?;", "assigned", id)
 	if err != nil {
 		if rollbackErr := tx.Rollback(); rollbackErr != nil {
 			log.Fatalf("update drivers: unable to rollback: %v", rollbackErr)
 		}
 		log.Fatal(err)
 	}
 	_, err = tx.ExecContext(ctx, "UPDATE pickups SET driver_id = $1;", id)
 	if err != nil {
 		if rollbackErr := tx.Rollback(); rollbackErr != nil {
 			log.Fatalf("update failed: %v, unable to back: %v", err, rollbackErr)
 		}
 		log.Fatal(err)
 	}
 	if err := tx.Commit(); err != nil {
 		log.Fatal(err)
 	}
 }

 func ExampleStmt() {
 	// In normal use, create one Stmt when your process starts.
 	stmt, err := db.PrepareContext(ctx, "SELECT username FROM users WHERE id = ?")
 	if err != nil {
 		log.Fatal(err)
 	}
 	defer stmt.Close()

 	// Then reuse it each time you need to issue the query.
 	id := 43
 	var username string
 	err = stmt.QueryRowContext(ctx, id).Scan(&username)
 	switch {
 	case err == sql.ErrNoRows:
 		log.Fatalf("no user with id %d", id)
 	case err != nil:
 		log.Fatal(err)
 	default:
 		log.Printf("username is %s\n", username)
 	}
 }

 func ExampleStmt_QueryRowContext() {
 	// In normal use, create one Stmt when your process starts.
 	stmt, err := db.PrepareContext(ctx, "SELECT username FROM users WHERE id = ?")
 	if err != nil {
 		log.Fatal(err)
 	}
 	defer stmt.Close()

 	// Then reuse it each time you need to issue the query.
 	id := 43
 	var username string
 	err = stmt.QueryRowContext(ctx, id).Scan(&username)
 	switch {
 	case err == sql.ErrNoRows:
 		log.Fatalf("no user with id %d", id)
 	case err != nil:
 		log.Fatal(err)
 	default:
 		log.Printf("username is %s\n", username)
 	}
 }

 func ExampleRows() {
 	age := 27
 	rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)
 	if err != nil {
 		log.Fatal(err)
 	}
 	defer rows.Close()

 	names := make([]string, 0)
 	for rows.Next() {
 		var name string
 		if err := rows.Scan(&name); err != nil {
 			log.Fatal(err)
 		}
 		names = append(names, name)
 	}
 	// Check for errors from iterating over rows.
 	if err := rows.Err(); err != nil {
 		log.Fatal(err)
 	}
 	log.Printf("%s are %d years old", strings.Join(names, ", "), age)
 }
	// Copyright 2013 The Go Authors. All rights reserved.
	// Use of this source code is governed by a BSD-style
	// license that can be found in the LICENSE file.

	package sql_test

	import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"strings"
	"time"
	)

	var (
	ctx context.Context
	db *sql.DB
	)

	func ExampleDB_QueryContext() {
	age := 27
	rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)
	if err != nil {
	log.Fatal(err)
	}
	defer rows.Close()
	names := make([]string, 0)

	for rows.Next() {
	var name string
	if err := rows.Scan(&name); err != nil {
	// Check for a scan error.
	// Query rows will be closed with defer.
	log.Fatal(err)
	}
	names = append(names, name)
	}
	// If the database is being written to ensure to check for Close
	// errors that may be returned from the driver. The query may
	// encounter an auto-commit error and be forced to rollback changes.
	rerr := rows.Close()
	if rerr != nil {
	log.Fatal(err)
	}

	// Rows.Err will report the last error encountered by Rows.Scan.
	if err := rows.Err(); err != nil {
	log.Fatal(err)
	}
	fmt.Printf("%s are %d years old", strings.Join(names, ", "), age)
	}

	func ExampleDB_QueryRowContext() {
	id := 123
	var username string
	var created time.Time
	err := db.QueryRowContext(ctx, "SELECT username, created_at FROM users WHERE id=?", id).Scan(&username, &created)
	switch {
	case err == sql.ErrNoRows:
	log.Printf("no user with id %d\n", id)
	case err != nil:
	log.Fatalf("query error: %v\n", err)
	default:
	log.Printf("username is %q, account created on %s\n", username, created)
	}
	}

	func ExampleDB_ExecContext() {
	id := 47
	result, err := db.ExecContext(ctx, "UPDATE balances SET balance = balance + 10 WHERE user_id = ?", id)
	if err != nil {
	log.Fatal(err)
	}
	rows, err := result.RowsAffected()
	if err != nil {
	log.Fatal(err)
	}
	if rows != 1 {
	log.Fatalf("expected to affect 1 row, affected %d", rows)
	}
	}

	func ExampleDB_Query_multipleResultSets() {
	age := 27
	q := `
	create temp table uid (id bigint); -- Create temp table for queries.
	insert into uid
	select id from users where age < ?; -- Populate temp table.

	-- First result set.
	select
	users.id, name
	from
	users
	join uid on users.id = uid.id
	;

	-- Second result set.
	select
	ur.user, ur.role
	from
	user_roles as ur
	join uid on uid.id = ur.user
	;
	`
	rows, err := db.Query(q, age)
	if err != nil {
	log.Fatal(err)
	}
	defer rows.Close()

	for rows.Next() {
	var (
	id int64
	name string
	)
	if err := rows.Scan(&id, &name); err != nil {
	log.Fatal(err)
	}
	log.Printf("id %d name is %s\n", id, name)
	}
	if !rows.NextResultSet() {
	log.Fatalf("expected more result sets: %v", rows.Err())
	}
	var roleMap = map[int64]string{
	1: "user",
	2: "admin",
	3: "gopher",
	}
	for rows.Next() {
	var (
	id int64
	role int64
	)
	if err := rows.Scan(&id, &role); err != nil {
	log.Fatal(err)
	}
	log.Printf("id %d has role %s\n", id, roleMap[role])
	}
	if err := rows.Err(); err != nil {
	log.Fatal(err)
	}
	}

	func ExampleDB_PingContext() {
	// Ping and PingContext may be used to determine if communication with
	// the database server is still possible.
	//
	// When used in a command line application Ping may be used to establish
	// that further queries are possible; that the provided DSN is valid.
	//
	// When used in long running service Ping may be part of the health
	// checking system.

	ctx, cancel := context.WithTimeout(ctx, 1*time.Second)
	defer cancel()

	status := "up"
	if err := db.PingContext(ctx); err != nil {
	status = "down"
	}
	log.Println(status)
	}

	func ExampleDB_Prepare() {
	projects := []struct {
	mascot string
	release int
	}{
	{"tux", 1991},
	{"duke", 1996},
	{"gopher", 2009},
	{"moby dock", 2013},
	}

	stmt, err := db.Prepare("INSERT INTO projects(id, mascot, release, category) VALUES( ?, ?, ?, ? )")
	if err != nil {
	log.Fatal(err)
	}
	defer stmt.Close() // Prepared statements take up server resources and should be closed after use.

	for id, project := range projects {
	if _, err := stmt.Exec(id+1, project.mascot, project.release, "open source"); err != nil {
	log.Fatal(err)
	}
	}
	}

	func ExampleTx_Prepare() {
	projects := []struct {
	mascot string
	release int
	}{
	{"tux", 1991},
	{"duke", 1996},
	{"gopher", 2009},
	{"moby dock", 2013},
	}

	tx, err := db.Begin()
	if err != nil {
	log.Fatal(err)
	}
	defer tx.Rollback() // The rollback will be ignored if the tx has been committed later in the function.

	stmt, err := tx.Prepare("INSERT INTO projects(id, mascot, release, category) VALUES( ?, ?, ?, ? )")
	if err != nil {
	log.Fatal(err)
	}
	defer stmt.Close() // Prepared statements take up server resources and should be closed after use.

	for id, project := range projects {
	if _, err := stmt.Exec(id+1, project.mascot, project.release, "open source"); err != nil {
	log.Fatal(err)
	}
	}
	if err := tx.Commit(); err != nil {
	log.Fatal(err)
	}
	}

	func ExampleConn_BeginTx() {
	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
	if err != nil {
	log.Fatal(err)
	}
	id := 37
	_, execErr := tx.Exec(`UPDATE users SET status = ? WHERE id = ?`, "paid", id)
	if execErr != nil {
	_ = tx.Rollback()
	log.Fatal(execErr)
	}
	if err := tx.Commit(); err != nil {
	log.Fatal(err)
	}
	}

	func ExampleConn_ExecContext() {
	// A *DB is a pool of connections. Call Conn to reserve a connection for
	// exclusive use.
	conn, err := db.Conn(ctx)
	if err != nil {
	log.Fatal(err)
	}
	defer conn.Close() // Return the connection to the pool.
	id := 41
	result, err := conn.ExecContext(ctx, `UPDATE balances SET balance = balance + 10 WHERE user_id = ?;`, id)
	if err != nil {
	log.Fatal(err)
	}
	rows, err := result.RowsAffected()
	if err != nil {
	log.Fatal(err)
	}
	if rows != 1 {
	log.Fatalf("expected single row affected, got %d rows affected", rows)
	}
	}

	func ExampleTx_ExecContext() {
	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
	if err != nil {
	log.Fatal(err)
	}
	id := 37
	_, execErr := tx.ExecContext(ctx, "UPDATE users SET status = ? WHERE id = ?", "paid", id)
	if execErr != nil {
	if rollbackErr := tx.Rollback(); rollbackErr != nil {
	log.Fatalf("update failed: %v, unable to rollback: %v\n", execErr, rollbackErr)
	}
	log.Fatalf("update failed: %v", execErr)
	}
	if err := tx.Commit(); err != nil {
	log.Fatal(err)
	}
	}

	func ExampleTx_Rollback() {
	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
	if err != nil {
	log.Fatal(err)
	}
	id := 53
	_, err = tx.ExecContext(ctx, "UPDATE drivers SET status = ? WHERE id = ?;", "assigned", id)
	if err != nil {
	if rollbackErr := tx.Rollback(); rollbackErr != nil {
	log.Fatalf("update drivers: unable to rollback: %v", rollbackErr)
	}
	log.Fatal(err)
	}
	_, err = tx.ExecContext(ctx, "UPDATE pickups SET driver_id = $1;", id)
	if err != nil {
	if rollbackErr := tx.Rollback(); rollbackErr != nil {
	log.Fatalf("update failed: %v, unable to back: %v", err, rollbackErr)
	}
	log.Fatal(err)
	}
	if err := tx.Commit(); err != nil {
	log.Fatal(err)
	}
	}

	func ExampleStmt() {
	// In normal use, create one Stmt when your process starts.
	stmt, err := db.PrepareContext(ctx, "SELECT username FROM users WHERE id = ?")
	if err != nil {
	log.Fatal(err)
	}
	defer stmt.Close()

	// Then reuse it each time you need to issue the query.
	id := 43
	var username string
	err = stmt.QueryRowContext(ctx, id).Scan(&username)
	switch {
	case err == sql.ErrNoRows:
	log.Fatalf("no user with id %d", id)
	case err != nil:
	log.Fatal(err)
	default:
	log.Printf("username is %s\n", username)
	}
	}

	func ExampleStmt_QueryRowContext() {
	// In normal use, create one Stmt when your process starts.
	stmt, err := db.PrepareContext(ctx, "SELECT username FROM users WHERE id = ?")
	if err != nil {
	log.Fatal(err)
	}
	defer stmt.Close()

	// Then reuse it each time you need to issue the query.
	id := 43
	var username string
	err = stmt.QueryRowContext(ctx, id).Scan(&username)
	switch {
	case err == sql.ErrNoRows:
	log.Fatalf("no user with id %d", id)
	case err != nil:
	log.Fatal(err)
	default:
	log.Printf("username is %s\n", username)
	}
	}

	func ExampleRows() {
	age := 27
	rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)
	if err != nil {
	log.Fatal(err)
	}
	defer rows.Close()

	names := make([]string, 0)
	for rows.Next() {
	var name string
	if err := rows.Scan(&name); err != nil {
	log.Fatal(err)
	}
	names = append(names, name)
	}
	// Check for errors from iterating over rows.
	if err := rows.Err(); err != nil {
	log.Fatal(err)
	}
	log.Printf("%s are %d years old", strings.Join(names, ", "), age)
	}