1// Copyright 2018 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	"flag"
11	"log"
12	"os"
13	"os/signal"
14	"time"
15)
16
17var pool *sql.DB // Database connection pool.
18
19func Example_openDBCLI() {
20	id := flag.Int64("id", 0, "person ID to find")
21	dsn := flag.String("dsn", os.Getenv("DSN"), "connection data source name")
22	flag.Parse()
23
24	if len(*dsn) == 0 {
25		log.Fatal("missing dsn flag")
26	}
27	if *id == 0 {
28		log.Fatal("missing person ID")
29	}
30	var err error
31
32	// Opening a driver typically will not attempt to connect to the database.
33	pool, err = sql.Open("driver-name", *dsn)
34	if err != nil {
35		// This will not be a connection error, but a DSN parse error or
36		// another initialization error.
37		log.Fatal("unable to use data source name", err)
38	}
39	defer pool.Close()
40
41	pool.SetConnMaxLifetime(0)
42	pool.SetMaxIdleConns(3)
43	pool.SetMaxOpenConns(3)
44
45	ctx, stop := context.WithCancel(context.Background())
46	defer stop()
47
48	appSignal := make(chan os.Signal, 3)
49	signal.Notify(appSignal, os.Interrupt)
50
51	go func() {
52		<-appSignal
53		stop()
54	}()
55
56	Ping(ctx)
57
58	Query(ctx, *id)
59}
60
61// Ping the database to verify DSN provided by the user is valid and the
62// server accessible. If the ping fails exit the program with an error.
63func Ping(ctx context.Context) {
64	ctx, cancel := context.WithTimeout(ctx, 1*time.Second)
65	defer cancel()
66
67	if err := pool.PingContext(ctx); err != nil {
68		log.Fatalf("unable to connect to database: %v", err)
69	}
70}
71
72// Query the database for the information requested and prints the results.
73// If the query fails exit the program with an error.
74func Query(ctx context.Context, id int64) {
75	ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
76	defer cancel()
77
78	var name string
79	err := pool.QueryRowContext(ctx, "select p.name from people as p where p.id = :id;", sql.Named("id", id)).Scan(&name)
80	if err != nil {
81		log.Fatal("unable to execute search query", err)
82	}
83	log.Println("name=", name)
84}
85