Golang Stmt.QueryRow方法代码示例

// QueryRow executes a prepared query statement with the given arguments. If an
// error occurs during the execution of the statement, that error will be returned
// by a call to Scan on the returned *Row, which is always non-nil. If the query
// selects no rows, the *Row's Scan will return ErrNoRows. Otherwise, the *Row's
// Scan scans the first selected row and discards the rest.
//
// QueryRow prepares the same statement on another connection and queries it
func (s *Stmt) QueryRow(ctx context.Context, args ...interface{}) *Row {
	if s.err != nil {
		return &Row{err: s.err}
	}

	done := make(chan struct{}, 0)

	var res *sql.Row
	f := func(sqldb *sql.DB) {
		defer close(done)

		var stmt *sql.Stmt
		stmt, err := sqldb.Prepare(s.query)
		if err != nil {
			return
		}

		res = stmt.QueryRow(args...)
	}

	sqldb, opErr := s.db.handleWithSQL(ctx, f, done)
	if opErr != nil {
		return &Row{err: opErr}
	}

	return &Row{
		row:   res,
		sqldb: sqldb,
		db:    s.db,
	}
}

func monitorRepoChanges(reposSelect *sql.Stmt, changedRepos chan [2]string,
	GitHubClient *github.Client) {
	firehose := make(chan github.Event, 30)
	go gitHubFirehose(firehose, GitHubClient)

	for e := range firehose {
		if *e.Type == "PushEvent" {
			name := *e.Repo.Name

			var mainName string
			err := reposSelect.QueryRow(name).Scan(&mainName)
			switch {
			case err == sql.ErrNoRows:
				// not a monitored repo
			case err != nil:
				log.Println("[!] Name lookup failure", err)
			default:
				changedRepos <- [...]string{name, mainName}
				if len(changedRepos) > cap(changedRepos)/10*9 {
					log.Println("[!] Queue is filling up:", len(changedRepos))
				}
			}
		}
	}
}

func (rdb *RelationalDB) insertLink(
	insertLinkStmt *sql.Stmt,
	getPageIDStmt *sql.Stmt,
	insertPageStmt *sql.Stmt,
	parent string,
	child string,
) {
	var parentID int64
	var childID int64
	if rdb.pageCache.URL == parent {
		parentID = rdb.pageCache.PageID
	} else {
		row := getPageIDStmt.QueryRow(parent)
		err := row.Scan(&parentID)
		if err == sql.ErrNoRows {
			insertPageStmt.Exec(nil, parent, nil, nil)
		}
	}
	insertPageStmt.Exec(nil, child, nil, nil)
	row := getPageIDStmt.QueryRow(child)
	err := row.Scan(&childID)
	if err != nil {
		log.Fatal(err)
	}
	insertLinkStmt.Exec(parentID, childID)
}

func GeoipLookup(stmt *sql.Stmt, ip string) (*GeoIP, error) {
	IP := net.ParseIP(ip)
	reserved := false
	for _, net := range reservedIPs {
		if net.Contains(IP) {
			reserved = true
			break
		}
	}
	geoip := GeoIP{Ip: ip}
	if reserved {
		geoip.CountryCode = "RD"
		geoip.CountryName = "Reserved"
	} else {
		var uintIP uint32
		b := bytes.NewBuffer(IP.To4())
		binary.Read(b, binary.BigEndian, &uintIP)
		if err := stmt.QueryRow(uintIP).Scan(
			&geoip.CountryCode,
			&geoip.CountryName,
			&geoip.RegionCode,
			&geoip.RegionName,
			&geoip.CityName,
			&geoip.ZipCode,
			&geoip.Latitude,
			&geoip.Longitude,
			&geoip.MetroCode,
			&geoip.AreaCode,
		); err != nil {
			return nil, err
		}
	}
	return &geoip, nil
}

func RawRead(b *B) {
	var m *Model
	var stmt *sql.Stmt
	wrapExecute(b, func() {
		var err error
		initDB()
		m = NewModel()
		rawInsert(m)
		stmt, err = raw.Prepare(rawSelectSQL)
		if err != nil {
			fmt.Println(err)
			b.FailNow()
		}
	})
	defer stmt.Close()

	for i := 0; i < b.N; i++ {
		var mout Model
		err := stmt.QueryRow(m.Id).Scan(
			&mout.Id,
			&mout.Name,
			&mout.Title,
			&mout.Fax,
			&mout.Web,
			&mout.Age,
			&mout.Right,
			&mout.Counter,
		)
		if err != nil {
			fmt.Println(err)
			b.FailNow()
		}
	}
}

func lookup(stmt *sql.Stmt, IP net.IP, nIP uint32) (*GeoIP, error) {
	var reserved bool
	for _, net := range reservedIPs {
		if net.Contains(IP) {
			reserved = true
			break
		}
	}
	geoip := GeoIP{Ip: IP.String()}
	if reserved {
		geoip.CountryCode = "RD"
		geoip.CountryName = "Reserved"
	} else {
		if err := stmt.QueryRow(nIP).Scan(
			&geoip.CountryCode,
			&geoip.CountryName,
			&geoip.RegionCode,
			&geoip.RegionName,
			&geoip.CityName,
			&geoip.ZipCode,
			&geoip.Latitude,
			&geoip.Longitude,
			&geoip.MetroCode,
			&geoip.AreaCode,
		); err != nil {
			return nil, err
		}
	}
	return &geoip, nil
}

func getNextHstDate(stmt *sql.Stmt, symbol string, year int) time.Time {

	start_dt := strings.Join([]string{strconv.Itoa(year), "01", "01"}, "-")
	var nextHstDt time.Time
	err := stmt.QueryRow(symbol, start_dt).Scan(&nextHstDt)
	if err != nil {
		return time.Date(year, 1, 1, 0, 0, 0, 0, time.UTC)
	}
	return nextHstDt
}

func (i *Images) getRawImage(id string) (buff []byte, err error) {
	var (
		smt *sql.Stmt
	)
	db := i.Connection()
	if smt, err = db.Prepare(ImageQ); err != nil {
		return
	}
	row := smt.QueryRow(id)
	if err = row.Scan(&buff); err != nil {
		return
	}
	return
}

func initNode(conn net.Conn, stmt *sql.Stmt) {
	log.Print("Init client: ", conn.RemoteAddr())
	address := conn.RemoteAddr().String()
	dot := strings.Index(address, ":")
	addr := address[0:dot]
	var id int
	err := stmt.QueryRow(addr).Scan(&id) // if no such id, QueryRow return ErrNoRows
	if err != nil {
		log.Print("stmt.QueryRow(): ", err)
		return
	}
	data, err := json.Marshal(id)
	if err != nil {
		log.Print("error in initNode: ", err)
		return
	}
	conn.Write(data)
	go initNodeUrls(addr) // send address lists to this node
}

func (p *Person) Get(id string) (err error) {
	var stm *sql.Stmt
	db := p.Connection()
	if stm, err = db.Prepare(PersonQ); err != nil {
		return
	}
	row := stm.QueryRow(id)
	dest := utils.Row{
		new(sql.NullString), //IMIE
		new(sql.NullString), //NAZWISKO
		new(sql.NullString), //TELEFON
		new(sql.NullString), //TELEFON_2
		new(sql.NullString), //TELEFON_3
		new(sql.NullString), //EMAIL
	}
	err = row.Scan(dest...)
	p.row = dest
	return
}

func bmPreparedQueryRow(b *testing.B) {
	var err error
	var stmt *sql.Stmt
	var num int64
	var str string
	var i int64
	for rep := 0; rep < 1000; rep++ {
		stmt, err = db.Prepare("SELECT * FROM test WHERE number=?")
		if err != nil {
			panic(err)
		}
		for i = 0; i < 100; i++ {
			stmt.QueryRow(i).Scan(&num, &str)
			if err != nil {
				panic(err)
			}
			if num != i {
				panic(fmt.Sprintf("Result didn't match: %d!=%d", num, i))
			}
		}
		stmt.Close()
	}
}

func ipdb_lookup(stmt *sql.Stmt, cache *Cache, IP net.IP, nIP uint32) (*GeoIP, error) {
	var reserved bool
	for _, net := range reservedIPs {
		if net.Contains(IP) {
			reserved = true
			break
		}
	}

	geoip := &GeoIP{Ip: IP.String()}
	if reserved {
		geoip.CountryCode = "RD"
		geoip.CountryName = "Reserved"
	} else {
		var locId int
		if err := stmt.QueryRow(nIP).Scan(&locId); err != nil {
			return nil, err
		}

		cache.Update(geoip, locId)
	}

	return geoip, nil
}

func (v *Vortrag) Put() (err error) {
	log.Println("Put", v)
	tx, _ := db.Begin()

	defer tx.Rollback()

	var stmt *sql.Stmt
	if v.Id < 0 {
		if v.Date.IsZero() {
			stmt, err = tx.Prepare("INSERT INTO vortraege (topic, abstract, speaker, password) VALUES ($1, $2, $3, $4) RETURNING id")
			if err != nil {
				return err
			}

			err = stmt.QueryRow(v.Topic, v.Abstract, v.Speaker, v.Password).Scan(&v.Id)
			if err != nil {
				return err
			}

			stmt, err = tx.Prepare("INSERT INTO vortrag_links (vortrag, kind, url) VALUES ($1, $2, $3)")
			if err != nil {
				return err
			}
			for _, link := range v.Links {
				_, err = stmt.Exec(v.Id, link.Kind, link.Url)
				if err != nil {
					return err
				}
			}
		} else {
			stmt, err = tx.Prepare("INSERT INTO vortraege (topic, abstract, speaker, password, date) VALUES ($1, $2, $3, $4, $5) RETURNING id")
			if err != nil {
				return err
			}

			err = stmt.QueryRow(v.Topic, v.Abstract, v.Speaker, v.Password, time.Time(v.Date)).Scan(&v.Id)
			if err != nil {
				return err
			}

			stmt, err = tx.Prepare("INSERT INTO vortrag_links (vortrag, kind, url) VALUES ($1, $2, $3)")
			if err != nil {
				return err
			}

			for _, link := range v.Links {
				_, err = stmt.Exec(v.Id, link.Kind, link.Url)
				if err != nil {
					return err
				}
			}
		}
	} else {
		if v.Date.IsZero() {
			stmt, err = tx.Prepare("UPDATE vortraege SET topic = $1, abstract = $2, speaker = $3, date = NULL WHERE id = $4")
			if err != nil {
				return err
			}

			_, err = stmt.Exec(v.Topic, v.Abstract, v.Speaker, v.Id)
			if err != nil {
				return err
			}

			_, err = tx.Exec("DELETE FROM vortrag_links WHERE vortrag = $1;", v.Id)
			if err != nil {
				return err
			}

			stmt, err = tx.Prepare("INSERT INTO vortrag_links (vortrag, kind, url) VALUES ($1, $2, $3);")
			if err != nil {
				return err
			}

			for _, link := range v.Links {
				_, err = stmt.Exec(v.Id, link.Kind, link.Url)
				if err != nil {
					return err
				}
			}
		} else {
			stmt, err = tx.Prepare("UPDATE vortraege SET topic = $1, abstract = $2, speaker = $3, date = $4 WHERE id = $5")
			if err != nil {
				return err
			}

			_, err = stmt.Exec(v.Topic, v.Abstract, v.Speaker, time.Time(v.Date), v.Id)
			if err != nil {
				return err
			}

			_, err = tx.Exec("DELETE FROM vortrag_links WHERE vortrag = $1;", v.Id)
			if err != nil {
				return err
			}

			stmt, err = tx.Prepare("INSERT INTO vortrag_links (vortrag, kind, url) VALUES ($1, $2, $3);")
			if err != nil {
				return err
			}
//.........这里部分代码省略.........

func StoreRedirects(db *sql.DB, redirs []wikidump.Redirect,
	bar *pb.ProgressBar) error {

	counts := make([]linkCount, 0)

	var titleId, old, del, delTitle, insTitle, ins, update *sql.Stmt
	tx, err := db.Begin()
	if err == nil {
		titleId, err = tx.Prepare(`select id from titles where title = ?`)
	}
	if err == nil {
		old, err = tx.Prepare(
			`select ngramhash, count from linkstats where targetid = ?`)
	}
	if err == nil {
		del, err = tx.Prepare(`delete from linkstats where targetid = ?`)
	}
	if err == nil {
		delTitle, err = tx.Prepare(`delete from titles where id = ?`)
	}
	if err == nil {
		insTitle, err = tx.Prepare(
			`insert or ignore into titles values (NULL, ?)`)
	}
	if err == nil {
		ins, err = tx.Prepare(
			`insert or ignore into linkstats values
			 (?, (select id from titles where title = ?), 0)`)
	}
	if err == nil {
		update, err = tx.Prepare(
			`update linkstats set count = count + ?
			 where targetid = (select id from titles where title = ?)
			       and ngramhash = ?`)
	}
	if err != nil {
		return err
	}

	for _, r := range redirs {
		if bar != nil {
			bar.Increment()
		}

		var fromId int
		err := titleId.QueryRow(r.Title).Scan(&fromId)
		if err == sql.ErrNoRows { // No links to this redirect.
			continue
		} else if err != nil {
			return err
		}

		rows, err := old.Query(fromId)
		if err != nil {
			return err
		}

		// SQLite won't let us INSERT or UPDATE while doing a SELECT.
		for counts = counts[:0]; rows.Next(); {
			var count float64
			var hash int64
			rows.Scan(&hash, &count)
			counts = append(counts, linkCount{hash, count})
		}
		rows.Close()
		err = rows.Err()

		if err == nil {
			_, err = del.Exec(fromId)
		}
		if err == nil {
			_, err = delTitle.Exec(fromId)
		}

		if err != nil {
			return err
		}

		for _, c := range counts {
			if err == nil {
				_, err = insTitle.Exec(r.Target)
			}
			if err == nil {
				_, err = ins.Exec(c.hash, r.Target)
			}
			if err == nil {
				_, err = update.Exec(c.count, r.Target, c.hash)
			}
		}
		if err != nil {
			return err
		}
	}
	err = tx.Commit()
	return err
}

var textHtml = http.Header{"Content-Type": []string{"text/html"}}

// Test 1: JSON serialization
var jsonFilter = falcore.NewRequestFilter(func(req *falcore.Request) *http.Response {
	if req.HttpRequest.URL.Path == "/json" {
		resp, _ := responder.JSONResponse(req.HttpRequest, 200, applicationJson, &Message{helloWorldString})
		return resp
	}
	return nil
})

// Test 2: Single database query
var dbFilter = falcore.NewRequestFilter(func(req *falcore.Request) *http.Response {
	if req.HttpRequest.URL.Path == "/db" {
		var world World
		err := worldStatement.QueryRow(rand.Intn(worldRowCount)+1).Scan(&world.Id, &world.RandomNumber)
		if err != nil {
			log.Fatalf("Error scanning world row: %s", err.Error())
		}

		resp, _ := responder.JSONResponse(req.HttpRequest, 200, applicationJson, &world)
		return resp
	}
	return nil
})

// Test 3: Multiple database queries
var queriesFilter = falcore.NewRequestFilter(func(req *falcore.Request) *http.Response {

	if req.HttpRequest.URL.Path == "/queries" {