mirror of https://go.googlesource.com/go
370 lines
8.5 KiB
Go
370 lines
8.5 KiB
Go
// 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(rerr)
|
|
}
|
|
|
|
// 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 ExampleDB_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)
|
|
}
|