Golang Rows.Close方法代码示例

func (this *db) GetHostsByProxy(proxy string) []*Host {
	var (
		rows *sql.Rows
		err  error
	)
	if len(proxy) == 0 {
		rows, err = this.Conn.Query("select h.id, h.server_id, h.uuid, h.ip, h.status, h.last_check, p.ip from host_host as h " +
			"left join system_proxy as p on h.proxy_id = p.id where p.ip is NULL")
	} else {
		rows, err = this.Conn.Query("select h.id, h.server_id, h.uuid, h.ip, h.status, h.last_check, p.ip from host_host as h "+
			"left join system_proxy as p on h.proxy_id = p.id where p.ip=?", proxy)
	}
	if err != nil {
		return nil
	}
	defer rows.Close()
	hosts := []*Host{}
	for rows.Next() {
		host := &Host{}
		var proxy sql.NullString
		var assestid sql.NullInt64
		if err := rows.Scan(&host.ID, &assestid, &host.UUID, &host.Ip, &host.Status, &host.last_check, &proxy); err != nil {
			continue
		}
		host.Proxy = proxy.String
		if assestid.Valid {
			host.AssestId = int(assestid.Int64)
		}
		hosts = append(hosts, host)
	}
	return hosts
}

//	sql_ := fmt.Sprintf(`SELECT "Json" %s where `+p.wheresql, p.getFrom())
//	var rows *sql.Rows
//	if rows, p.Err = p._query(sql_, p.whereargs...); p.NotErr() {
//		defer rows.Close()
//		for rows.Next() {
//			var v []byte
//			rows.Scan(&v)
//			obj := reflect.New(p.model.Type).Interface()
//			json.Unmarshal(v, obj)
//			slicev = reflect.Append(slicev, reflect.ValueOf(obj).Elem())
//		}
//		resultv.Elem().Set(slicev.Slice(0, slicev.Len()))
//	}
//	return p
//}
func (p *Scope) PageJson(lst T, page, perPage int) (pager db.Paging) {
	p.checkModel(lst)
	pf := filter.NewPageFilter(page, perPage)
	p.Limit(pf.Skip(), perPage)
	pager.Total = p.Count(lst)
	//	log.Println(pager.Total)
	resultv := reflect.ValueOf(lst)
	if resultv.Kind() != reflect.Ptr || resultv.Elem().Kind() != reflect.Slice {
		panic("out argument must be a slice address")
	}
	slicev := resultv.Elem()
	wsa := p.buildWhere()
	pas := p.buildPage()
	sql_ := fmt.Sprintf(`SELECT "Json" %s `+wsa.Sql+" "+pas.Sql, p.getFrom())
	var rows *sql.Rows
	if rows, p.Err = p._query(sql_, wsa.Args...); p.NotErr() {
		defer rows.Close()
		for rows.Next() {
			var v []byte
			rows.Scan(&v)
			obj := reflect.New(p.model.Type).Interface()
			json.Unmarshal(v, obj)
			slicev = reflect.Append(slicev, reflect.ValueOf(obj).Elem())
		}
		resultv.Elem().Set(slicev.Slice(0, slicev.Len()))
	}
	pager.Items = lst
	//	log.Println(lst)
	return
}

func (persister *Persister) GetServiceAgentList(cond string) ([]ServiceAgent, error) {
	var rows *sql.Rows
	var err error
	if cond == "" {
		rows, err = persister.Db.Query("SELECT service_host,docker_host,docker_port,is_active,perf_factor,ping_interval_secs,last_ping,exec_command,exec_args,portbinding_min,portbinding_max,portbindings FROM serviceagents")
	} else {
		rows, err = persister.Db.Query("SELECT service_host,docker_host,docker_port,is_active,perf_factor,ping_interval_secs,last_ping,exec_command,exec_args,portbinding_min,portbinding_max,portbindings FROM serviceagents where " + cond)
	}
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var serviceagents []ServiceAgent
	i := 0
	for rows.Next() {
		serviceagent := ServiceAgent{}
		var timevalue interface{}
		rows.Scan(&serviceagent.ServiceHost, &serviceagent.DockerHost, &serviceagent.DockerPort, &serviceagent.IsActive, &serviceagent.PerfFactor, &serviceagent.KeepAlive, &timevalue, &serviceagent.ExecCommand, &serviceagent.ExecArgs, &serviceagent.Portbind_min, &serviceagent.Portbind_max, &serviceagent.Portbindings)
		switch timevalue.(type) {
		case string:
			serviceagent.LastPing, err = time.Parse("2006-01-02 15:04:05 ", timevalue.(string))
		case time.Time:
			serviceagent.LastPing = timevalue.(time.Time)
		}

		if err != nil {
			log.Println("error reading row ", err)
		}
		serviceagents = append(serviceagents, serviceagent)
		i = i + 1
	}
	return serviceagents, nil
}

func (m *mysql) AuditLog(from, to, limit, offset int64, reverse bool) ([]*proto.ChangeLog, error) {
	var r *sql.Rows
	var err error

	if from == 0 && to == 0 {
		q := "readLog"
		if reverse {
			q += "Desc"
		}
		r, err = st[q].Query(limit, offset)
	} else {
		q := "readBetween"
		if reverse {
			q += "Desc"
		}
		r, err = st[q].Query(from, to, limit, offset)
	}

	if err != nil {
		return nil, err
	}
	defer r.Close()

	var logs []*proto.ChangeLog

	for r.Next() {
		var id int

		log := &proto.ChangeLog{
			Change: &proto.Change{
				ChangeSet: &proto2.ChangeSet{},
			},
		}
		if err := r.Scan(
			&id,
			&log.Action,
			&log.Change.Id,
			&log.Change.Path,
			&log.Change.Author,
			&log.Change.Comment,
			&log.Change.Timestamp,
			&log.Change.ChangeSet.Timestamp,
			&log.Change.ChangeSet.Checksum,
			&log.Change.ChangeSet.Data,
			&log.Change.ChangeSet.Source,
		); err != nil {
			if err == sql.ErrNoRows {
				return nil, errors.New("not found")
			}
			return nil, err
		}
		logs = append(logs, log)

	}
	if r.Err() != nil {
		return nil, err
	}

	return logs, nil
}

func (gkDbCon *GkDbConDef) getMaxChatId() (int32, *gkerr.GkErrDef) {
	var stmt *sql.Stmt
	var maxId int32 = 0
	var err error

	stmt, err = gkDbCon.sqlDb.Prepare("select max(id) from chat_archives")
	if err != nil {
		return 0, gkerr.GenGkErr("sql.Prepare"+getDatabaseErrorMessage(err), err, ERROR_ID_PREPARE)
	}

	defer stmt.Close()

	var rows *sql.Rows

	rows, err = stmt.Query()
	if err != nil {
		return 0, gkerr.GenGkErr("stmt.Query"+getDatabaseErrorMessage(err), err, ERROR_ID_QUERY)
	}

	defer rows.Close()

	if rows.Next() {
		err = rows.Scan(&maxId)
		if err != nil {
			return 0, gkerr.GenGkErr("rows.Scan"+getDatabaseErrorMessage(err), err, ERROR_ID_ROWS_SCAN)
		}
	}

	return maxId, nil
}

func sqlToMaps(ctx *Ctx, sqlString string, sqlParams ...interface{}) (ret []map[string]interface{}, err error) {
	ret = []map[string]interface{}{}
	var rows *sql.Rows
	rows, err = ctx.db.Query(sqlString, sqlParams...)
	defer rows.Close()
	if err != nil {
		return
	}
	columns, err := rows.Columns()
	if err != nil {
		return
	}
	count := len(columns)
	values := make([]interface{}, count)
	valuePtrs := make([]interface{}, count)
	for i := 0; i < count; i++ {
		valuePtrs[i] = &values[i]
	}
	for rows.Next() {
		retRow := map[string]interface{}{}
		rows.Scan(valuePtrs...)
		for i, col := range columns {
			val := values[i]
			switch val.(type) {
			case []uint8:
				retRow[col] = string(val.([]uint8))
			default:
				retRow[col] = val
			}
		}
		ret = append(ret, retRow)
	}
	return
}

// Query if given upload is expired - an upload thus not having any records which are currently active
func (tx *Transaction) QueryExpiredUpload(id string) (result bool, err error) {
	err = tx.do(func(tx *sql.Tx) (err error) {
		var rows *sql.Rows

		rows, err = tx.Query(`
      SELECT 
        "u"."id" AS "id"
      FROM "uploads" AS "u"
      WHERE "u"."id" = $1
      GROUP BY "u"."id"
      HAVING MAX("u"."expiration") < NOW()`,
			id)
		if err != nil {
			return
		}

		defer rows.Close()

		if rows.Next() {
			result = true
		}

		return
	})

	return
}

func Test_Query(t *testing.T) {
	conn, err := sql.Open("mysql", TestConnParam)
	utest.IsNilNow(t, err)
	defer conn.Close()

	var res *sql.Rows

	res, err = conn.Query("SELECT * FROM test ORDER BY id ASC")
	utest.IsNilNow(t, err)
	defer res.Close()

	i := 0
	for res.Next() {
		var (
			id    int64
			value string
		)

		err := res.Scan(&id, &value)
		utest.IsNilNow(t, err)

		utest.Equal(t, id, int64(i))
		utest.Equal(t, value, strconv.Itoa(i))
		i++
	}

	utest.Equal(t, i, 10)
}

func (db *StatsDB) streamBuckets(result *sql.Rows, out chan Bucket) {
	var err error
	defer result.Close()
LOOP:
	for result.Next() && result.Err() == nil {
		var bucket Bucket
		var info string
		bucket.Info = make(map[string]interface{})
		err = result.Scan(&bucket.Id, &bucket.ServerTime, &bucket.Bucket, &info)
		if err != nil {
			printf("error scanning database: %v", err)
			break LOOP
		}
		dec := json.NewDecoder(bytes.NewBufferString(info))
		err = dec.Decode(&bucket.Info)
		if err != nil {
			printf("error decoding info from record: %v", err)
			break LOOP
		}
		select {
		case out <- bucket:
		case <-time.After(time.Second * 10):
			break LOOP
		}
	}
	close(out)
}

func findNotesByUser(user *User, query string) []*Note {
	var err error
	var rows *sql.Rows

	if len(query) == 0 {
		rows, err = db.Query("SELECT * FROM notes WHERE user_id=?", user.ID)
	} else {
		queryFmt := fmt.Sprintf("%%%s%%", query)
		rows, err = db.Query(
			"SELECT * FROM notes WHERE user_id=? AND (body LIKE ? OR title LIKE ?)",
			user.ID,
			queryFmt,
			queryFmt)
	}

	if err != nil {
		checkErr(err, "findNotesByUser")
	} else {
		defer rows.Close()
	}

	var notes []*Note
	for rows.Next() {
		notes = append(notes, noteFromDbRows(rows))
	}
	return notes
}

// LastK returns the k most recent command lines in history
func (d Database) LastK(qp conf.QueryParams) ([]byte, error) {
	var rows *sql.Rows
	var err error
	switch qp.Unique {
	case true:
		rows, err = d.Query(`SELECT * FROM
                                      (SELECT rowid, * FROM history
                                         WHERE user LIKE ? AND host LIKE ? AND command LIKE ? ESCAPE '\'
                                         GROUP BY command
                                         ORDER BY datetime DESC LIMIT ?)
                                      ORDER BY datetime ASC`,
			qp.User, qp.Host, qp.Command, qp.Kappa)
	default:
		rows, err = d.Query(`SELECT * FROM
                                      (SELECT rowid, * FROM history
                                         WHERE user LIKE ? AND host LIKE ? AND command LIKE ? ESCAPE '\'
                                         ORDER BY datetime DESC LIMIT ?)
                                   ORDER BY datetime ASC`,
			qp.User, qp.Host, qp.Command, qp.Kappa)
	}
	if err != nil {
		return []byte{}, err
	}
	defer rows.Close()

	res := result.New(qp.Format)
	for rows.Next() {
		var user, host, command string
		var t time.Time
		var row int
		rows.Scan(&row, &user, &host, &command, &t)
		res.AddRow(row, user, host, command, t)
	}
	return res.Formatted(), nil
}

func (self *PostgresDatabase) GetThreadReplies(rootpost string, start, limit int) (repls []string) {
	var rows *sql.Rows
	var err error
	if limit > 0 {
		rows, err = self.conn.Query("SELECT message_id FROM ArticlePosts WHERE message_id IN ( SELECT message_id FROM ArticlesPosts WHERE ref_id = $1 ORDER BY time_posted DESC LIMIT $2 ) ORDER BY time_posted ASC", rootpost, limit)
	} else {
		rows, err = self.conn.Query("SELECT message_id FROM ArticlePosts WHERE message_id IN ( SELECT message_id FROM ArticlePosts WHERE ref_id = $1 ) ORDER BY time_posted ASC", rootpost)
	}
	offset := start
	if err == nil {
		for rows.Next() {
			// TODO: this is a hack, optimize queries plz
			if offset > 0 {
				offset--
				continue
			}
			var msgid string
			rows.Scan(&msgid)
			repls = append(repls, msgid)
		}
		rows.Close()
	} else {
		log.Println("failed to get thread replies", rootpost, err)
	}
	return
}

func dataCompletenessTagProto(r *http.Request, h http.Header, b *bytes.Buffer) *weft.Result {
	var err error
	var rows *sql.Rows

	if rows, err = dbR.Query(`SELECT siteID, tag, typeID from data.completeness_tag
				JOIN mtr.tag USING (tagpk)
				JOIN data.site USING (sitepk)
				JOIN data.completeness_type USING (typepk)
				ORDER BY tag ASC`); err != nil {
		return weft.InternalServerError(err)
	}
	defer rows.Close()

	var ts mtrpb.DataCompletenessTagResult

	for rows.Next() {
		var t mtrpb.DataCompletenessTag

		if err = rows.Scan(&t.SiteID, &t.Tag, &t.TypeID); err != nil {
			return weft.InternalServerError(err)
		}

		ts.Result = append(ts.Result, &t)
	}

	var by []byte
	if by, err = proto.Marshal(&ts); err != nil {
		return weft.InternalServerError(err)
	}

	b.Write(by)

	return &weft.StatusOK
}

/*
ScanAll accepts a pointer to a slice of a type and fills it with repeated calls to Scan.

ScanAll only works if you're trying to extract a single object from each row
of the query results. Additionally, it closes the passed sql.Rows object. ScanAll
effectively replaces this code

  // old code
  defer rows.Close()
  objs := []Object{}
  for rows.Next() {
	  var obj Object
	  Scan(rows, &obj)
	  objs = append(objs, obj)
  }

With simply

  // new code
  objs := []Object{}
  ScanAll(rows, &objs)
*/
func ScanAll(rows *sql.Rows, slicePtr interface{}) error {
	defer rows.Close()

	sliceVal := reflect.ValueOf(slicePtr).Elem()

	if sliceVal.Kind() != reflect.Slice {
		return fmt.Errorf("Argument to crud.ScanAll is not a slice")
	}

	elemType := sliceVal.Type().Elem()

	if elemType.Kind() != reflect.Struct {
		return fmt.Errorf("Argument to crud.ScanAll must be a slice of structs")
	}

	for rows.Next() {
		newVal := reflect.New(elemType)

		if er := Scan(rows, newVal.Interface()); er != nil {
			return er
		}

		sliceVal.Set(reflect.Append(sliceVal, newVal.Elem()))
	}

	return nil
}

func Test_Query(t *testing.T) {
	conn, err := sql.Open("mysql", TestConnParam)
	unitest.NotError(t, err)
	defer conn.Close()

	var res *sql.Rows

	res, err = conn.Query("SELECT * FROM test ORDER BY id ASC")
	unitest.NotError(t, err)
	defer res.Close()

	i := 0
	for res.Next() {
		var (
			id    int64
			value string
		)

		err := res.Scan(&id, &value)
		unitest.NotError(t, err)

		unitest.Pass(t, id == int64(i))
		unitest.Pass(t, value == strconv.Itoa(i))
		i++
	}

	unitest.Pass(t, i == 10)
}