Golang Stmt.Query方法代码示例

func (gkDbCon *GkDbConDef) GetPodsList() ([]DbPodDef, *gkerr.GkErrDef) {
	var stmt *sql.Stmt
	var err error
	var dbPodsList []DbPodDef = make([]DbPodDef, 0, 0)

	stmt, err = gkDbCon.sqlDb.Prepare("select id, pod_title from pods")
	if err != nil {
		return nil, 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 nil, gkerr.GenGkErr("stmt.Query"+getDatabaseErrorMessage(err), err, ERROR_ID_QUERY)
	}

	defer rows.Close()

	for rows.Next() {
		var dbPod DbPodDef
		err = rows.Scan(&dbPod.Id, &dbPod.Title)
		if err != nil {
			return nil, gkerr.GenGkErr("rows.Scan"+getDatabaseErrorMessage(err), err, ERROR_ID_ROWS_SCAN)
		}
		dbPodsList = append(dbPodsList, dbPod)
	}

	return dbPodsList, nil
}

// LookupBrand takes a prepared statment (using the BRAND_LOOKUP string),
// a bsin (brand id) string, and looks it up in POD, returning a list of
// matching BRAND structs
func LookupBrand(stmt *sql.Stmt, bsin string) ([]*BRAND, error) {
	results := make([]*BRAND, 0)

	rows, err := stmt.Query(bsin)
	if err != nil {
		return results, err
	}
	defer rows.Close()

	for rows.Next() {
		var n, u sql.NullString
		err := rows.Scan(&n, &u)
		if err != nil {
			return results, err
		} else {
			if n.Valid || u.Valid {
				result := new(BRAND)
				result.Id = bsin
				if n.Valid {
					result.Name = n.String
				}
				if u.Valid {
					result.URL = u.String
				}
				results = append(results, result)
			}
		}
	}

	return results, nil
}

func sexDomain(dom string) (sexAnswer bool, err error) {
	var stmt *sql.Stmt

	stmt, err = db.Prepare(`SELECT dom FROM domains WHERE dom = $1 AND sex = true LIMIT 1`)

	if err != nil {
		fmt.Printf("db.Prepare error: %v\n", err)
		return false, err
	}

	var rows *sql.Rows

	rows, err = stmt.Query(dom)
	if err != nil {
		fmt.Printf("stmt.Query error: %v\n", err)
		return false, err
	}

	defer stmt.Close()
	if rows.Next() {
		var domain string

		err = rows.Scan(&domain)
		if err != nil {
			fmt.Printf("rows.Scan error: %v\n", err)
			return false, err
		}
		if configuration.Debug == "Yes" {
			fmt.Println("domain => \"" + domain + "\"")
		}
		return true, err
	}

	return false, nil
}

func LookupBrandByName(stmt *sql.Stmt, brandName string) ([]*BRAND, error) {
	results := make([]*BRAND, 0)

	rows, err := stmt.Query(fmt.Sprintf("%s%%", brandName)) // like 'brandName%'
	if err != nil {
		return results, err
	}
	defer rows.Close()

	for rows.Next() {
		var i, n, u sql.NullString
		err := rows.Scan(&i, &n, &u)
		if err != nil {
			return results, err
		} else {
			if n.Valid || u.Valid {
				result := new(BRAND)
				result.Id = i.String
				if n.Valid {
					result.Name = n.String
				}
				if u.Valid {
					result.URL = u.String
				}
				results = append(results, result)
			}
		}
	}

	return results, nil
}

func LoadEnvVarsOf(appName string) (envVars map[string]string, err error) {
	envVars = make(map[string]string)

	sq := "select k, v from env where app_name = ?"

	var stmt *sql.Stmt
	stmt, err = DB.Prepare(sq)
	if err != nil {
		log.Printf("[ERROR] prepare sql: %s fail: %v, params: [%s]", sq, err, appName)
		return
	}

	defer stmt.Close()

	var rows *sql.Rows
	rows, err = stmt.Query(appName)
	if err != nil {
		log.Printf("[ERROR] exec sql: %s fail: %v, params: [%s]", sq, err, appName)
		return
	}

	for rows.Next() {
		var k, v string
		err = rows.Scan(&k, &v)
		if err != nil {
			log.Printf("[ERROR] %s scan fail: %s", sq, err)
			return
		}

		envVars[k] = v
	}

	return
}

func LookupContributedBrand(stmt *sql.Stmt, brandName string) ([]*CONTRIBUTED_BRAND, error) {
	results := make([]*CONTRIBUTED_BRAND, 0)

	rows, err := stmt.Query(fmt.Sprintf("%s%%", brandName)) // like 'brandName%'
	if err != nil {
		return results, err
	}
	defer rows.Close()

	for rows.Next() {
		var i, n, u, a sql.NullString
		err := rows.Scan(&i, &n, &u, &a)
		if err != nil {
			return results, err
		} else {
			if n.Valid {
				result := new(CONTRIBUTED_BRAND)
				result.Uuid = i.String
				result.Name = n.String
				result.URL = u.String
				result.AccountID = a.String
				results = append(results, result)
			}
		}
	}

	return results, nil
}

// LookupGtin takes a prepared statment (using the GTIN_LOOKUP string),
// a barcode string, and looks it up in POD, returning a list of matching
// GTIN structs
func LookupGtin(stmt *sql.Stmt, barcode string) ([]*GTIN, error) {
	results := make([]*GTIN, 0)

	rows, err := stmt.Query(barcode)
	if err != nil {
		return results, err
	}
	defer rows.Close()

	for rows.Next() {
		var p, b sql.NullString
		err := rows.Scan(&p, &b)
		if err != nil {
			return results, err
		} else {
			if p.Valid || b.Valid {
				result := new(GTIN)
				result.Id = barcode
				if p.Valid {
					result.ProductName = p.String
				}
				if b.Valid {
					result.BrandId = b.String
				}
				results = append(results, result)
			}
		}
	}

	return results, nil
}

//获取所有任务的任务列表
func FetchTaskList() (int, *list.List) {
	var stmtIns *sql.Stmt
	var err error
	stmtIns, err = db.Prepare("select task_id, create_time from rdb_parse_task")

	if err != nil {
		logs.Log("fetch task list error in dbutil db.Prepare : " + err.Error())
		return -1, list.New()
	}

	defer stmtIns.Close()

	rows, errs := stmtIns.Query()

	if errs != nil {
		logs.Log("fetch task list error in dbutil stmtIns.Query : " + errs.Error())
		return -2, list.New()
	}

	taskList := list.New()

	var taskId string
	var createTime string
	for rows.Next() {
		var task Task
		rows.Scan(&taskId, &createTime)
		task.TaskId = taskId
		task.CreateTime = createTime
		taskList.PushBack(task)
	}
	return 1, taskList
}

// return error if row not found
func (gkDbCon *GkDbConDef) GetUser(userName string) (*DbUserDef, *gkerr.GkErrDef) {
	var stmt *sql.Stmt
	var err error
	var dbUser *DbUserDef = new(DbUserDef)

	stmt, err = gkDbCon.sqlDb.Prepare("select id, user_name, password_hash, password_salt, email, account_creation_date, last_login_date from users where user_name = $1")
	if err != nil {
		return nil, gkerr.GenGkErr("sql.Prepare"+getDatabaseErrorMessage(err), err, ERROR_ID_PREPARE)
	}

	defer stmt.Close()

	var rows *sql.Rows

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

	defer rows.Close()

	if rows.Next() {
		err = rows.Scan(&dbUser.id, &dbUser.UserName, &dbUser.PasswordHash, &dbUser.PasswordSalt, &dbUser.Email, &dbUser.accountCreationDate, &dbUser.lastLoginDate)
		if err != nil {
			return nil, gkerr.GenGkErr("rows.Scan"+getDatabaseErrorMessage(err), err, ERROR_ID_ROWS_SCAN)
		}
	} else {
		return nil, gkerr.GenGkErr("select users", nil, ERROR_ID_NO_ROWS_FOUND)
	}

	return dbUser, 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 doSelect(res http.ResponseWriter, db *sql.DB) error {
	var stmt *sql.Stmt
	var err error

	stmt, err = db.Prepare(selectStatement)
	if err != nil {
		fmt.Printf("db.Prepare error: %v\n", err)
		return err
	}

	var rows *sql.Rows

	rows, err = stmt.Query()
	if err != nil {
		fmt.Printf("stmt.Query error: %v\n", err)
		return err
	}

	defer stmt.Close()

	for rows.Next() {
		var firstname string
		var lastname string

		err = rows.Scan(&firstname, &lastname)
		if err != nil {
			fmt.Printf("rows.Scan error: %v\n", err)
			return err
		}

		fmt.Fprintln(res, "firstname: "+firstname+"    lastname: "+lastname+"\n")
	}

	return nil
}

func (gkDbCon *GkDbConDef) getNextChatArchivesId() (int32, *gkerr.GkErrDef) {
	var stmt *sql.Stmt
	var err error

	stmt, err = gkDbCon.sqlDb.Prepare("select nextval('chat_archives_seq')")
	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()

	var id int32

	if rows.Next() {
		err = rows.Scan(&id)
		if err != nil {
			return 0, gkerr.GenGkErr("rows.Scan"+getDatabaseErrorMessage(err), err, ERROR_ID_ROWS_SCAN)
		}
	} else {
		return 0, gkerr.GenGkErr("select users", nil, ERROR_ID_NO_ROWS_FOUND)
	}

	return id, nil
}

func executePopularNamesCheck(stmt *sql.Stmt, fk int64, table_name string, args [20]string) (bool, error) {
	rows, err := stmt.Query(
		fk,
		table_name,
		args[0], args[1], args[2], args[3], args[4],
		args[5], args[6], args[7], args[8], args[9],
		args[10], args[11], args[12], args[13], args[14],
		args[15], args[16], args[17], args[18], args[19])

	if err != nil {
		return false, errors.New("Query failed.")
	}

	defer rows.Close()

	for rows.Next() {
		var popular_name string

		err = rows.Scan(&popular_name)

		if err != nil {
			log.Printf("Checking failed: %v\n", err)
			return false, err
		}

		log.Printf("Name found: %s\n", popular_name)

		return false, nil
	}

	return true, 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
}

// no error return if row is not found
func (gkDbCon *GkDbConDef) GetContextUser(userName string) (*DbContextUserDef, bool, *gkerr.GkErrDef) {
	var stmt *sql.Stmt
	var err error
	var dbContextUser *DbContextUserDef = new(DbContextUserDef)
	var rowFound bool

	stmt, err = gkDbCon.sqlDb.Prepare("select context_users.id, context_users.last_position_x, context_users.last_position_y, context_users.last_position_z, context_users.last_pod from users, context_users where users.id = context_users.id and users.user_name = $1")
	if err != nil {
		return nil, false, gkerr.GenGkErr("sql.Prepare"+getDatabaseErrorMessage(err), err, ERROR_ID_PREPARE)
	}

	defer stmt.Close()

	var rows *sql.Rows

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

	defer rows.Close()

	if rows.Next() {
		err = rows.Scan(&dbContextUser.id, &dbContextUser.LastPositionX, &dbContextUser.LastPositionY, &dbContextUser.LastPositionZ, &dbContextUser.LastPod)
		if err != nil {
			return nil, false, gkerr.GenGkErr("rows.Scan"+getDatabaseErrorMessage(err), err, ERROR_ID_ROWS_SCAN)
		}
		rowFound = true
	} else {
		rowFound = false
	}

	return dbContextUser, rowFound, nil
}