...
Run Format

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 ctx = context.Background()
    17  var db *sql.DB
    18  
    19  func ExampleDB_QueryContext() {
    20  	age := 27
    21  	rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)
    22  	if err != nil {
    23  		log.Fatal(err)
    24  	}
    25  	defer rows.Close()
    26  	names := make([]string, 0)
    27  	for rows.Next() {
    28  		var name string
    29  		if err := rows.Scan(&name); err != nil {
    30  			log.Fatal(err)
    31  		}
    32  		names = append(names, name)
    33  	}
    34  	if err := rows.Err(); err != nil {
    35  		log.Fatal(err)
    36  	}
    37  	fmt.Printf("%s are %d years old", strings.Join(names, ", "), age)
    38  }
    39  
    40  func ExampleDB_QueryRowContext() {
    41  	id := 123
    42  	var username string
    43  	var created time.Time
    44  	err := db.QueryRowContext(ctx, "SELECT username, created_at FROM users WHERE id=?", id).Scan(&username, &created)
    45  	switch {
    46  	case err == sql.ErrNoRows:
    47  		log.Printf("No user with id %d", id)
    48  	case err != nil:
    49  		log.Fatal(err)
    50  	default:
    51  		fmt.Printf("Username is %s, account created on %s\n", username, created)
    52  	}
    53  }
    54  
    55  func ExampleDB_ExecContext() {
    56  	id := 47
    57  	result, err := db.ExecContext(ctx, "UPDATE balances SET balance = balance + 10 WHERE user_id = ?", id)
    58  	if err != nil {
    59  		log.Fatal(err)
    60  	}
    61  	rows, err := result.RowsAffected()
    62  	if err != nil {
    63  		log.Fatal(err)
    64  	}
    65  	if rows != 1 {
    66  		panic(err)
    67  	}
    68  }
    69  
    70  func ExampleDB_Query_multipleResultSets() {
    71  	age := 27
    72  	q := `
    73  create temp table uid (id bigint); -- Create temp table for queries.
    74  insert into uid
    75  select id from users where age < ?; -- Populate temp table.
    76  
    77  -- First result set.
    78  select
    79  	users.id, name
    80  from
    81  	users
    82  	join uid on users.id = uid.id
    83  ;
    84  
    85  -- Second result set.
    86  select 
    87  	ur.user, ur.role
    88  from
    89  	user_roles as ur
    90  	join uid on uid.id = ur.user
    91  ;
    92  	`
    93  	rows, err := db.Query(q, age)
    94  	if err != nil {
    95  		log.Fatal(err)
    96  	}
    97  	defer rows.Close()
    98  
    99  	for rows.Next() {
   100  		var (
   101  			id   int64
   102  			name string
   103  		)
   104  		if err := rows.Scan(&id, &name); err != nil {
   105  			log.Fatal(err)
   106  		}
   107  		fmt.Printf("id %d name is %s\n", id, name)
   108  	}
   109  	if !rows.NextResultSet() {
   110  		log.Fatal("expected more result sets", rows.Err())
   111  	}
   112  	var roleMap = map[int64]string{
   113  		1: "user",
   114  		2: "admin",
   115  		3: "gopher",
   116  	}
   117  	for rows.Next() {
   118  		var (
   119  			id   int64
   120  			role int64
   121  		)
   122  		if err := rows.Scan(&id, &role); err != nil {
   123  			log.Fatal(err)
   124  		}
   125  		fmt.Printf("id %d has role %s\n", id, roleMap[role])
   126  	}
   127  	if err := rows.Err(); err != nil {
   128  		log.Fatal(err)
   129  	}
   130  }
   131  
   132  func ExampleDB_PingContext() {
   133  	ctx, cancel := context.WithTimeout(ctx, 1*time.Second)
   134  	defer cancel()
   135  	if err := db.PingContext(ctx); err != nil {
   136  		log.Fatal(err)
   137  	}
   138  }
   139  
   140  func ExampleConn_BeginTx() {
   141  	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
   142  	if err != nil {
   143  		log.Fatal(err)
   144  	}
   145  	id := 37
   146  	_, execErr := tx.Exec(`UPDATE users SET status = ? WHERE id = ?`, "paid", id)
   147  	if execErr != nil {
   148  		_ = tx.Rollback()
   149  		log.Fatal(execErr)
   150  	}
   151  	if err := tx.Commit(); err != nil {
   152  		log.Fatal(err)
   153  	}
   154  }
   155  
   156  func ExampleConn_ExecContext() {
   157  	// A *DB is a pool of connections. Call Conn to reserve a connection for
   158  	// exclusive use.
   159  	conn, err := db.Conn(ctx)
   160  	if err != nil {
   161  		log.Fatal(err)
   162  	}
   163  	defer conn.Close() // Return the connection to the pool.
   164  	id := 41
   165  	result, err := conn.ExecContext(ctx, `UPDATE balances SET balance = balance + 10 WHERE user_id = ?`, id)
   166  	if err != nil {
   167  		log.Fatal(err)
   168  	}
   169  	rows, err := result.RowsAffected()
   170  	if err != nil {
   171  		log.Fatal(err)
   172  	}
   173  	if rows != 1 {
   174  		panic(err)
   175  	}
   176  }
   177  
   178  func ExampleTx_ExecContext() {
   179  	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
   180  	if err != nil {
   181  		log.Fatal(err)
   182  	}
   183  	id := 37
   184  	_, execErr := tx.ExecContext(ctx, "UPDATE users SET status = ? WHERE id = ?", "paid", id)
   185  	if execErr != nil {
   186  		if rollbackErr := tx.Rollback(); rollbackErr != nil {
   187  			log.Printf("Could not roll back: %v\n", rollbackErr)
   188  		}
   189  		log.Fatal(execErr)
   190  	}
   191  	if err := tx.Commit(); err != nil {
   192  		log.Fatal(err)
   193  	}
   194  }
   195  
   196  func ExampleTx_Rollback() {
   197  	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
   198  	if err != nil {
   199  		log.Fatal(err)
   200  	}
   201  	id := 53
   202  	_, err = tx.ExecContext(ctx, "UPDATE drivers SET status = ? WHERE id = ?", "assigned", id)
   203  	if err != nil {
   204  		if rollbackErr := tx.Rollback(); rollbackErr != nil {
   205  			log.Printf("Could not roll back: %v\n", rollbackErr)
   206  		}
   207  		log.Fatal(err)
   208  	}
   209  	_, err = tx.ExecContext(ctx, "UPDATE pickups SET driver_id = $1", id)
   210  	if err != nil {
   211  		if rollbackErr := tx.Rollback(); rollbackErr != nil {
   212  			log.Printf("Could not roll back: %v\n", rollbackErr)
   213  		}
   214  		log.Fatal(err)
   215  	}
   216  	if err := tx.Commit(); err != nil {
   217  		log.Fatal(err)
   218  	}
   219  }
   220  
   221  func ExampleStmt() {
   222  	// In normal use, create one Stmt when your process starts.
   223  	stmt, err := db.PrepareContext(ctx, "SELECT username FROM users WHERE id = ?")
   224  	if err != nil {
   225  		log.Fatal(err)
   226  	}
   227  	defer stmt.Close()
   228  	// Then reuse it each time you need to issue the query.
   229  	id := 43
   230  	var username string
   231  	err = stmt.QueryRowContext(ctx, id).Scan(&username)
   232  	switch {
   233  	case err == sql.ErrNoRows:
   234  		log.Printf("No user with that ID.")
   235  	case err != nil:
   236  		log.Fatal(err)
   237  	default:
   238  		fmt.Printf("Username is %s\n", username)
   239  	}
   240  }
   241  
   242  func ExampleStmt_QueryRowContext() {
   243  	// In normal use, create one Stmt when your process starts.
   244  	stmt, err := db.PrepareContext(ctx, "SELECT username FROM users WHERE id = ?")
   245  	if err != nil {
   246  		log.Fatal(err)
   247  	}
   248  	// Then reuse it each time you need to issue the query.
   249  	id := 43
   250  	var username string
   251  	err = stmt.QueryRowContext(ctx, id).Scan(&username)
   252  	switch {
   253  	case err == sql.ErrNoRows:
   254  		log.Printf("No user with that ID.")
   255  	case err != nil:
   256  		log.Fatal(err)
   257  	default:
   258  		fmt.Printf("Username is %s\n", username)
   259  	}
   260  }
   261  
   262  func ExampleRows() {
   263  	age := 27
   264  	rows, err := db.QueryContext(ctx, "SELECT name FROM users WHERE age=?", age)
   265  	if err != nil {
   266  		log.Fatal(err)
   267  	}
   268  	defer rows.Close()
   269  	names := make([]string, 0)
   270  	for rows.Next() {
   271  		var name string
   272  		if err := rows.Scan(&name); err != nil {
   273  			log.Fatal(err)
   274  		}
   275  		names = append(names, name)
   276  	}
   277  	if err := rows.Err(); err != nil {
   278  		log.Fatal(err)
   279  	}
   280  	fmt.Printf("%s are %d years old", strings.Join(names, ", "), age)
   281  }
   282  

View as plain text