Golang DB.Close方法代碼示例

func (c *Connection) Connect(tries uint) error {
	if tries == 0 {
		return nil
	}

	var err error
	var db *sql.DB
	for i := tries; i > 0; i-- {
		// Wait before attempt.
		time.Sleep(c.backoff.Wait())

		// Open connection to MySQL but...
		db, err = sql.Open("mysql", c.dsn)
		if err != nil {
			continue
		}

		// ...try to use the connection for real.
		if err = db.Ping(); err != nil {
			// Connection failed.  Wrong username or password?
			db.Close()
			continue
		}

		// Connected
		c.conn = db
		c.backoff.Success()
		return nil
	}

	return errors.New(fmt.Sprintf("Failed to connect to MySQL after %d tries (%s)", tries, err))
}

func Get(id string) (*Video, error) {
	var (
		video *Video = &Video{}
		err   error
		db    *sql.DB
	)

	db, err = settings.Db()
	if err != nil {
		return nil, err
	}
	defer db.Close()

	err = db.QueryRow("SELECT * FROM videos WHERE id = $1", id).Scan(
		&video.Id,
		&video.Title,
		&video.YoutubeId,
		&video.NicoId,
		&video.UploadedAt,
		&video.CreatedAt,
		&video.UpdatedAt,
	)
	if err != nil {
		return nil, err
	}

	return video, nil
}

func (sqlite sqlite_driver) OpenDatabase(db *sql.DB) error {
	if _, err := db.Exec(`
	CREATE TABLE IF NOT EXISTS samples (
		timestamp   integer,
		id          text,
		channel     integer,
		serial      string,
		key         string,
		min         real,
		max         real,
		avg         real
	)`); err != nil {
		db.Close()
		return err
	}

	if _, err := db.Exec(`
	CREATE INDEX IF NOT EXISTS i_samples ON samples (
		timestamp,
		key,
		id,
		channel,
		serial
	)`); err != nil {
		db.Close()
		return err
	}

	return nil
}

func getCustomerPrice(personId int, productId int, number int) ([]*model.CustomerPrice, error) {
	var conn *sql.DB
	var stmt *sql.Stmt
	var err error
	if conn, err = db.Connect(); err != nil {
		return nil, err
	}
	defer conn.Close()

	_sql := "select * from customer_special_price where person_id = ? and product_id = ? order by create_time DESC limit ?"
	if stmt, err = conn.Prepare(_sql); err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(personId, productId, number)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	models := []*model.CustomerPrice{}
	for rows.Next() {
		m := new(model.CustomerPrice)
		var blackhole sql.NullInt64
		err := rows.Scan(&m.Id, &m.PersonId, &m.ProductId, &m.Price, &m.CreateTime, &blackhole /*&m.LastUsedTime */)
		if err != nil {
			panic(err)
		}
		models = append(models, m)
	}
	return models, nil
}

func (s *sphinxPools) newConn() (*SphinxDB, error) {
	var (
		err error
		db  *sql.DB
		o   orm.Ormer
	)

	if db, err = sql.Open("sphinx", "root:[email protected]("+setting.SphinxHost+")/?loc=UTC"); err != nil {
		return nil, err
	}

	if err = db.Ping(); err != nil {
		db.Close()
		return nil, err
	}

	db.SetMaxIdleConns(1)
	db.SetMaxOpenConns(2)

	o, err = orm.NewOrmWithDB("sphinx", "sphinx", db)
	if err != nil {
		return nil, err
	}

	sdb := &SphinxDB{
		alive: true,
		pools: s,
		db:    db,
		orm:   o,
	}
	return sdb, nil
}

func DatabaseConnect(c conf.Database) (*sql.DB, error) {
	var db *sql.DB
	var err error

	if c.Type == "mysql" {
		if c.Password == "" {
			db, err = sql.Open("mysql", c.Username+"@"+c.Protocol+"("+c.Address+":"+strconv.Itoa(c.Port)+")/")
		} else {
			db, err = sql.Open("mysql", c.Username+":"+c.Password+"@"+c.Protocol+"("+c.Address+":"+strconv.Itoa(c.Port)+")/")
		}
	}

	if err != nil {
		slog.Errorf("%v: %v: %v: %v", "Database", c.Type, "connect open error", err)
		return nil, fmt.Errorf("%v: %v", "connect open error", err)
	}

	// check if can connect to database, if fail, check again every minute until connected
	for {
		err = db.Ping()
		if err == nil {
			break
		}
		slog.Errorf("%v: %v: %v: %v", "Database", c.Type, "connect ping error", err)
		next := time.After(time.Minute)
		select {
		case <-next:
		case <-ContinuousCollectorVars.quit:
			db.Close()
			return nil, fmt.Errorf("connect while quitting")
		}
	}

	return db, nil
}

func process(node *types.Container, credential *types.Credential, gauge *prometheus.GaugeVec) error {
	var err error

	//logit.Info.Println("dbsize node=" + node.Name + " credentials Username:" + credential.Username + " Password:" + credential.Password + " Database:" + credential.Database + " Host:" + credential.Host)
	var db *sql.DB
	db, err = util.GetMonitoringConnection(credential.Host,
		credential.Username, credential.Port, credential.Database, credential.Password)
	defer db.Close()
	if err != nil {
		logit.Error.Println("error in getting connectionto " + credential.Host)
		return err
	}

	var metrics []DBMetric
	//logit.Info.Println("dbsize running pg2 on " + node.Name)
	metrics, err = pg2(db)

	//write metrcs to prometheus

	i := 0
	for i = range metrics {
		//logit.Info.Println("dbsize setting dbsize metric")
		gauge.WithLabelValues(node.Name, metrics[i].Name).Set(metrics[i].Value)
		i++
	}

	return nil

}

func saveToDB(db *sql.DB, stockList []Stock, configuration Configuration) {
	db, err := sql.Open("mysql", configuration.MySQLUser+":"+configuration.MySQLPass+"@tcp("+configuration.MySQLHost+":"+configuration.MySQLPort+")/"+configuration.MySQLDB)
	if err != nil {
		fmt.Println("Could not connect to database")
		return
	}

	for i := range stockList {
		//@TODO Save results to database
		stock := stockList[i]

		// Prepare statement for inserting data
		insertStatement := "INSERT INTO st_data (`symbol`, `exchange`, `name`, `change`, `close`, `percentageChange`, `open`, `high`, `low`, `volume` , `avgVolume`, `high52` , `low52`, `marketCap`, `eps`, `shares`, `time`, `minute`, `hour`, `day`, `month`, `year`) "
		insertStatement += "VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )"
		stmtIns, err := db.Prepare(insertStatement)
		if err != nil {
			panic(err.Error()) // proper error handling instead of panic in your app
		}
		defer stmtIns.Close() // Close the statement when we leave main() / the program terminates

		// Convert variables
		sqlChange, _ := strconv.ParseFloat(strings.Replace(stock.Change, ",", "", -1), 64)
		sqlClose, _ := strconv.ParseFloat(strings.Replace(stock.Close, ",", "", -1), 64)
		sqlPercChange, _ := strconv.ParseFloat(stock.PercentageChange, 64)
		sqlOpen, _ := strconv.ParseFloat(strings.Replace(stock.Open, ",", "", -1), 64)
		sqlHigh, _ := strconv.ParseFloat(strings.Replace(stock.High, ",", "", -1), 64)
		sqlLow, _ := strconv.ParseFloat(strings.Replace(stock.Low, ",", "", -1), 64)
		sqlHigh52, _ := strconv.ParseFloat(strings.Replace(stock.High52, ",", "", -1), 64)
		sqlLow52, _ := strconv.ParseFloat(strings.Replace(stock.Low52, ",", "", -1), 64)
		sqlEps, _ := strconv.ParseFloat(stock.EPS, 64)

		// Some contain letters that need to be converted
		sqlVolume := convertLetterToDigits(stock.Volume)
		sqlAvgVolume := convertLetterToDigits(stock.AverageVolume)
		sqlMarketCap := convertLetterToDigits(stock.MarketCap)
		sqlShares := convertLetterToDigits(stock.Shares)

		t := time.Now()
		utc, err := time.LoadLocation(configuration.TimeZone)
		if err != nil {
			fmt.Println("err: ", err.Error())
		}
		sqlTime := int32(t.Unix())
		sqlMinute := t.In(utc).Minute()
		sqlHour := t.In(utc).Hour()
		sqlDay := t.In(utc).Day()
		sqlMonth := t.In(utc).Month()
		sqlYear := t.In(utc).Year()

		_, err = stmtIns.Exec(stock.Symbol, stock.Exchange, stock.Name, sqlChange, sqlClose,
			sqlPercChange, sqlOpen, sqlHigh, sqlLow, sqlVolume, sqlAvgVolume,
			sqlHigh52, sqlLow52, sqlMarketCap, sqlEps, sqlShares,
			sqlTime, sqlMinute, sqlHour, sqlDay, sqlMonth, sqlYear)

		if err != nil {
			fmt.Println("Could not save results: " + err.Error())
		}
	}
	defer db.Close()
}

func database(res http.ResponseWriter, req *http.Request) {

	//	fmt.Fprintln(res, "testing...\n")

	var db *sql.DB
	var err error
	dokku_db := os.Getenv("DATABASE_URL")

	db, err = sql.Open("postgres", dokku_db+"?sslmode=disable")
	if err != nil {
		fmt.Printf("sql.Open error: %v\n", err)
		return
	}
	defer db.Close()

	err = doInitialize(db)
	if err != nil {
		fmt.Print("initialize error: %v\n", err)
		return
	}

	err = doSelect(res, db)
	if err != nil {
		fmt.Print("select error: %v\n", err)
		return
	}
}

func Disconnect(db *sql.DB) {
	err := db.Close()
	if err != nil {
		log.Println(err)
	}
	log.Println("Database connection: Closed.")
}

func main() {

	// Establish mysql connection
	var db *sql.DB
	var err error
	db, err = sql.Open("mysql", "user:[email protected](127.0.0.1:3306)/db_name")
	if err != nil {
		log.Println(err)
	}
	defer db.Close()

	// Single query
	var str string
	str = singleQuery(db)
	fmt.Println(str)

	// Another query - multiple rows
	var rows *sql.Rows
	rows = query(db)
	printRows(rows)
	rows.Close()

	// Insert statement
	//stmt, err := db.Prepare("INSERT INTO users(username, password) VALUES(?, ?)")
	//if err != nil {	log.Fatal(err) }
	//_, err = stmt.Exec("myuser", "mypass")
	//if err != nil {	log.Fatal(err) }

}

func SetEmailTaskDone(emailTask *EmailTask) bool {
	var db *sql.DB
	var stmt *sql.Stmt
	var err error
	LogError := func() {
		seelog.Errorf("[SetEmailTaskDone Failed] [EmailTask : %v] [ERROR : %v]", *emailTask, err)
	}

	if db, err = GetDBConnection(); err != nil {
		LogError()
		return false
	}
	defer db.Close()

	if stmt, err = db.Prepare("UPDATE `email_request` SET `is_done` = 1 WHERE `id` = ?"); err != nil {
		LogError()
		return false
	}
	defer stmt.Close()

	if _, err = stmt.Exec(emailTask.Id); err != nil {
		LogError()
		return false
	}

	return true
}

// StatusUpdate called by backup jobs as they execute
func StatusUpdate(w rest.ResponseWriter, r *rest.Request) {

	request := TaskStatus{}
	err := r.DecodeJsonPayload(&request)
	if err != nil {
		logit.Error.Println(err.Error())
		rest.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}

	var dbConn *sql.DB
	dbConn, err = util.GetConnection(CLUSTERADMIN_DB)
	if err != nil {
		logit.Error.Println(err.Error())
		rest.Error(w, err.Error(), http.StatusInternalServerError)
		return

	}
	defer dbConn.Close()

	//logit.Info.Println("StatusUpdate called")

	err = UpdateStatus(dbConn, &request)
	if err != nil {
		logit.Error.Println(err.Error())
		rest.Error(w, err.Error(), http.StatusInternalServerError)
		return
	}

	response := StatusUpdateResponse{}
	response.Output = "ok"
	w.WriteJson(&response)
}

func TestEmptyDB(db *sql.DB) {
	q := `select relname from pg_class where relname = 'post' and relkind='r'`
	var initialized int = 0
	rows, err := db.Query(q)
	if err != nil {
		fmt.Println("DB Query panic")
		panic(fmt.Sprintf("%s", err))
	}
	for rows.Next() {
		var relname string
		err = rows.Scan(&relname)
		if len(relname) > 0 {
			initialized++
		}
	}
	err = rows.Err()
	util.HandleErr(err)
	rows.Close()

	if initialized == 0 {
		q = `DROP SEQUENCE IF EXISTS post_id_seq CASCADE;
	            DROP TABLE IF EXISTS post CASCADE;
	            CREATE SEQUENCE post_id_seq;
	            CREATE TABLE post(id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('post_id_seq'),
	                title VARCHAR(32), content TEXT, user_id INTEGER,
	                published BOOLEAN, created TIMESTAMP, modified TIMESTAMP)`
		_, err = db.Exec(q)
		util.HandleErr(err)
	}
	defer db.Close()
}

func runTests(t *testing.T, dsn string, tests ...func(dbt *DBTest)) {
	if !available {
		t.Skipf("MySQL server not running on %s", netAddr)
	}

	db, err := sql.Open("mysql", dsn)
	if err != nil {
		t.Fatalf("error connecting: %s", err.Error())
	}
	defer db.Close()

	db.Exec("DROP TABLE IF EXISTS test")

	dsn2 := dsn + "&interpolateParams=true"
	var db2 *sql.DB
	if _, err := ParseDSN(dsn2); err != errInvalidDSNUnsafeCollation {
		db2, err = sql.Open("mysql", dsn2)
		if err != nil {
			t.Fatalf("error connecting: %s", err.Error())
		}
		defer db2.Close()
	}

	dbt := &DBTest{t, db}
	dbt2 := &DBTest{t, db2}
	for _, test := range tests {
		test(dbt)
		dbt.db.Exec("DROP TABLE IF EXISTS test")
		if db2 != nil {
			test(dbt2)
			dbt2.db.Exec("DROP TABLE IF EXISTS test")
		}
	}
}