Source file src/database/sql/example_test.go

Documentation: database/sql

		 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  
		 5  package sql_test
		 6  
		 7  import (
		 8  	"context"
		 9  	"database/sql"
		10  	"fmt"
		11  	"log"
		12  	"strings"
		13  	"time"
		14  )
		15  
		16  var (
		17  	ctx context.Context
		18  	db	*sql.DB
		19  )
		20  
		21  func 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  
		54  func 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  
		69  func 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  
		84  func ExampleDB_Query_multipleResultSets() {
		85  	age := 27
		86  	q := `
		87  create temp table uid (id bigint); -- Create temp table for queries.
		88  insert into uid
		89  select id from users where age < ?; -- Populate temp table.
		90  
		91  -- First result set.
		92  select
		93  	users.id, name
		94  from
		95  	users
		96  	join uid on users.id = uid.id
		97  ;
		98  
		99  -- Second result set.
	 100  select 
	 101  	ur.user, ur.role
	 102  from
	 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  
	 146  func 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  
	 166  func 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  
	 190  func 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  
	 223  func 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  
	 239  func 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  
	 261  func 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  
	 279  func 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  
	 304  func 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  
	 326  func 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  
	 348  func 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  

View as plain text