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 }
func LookupContributedBarcode(stmt *sql.Stmt, code string) ([]*BARCODE, error) { results := make([]*BARCODE, 0) rows, err := stmt.Query(code) if err != nil { return results, err } defer rows.Close() for rows.Next() { var ( i, n, d, a sql.NullString e sql.NullBool ) err := rows.Scan(&i, &n, &d, &e, &a) if err != nil { return results, err } else { result := new(BARCODE) result.Barcode = code result.Uuid = i.String result.ProductName = n.String result.GtinEdit = e.Bool result.AccountID = a.String if d.Valid { result.ProductDesc = d.String } results = append(results, result) } } return results, nil }
func getEntriesFromSth(stmt *sql.Stmt) (el []Entry, err error) { rows, err := stmt.Query() if err != nil { glog.Errorf("stmt.Query() %s", err) return el, err } return getEntriesFromRows(rows) }
func getEntriesFromSthP(stmt *sql.Stmt, p string) (el []Entry, err error) { rows, err := stmt.Query(p) if err != nil { glog.Errorf("getEntriesFromSthP.Query(%s): %s", p, err) return el, err } return getEntriesFromRows(rows) }
// getMealTagsPrepared fetches the list of tags associated with the meal // identified by 'mealID'. 'stmt' is assumed to be a prepared statement compiled // from "SELECT tag.tag FROM tag WHERE tag.mealid = ?". func getMealTagsPrepared(stmt *sql.Stmt, mealID uint64) (tags []string, err error) { rows, err := stmt.Query(mealID) if err != nil { return nil, err } defer rows.Close() return readTags(rows) }
func (mysql *Mysql) Query(args ...interface{}) ([]interface{}, error) { // Parse sql q, param, err := mysql.parseQuery(args...) if err != nil { return nil, err } var stmt *sql.Stmt // Prepare if mysql.Tx != nil { stmt, err = mysql.Tx.Prepare(q) } else { stmt, err = mysql.Db.Prepare(q) } if err != nil { return nil, err } defer stmt.Close() // ------------- // Scan // ------------- rows, err := stmt.Query(param...) if err != nil { return nil, err } defer rows.Close() // Get all col name cols, err := rows.Columns() if err != nil { return nil, err } // Scan value out := make([]interface{}, 0) for rows.Next() { rbs := make([]sql.RawBytes, len(cols)) vals := make([]interface{}, len(cols)) for i := range vals { vals[i] = &rbs[i] } if err = rows.Scan(vals...); err != nil { return nil, err } // Make map r := make(map[string]interface{}, len(cols)) for i, val := range rbs { r[cols[i]] = string(val) } out = append(out, r) } return out, nil }
func RawReadSlice(b *B) { var m *Model var stmt *sql.Stmt wrapExecute(b, func() { var err error initDB() m = NewModel() for i := 0; i < 100; i++ { err = rawInsert(m) if err != nil { fmt.Println(err) b.FailNow() } } stmt, err = raw.Prepare(rawSelectMultiSQL) if err != nil { fmt.Println(err) b.FailNow() } }) defer stmt.Close() for i := 0; i < b.N; i++ { var j int models := make([]Model, 100) rows, err := stmt.Query() if err != nil { fmt.Println(err) b.FailNow() } for j = 0; rows.Next() && j < len(models); j++ { err = rows.Scan( &models[j].Id, &models[j].Name, &models[j].Title, &models[j].Fax, &models[j].Web, &models[j].Age, &models[j].Right, &models[j].Counter, ) if err != nil { fmt.Println(err) b.FailNow() } } models = models[:j] if err = rows.Err(); err != nil { fmt.Println(err) b.FailNow() } if err = rows.Close(); err != nil { fmt.Println(err) b.FailNow() } } }
func DailySalesData_alldata(startTime string, excludeYangYi bool) (model.ProductSales, 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 DATE_FORMAT(o.create_time, '%Y-%m-%d'), sum(od.quantity) from order_detail od left join ` + "`" + `order` + "`" + ` o on od.order_track_number = o.track_number where o.type in (?,?) and o.status in (?,?,?,?) and o.create_time >= ? and od.product_id <> ? group by DATE_FORMAT(o.create_time, '%Y-%m-%d') order by DATE_FORMAT(o.create_time, '%Y-%m-%d') asc ` if stmt, err = conn.Prepare(_sql); err != nil { return nil, err } defer stmt.Close() var excluded_product_id = 0 if excludeYangYi { excluded_product_id = base.STAT_EXCLUDED_PRODUCT } rows, err := stmt.Query( model.Wholesale, model.SubOrder, // model.ShippingInstead, // 查子订单 "toprint", "todeliver", "delivering", "done", startTime, excluded_product_id, ) if db.Err(err) { return nil, err } defer rows.Close() // db.CloseRows(rows) // use db.CloseRows or rows.Close()? Is rows always nun-nil? ps := model.ProductSales{} for rows.Next() { p := new(model.SalesNode) rows.Scan(&p.Key, &p.Value) ps = append(ps, p) } return ps, nil }
func SettleAccount(startTime, endTime time.Time, factoryId int64) (*model.ProductSalesTable, 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() startTime = startTime.UTC().Truncate(time.Hour * 24) endTime = endTime.UTC().Truncate(time.Hour*24).AddDate(0, 0, 1) _sql := ` select product_id, sum(stock), send_time from inventory i where send_time >= ? and send_time <= ? and provider_id = ? group by DATE_FORMAT(send_time, '%Y-%m-%d'), product_id ` if stmt, err = conn.Prepare(_sql); err != nil { return nil, err } defer stmt.Close() // now := time.Now() rows, err := stmt.Query( startTime, endTime, factoryId, // model.Wholesale, model.SubOrder, // model.ShippingInstead, // 查子订单 // "toprint", "todeliver", "delivering", "done", // base.STAT_EXCLUDED_PRODUCT, // startTime, // startTime, ) if db.Err(err) { return nil, err } defer rows.Close() // db.CloseRows(rows) // use db.CloseRows or rows.Close()? Is rows always nun-nil? // the final result ps := model.NewProductSalesTable() var ( productId int64 stock int send_time time.Time ) for rows.Next() { rows.Scan(&productId, &stock, &send_time) ps.Set(send_time.Format("2006-01-02"), productId, stock) } return ps, nil }
func prepareSelect(db *sql.DB, query string, parameters ...interface{}) *sql.Rows { var err error var stmt *sql.Stmt var rows *sql.Rows stmt, err = db.Prepare(query) rows, err = stmt.Query(parameters...) if err != nil { log.Fatalf("Select error %q\n", err) } return rows }
func quotesExistForPastYear(stmt *sql.Stmt, symbol string, year int) bool { start_dt := strings.Join([]string{strconv.Itoa(year), "01", "01"}, "-") end_dt := strings.Join([]string{strconv.Itoa(year), "12", "31"}, "-") rows, err := stmt.Query(symbol, start_dt, end_dt) defer rows.Close() if err != nil { fmt.Printf("error running select query for symbol and year %s\n", err.Error()) return false } return rows.Next() }
// -------------------------------------------------------------------------------- // Product's top buyer list. In product/detail page. func ProductBestBuyerList(productId int) (model.BestBuyerList, 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 -- d.product_id, p.Name, o.customer_id, pp.Name, sum(d.quantity), d.selling_price ` + "from `order` o " + ` right join order_detail d on d.order_track_number = o.track_number left join product p on d.product_id = p.Id left join person pp on o.customer_id=pp.Id where -- and o.customer_id = 305 d.product_id = ? and o.type in (?,?) and o.status in (?,?,?,?) -- and o.create_time >= "2015-08-14" -- and o.create_time < "2015-03-23 23:55:55" -- and o.track_number=1501161519337773 -- debug group BY d.product_id, o.customer_id order by sum(d.quantity) desc ` if stmt, err = conn.Prepare(_sql); err != nil { return nil, err } defer stmt.Close() rows, err := stmt.Query( productId, model.Wholesale, model.SubOrder, // model.ShippingInstead, // 查子订单 "toprint", "todeliver", "delivering", "done", ) if db.Err(err) { return nil, err } defer rows.Close() // db.CloseRows(rows) // use db.CloseRows or rows.Close()? Is rows always nun-nil? ps := model.BestBuyerList{} for rows.Next() { p := new(model.BestBuyerListItem) rows.Scan(&p.CustomerId, &p.CustomerName, &p.Quantity, &p.SalePrice) ps = append(ps, p) } return ps, nil }
func GetFamilyNames(db *sql.DB, table_name string, like string) ([]string, error) { defer recoverDbPanic() var stmt *sql.Stmt like = like + "%" family_names := []string{} switch table_name { case "coral": stmt = getStatement("coral_family_list", CORAL_FAMILY_NAMES_LIST_QUERY, db) case "invertebrate": stmt = getStatement("invertebrate_family_list", INVERTEBRATE_FAMILY_NAMES_LIST_QUERY, db) case "inhabitant": stmt = getStatement("inhabitant_family_list", INHABITANT_FAMILY_NAMES_LIST_QUERY, db) case "plant": stmt = getStatement("plant_family_list", PLANT_FAMILY_NAMES_LIST_QUERY, db) break } if stmt == nil { return family_names, errors.New("Query could not be created.") } rows, err := stmt.Query(like) defer rows.Close() if err != nil { log.Printf("Query err: %v\n", err) return family_names, err } for rows.Next() { family_name := "" err = rows.Scan( &family_name, ) if err != nil { log.Printf("Scan err: %v\n", err) return family_names, err } family_names = append(family_names, family_name) } return family_names, err }
func (this *mysql) Query(query string, args ...interface{}) (rows *sql.Rows, err error) { if this.db == nil { return nil, ErrNotOpen } if this.breaker.Open() { return nil, ErrCircuitOpen } var stmt *sql.Stmt = nil if this.stmtsStore != nil { if stmtc, present := this.stmtsStore.Get(query); present { stmt = stmtc.(*sql.Stmt) } else { // FIXME thundering hurd stmt, err = this.db.Prepare(query) if err != nil { if this.isSystemError(err) { log.Warn("mysql prepare breaks: %s", err.Error()) this.breaker.Fail() } return nil, err } this.mutex.Lock() this.stmtsStore.Set(query, stmt) this.mutex.Unlock() log.Debug("[%s] stmt[%s] open", this.dsn, query) } } // Under the hood, db.Query() actually prepares, executes, and closes // a prepared statement. That's three round-trips to the database. if stmt != nil { rows, err = stmt.Query(args...) } else { rows, err = this.db.Query(query, args...) } if err != nil { if this.isSystemError(err) { log.Warn("mysql query breaks: %s", err.Error()) this.breaker.Fail() } } else { this.breaker.Succeed() } return }
func getNumThings(getCount *sql.Stmt) (int64, error) { r, err := getCount.Query() if err != nil { return -1, err } if !r.Next() { return -1, fmt.Errorf("Empty scan returned while querying %v", getCount) } var count int64 if err := r.Scan(&count); err != nil { return -1, err } return count, nil }
func selectThingID(getID *sql.Stmt, thing interface{}) (int64, error) { rows, err := getID.Query(thing) if err != nil { return -1, err } if !rows.Next() { return -1, fmt.Errorf("couldn't look up ID for %v", thing) } var id int64 if err = rows.Scan(&id); err != nil { return -1, err } return id, nil }