Golang Stmt.Exec方法代码示例

func SaveAttrs(log *logging.Logger, tx *sql.Tx, section *common.Section, metadata common.JSONFile) error {
	var stmt *sql.Stmt
	var err error

	var insert = "INSERT INTO attrs" +
		"(area, grace, dataset, element, os, username, groupname, type," +
		" link, mtime, ctime, hash, perms, compressed)" +
		" VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)"

	stmt, err = tx.Prepare(insert)
	if err != nil {
		log.Debug("Failed prepare: " + err.Error())
		return errorMsg(log, 3, "Cannot save Attributes for element "+section.Name)
	}

	_, err = stmt.Exec(section.Name, section.Grace, section.Dataset, metadata.Name,
		metadata.Os, metadata.User, metadata.Group, metadata.Type, metadata.Link,
		metadata.Mtime, metadata.Ctime, metadata.Hash, metadata.Mode, section.Compressed)

	if err != nil {
		log.Debug("Failed execute: " + err.Error())
		return errorMsg(log, 4, "Cannot save Attributes for element "+section.Name)
	}

	stmt.Close()
	return nil
}

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
}

func ExecuteImpInsert(stmt *sql.Stmt, var_imp map[string]interface{}) error {
	var err error
	if _, ok := var_imp["pred"].(float64); ok {
		_, err = stmt.Exec(var_imp["GLB_OE_ID"].(float64),
			var_imp["CLM_BUS_ID"].(string),
			var_imp["MODEL"].(string),
			var_imp["MODEL_DESC"].(string),
			var_imp["VAR"].(string),
			var_imp["VALUE"].(string),
			var_imp["WEIGHT"].(string),
			var_imp["RANK"].(float64),
			var_imp["pred"].(float64),
			var_imp["CREATE_DT_TS"].(float64))
	} else {
		_, err = stmt.Exec(var_imp["GLB_OE_ID"].(float64),
			var_imp["CLM_BUS_ID"].(string),
			var_imp["MODEL"].(string),
			var_imp["MODEL_DESC"].(string),
			var_imp["VAR"].(string),
			var_imp["VALUE"].(string),
			nil,
			nil,
			nil,
			var_imp["CREATE_DT_TS"].(float64))
	}
	check(err)
	return err
}

func (follow Follow) Persist() error {
	var stmtIns *sql.Stmt
	var err error

	if follow.id == 0 {
		stmtIns, err = database.Prepare("INSERT INTO " + _TABLE_FOLLOW + "(userId, userName, status, followDate, unfollowDate, lastAction) VALUES( ?, ?, ?, ?, ? ,?)")
	} else {
		stmtIns, err = database.Prepare("UPDATE " + _TABLE_FOLLOW + " SET userId = ?, userName = ?, status = ?, followDate = ?, unfollowDate = ?, lastAction = ? WHERE id = ?")
	}

	if err != nil {
		return err
	}

	defer stmtIns.Close()

	unfollowDate := mysql.NullTime{Time: follow.UnfollowDate, Valid: !follow.UnfollowDate.IsZero()}

	if follow.id == 0 {
		_, err = stmtIns.Exec(follow.UserId, follow.UserName, follow.Status, follow.FollowDate, unfollowDate, time.Now())
	} else {
		_, err = stmtIns.Exec(follow.UserId, follow.UserName, follow.Status, follow.FollowDate, unfollowDate, follow.LastAction, follow.id)
	}

	return err
}

// Exec executes a prepared statement with the given arguments and returns a Result
// summarizing the effect of the statement.
//
// Exec prepares the same statement on another connection and executes it
func (s *Stmt) Exec(ctx context.Context, args ...interface{}) (sql.Result, error) {
	if s.err != nil {
		return nil, s.err
	}

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

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

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

		res, err = stmt.Exec(args...)

	}

	if opErr := s.db.process(ctx, f, done); opErr != nil {
		return nil, opErr
	}

	return res, err
}

/* **
执行删除操作
-----------------------

param1: 要操作的数据库表
param2: 条件语句，当没有条件时填空
param3: 对应条件传的参数

return 成功返回nil，失败返回error
** */
func (self *MDB) execDelete(tableName, condition string, args ...interface{}) error {
	if self == nil {
		return errors.New("数据库句柄错误。")
	}
	if tableName == "" {
		return errors.New("数据库表名不能为空。")
	}
	tx, err := self.Begin()
	if err != nil {
		return err
	}
	var stmt *sql.Stmt
	if condition == "" || len(args) == 0 {
		stmt, err = tx.Prepare(fmt.Sprintf("DELETE FROM %s", tableName))
	} else {
		stmt, err = tx.Prepare(fmt.Sprintf("DELETE FROM %s WHERE %s", tableName, condition))
	}
	if err != nil {
		return err
	}
	if _, err = stmt.Exec(args...); err != nil {
		return err
	}
	defer stmt.Close()
	if err = tx.Commit(); err != nil {
		// 回滚？？？？用得着么
		tx.Rollback()
		return err
	}
	return nil
}

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

	for i := 0; i < b.N; i++ {
		res, err := stmt.Exec(m.Name, m.Title, m.Fax, m.Web, m.Age, m.Right, m.Counter)
		if err != nil {
			fmt.Println(err)
			b.FailNow()
		}
		id, err := res.LastInsertId()
		m.Id = int(id)
		if err != nil {
			fmt.Println(err)
			b.FailNow()
		}
	}
}

func (gkDbCon *GkDbConDef) AddNewUser(userName string, passwordHash string, passwordSalt string, email string) *gkerr.GkErrDef {

	var stmt *sql.Stmt
	var err error

	var id int64
	var gkErr *gkerr.GkErrDef

	id, gkErr = gkDbCon.getNextUsersId()
	if gkErr != nil {
		return gkErr
	}

	stmt, err = gkDbCon.sqlDb.Prepare("insert into users (id, user_name, password_hash, password_salt, email, account_creation_date, last_login_date) values ($1, $2, $3, $4, $5, $6, $7)")
	if err != nil {
		return gkerr.GenGkErr("stmt.Prepare"+getDatabaseErrorMessage(err), err, ERROR_ID_PREPARE)
	}

	defer stmt.Close()

	var accountCreationDate time.Time = time.Now()
	var lastLoginDate time.Time = time.Now()

	_, err = stmt.Exec(id, userName, passwordHash, passwordSalt, email, accountCreationDate, lastLoginDate)
	if err != nil {
		if isUniqueViolation(err) {
			return gkerr.GenGkErr("stmt.Exec unique violation", err, ERROR_ID_UNIQUE_VIOLATION)
		}
		return gkerr.GenGkErr("stmt.Exec"+getDatabaseErrorMessage(err), err, ERROR_ID_EXECUTE)
	}

	return nil
}

func (p *TxScope) ExecStmt(stmt *sql.Stmt, args ...interface{}) int64 {
	if r, err := stmt.Exec(args...); err != nil {
		panic(err)
	} else {
		return p.getAffectedRows(r)
	}
}

func (fav Favorite) Persist() error {
	var stmtIns *sql.Stmt
	var err error

	if fav.id == 0 {
		stmtIns, err = database.Prepare("INSERT INTO " + _TABLE_FAVORITE + "(userId, userName, tweetId, status, favDate, unfavDate, lastAction) VALUES( ?, ?, ?, ?, ?, ?, ?)")
	} else {
		stmtIns, err = database.Prepare("UPDATE " + _TABLE_FAVORITE + " SET userId = ?, userName = ?, tweetId = ?, status = ?, favDate = ?, unfavDate = ?, lastAction = ? WHERE id = ?")
	}

	if err != nil {
		return err
	}

	defer stmtIns.Close()

	unfavDate := mysql.NullTime{Time: fav.UnfavDate, Valid: !fav.UnfavDate.IsZero()}

	if fav.id == 0 {
		_, err = stmtIns.Exec(fav.UserId, fav.UserName, fav.TweetId, fav.Status, fav.FavDate, unfavDate, time.Now())
	} else {
		_, err = stmtIns.Exec(fav.UserId, fav.UserName, fav.TweetId, fav.Status, fav.FavDate, unfavDate, fav.LastAction, fav.id)
	}

	return err
}

// Delete will delete all of the configurations whose name is in the list
// of names in the arugment. It will not return an error if the name is not found.
func (cc *ConfigurationController) Delete(names ...string) (err error) {
	var (
		tx   *sql.Tx
		stmt *sql.Stmt
	)
	tx, err = cc.DB.Begin()
	if err != nil {
		tx.Rollback()
		return err
	}

	stmt, err = tx.Prepare("DELETE FROM configurations where config_name = $1")
	if err != nil {
		tx.Rollback()
		return err
	}

	for _, name := range names {
		_, err := stmt.Exec(name)

		if err == sql.ErrNoRows {
			err = nil
		}
		if err != nil {
			tx.Rollback()
			return err
		}
	}

	return tx.Commit()

}

func (c *Comment) Save(db *sql.DB) error {
	var stmt *sql.Stmt
	var err error
	if c.Id != 0 {
		stmt, err = db.Prepare(`
			UPDATE comments SET
			Email = ?, Name = ?, Body = ?, Created = ?, Url = ?, ClientIp = ?, Approved = ? WHERE Id = ?`)
		if err != nil {
			return err
		}
		_, err = stmt.Exec(c.Email, c.Name, c.Body, c.Created, c.Url, c.ClientIp, c.Approved, c.Id)
	} else {
		stmt, err = db.Prepare(`
			INSERT INTO
			comments(Email, Name, Body, Created, Url, ClientIp, Approved)
			VALUES(?, ?, ?, ?, ?, ?, ?)`)
		if err != nil {
			return err
		}
		res, err := stmt.Exec(c.Email, c.Name, c.Body, c.Created, c.Url, c.ClientIp, c.Approved)
		if err != nil {
			return err
		}
		lastId, err := res.LastInsertId()
		if err != nil {
			return err
		}
		c.Id = lastId
	}
	if err != nil {
		return err
	}
	return nil
}

func (self *Params) Delete() (res sql.Result, err error) {

	db, query := self.getWriteConnect()
	sqls, val := query.Delete()
	if debug_sql {
		Debug.Println("delete  ", sqls, val)
	}
	if OpenSyncDelete {
		sqls = strings.Replace(sqls, "?", "%v", -1)
		sqls = fmt.Sprintf(sqls, val...)
		SqlSyncHook <- sqls
		return nil, nil
	} else {
		var stmt *sql.Stmt
		stmt, err = db.Prepare(sqls)
		if err == nil {
			defer stmt.Close()
		} else {
			Error.Println(err)
			return
		}
		res, err = stmt.Exec(val...)
		if err != nil {
			Error.Println(err)
		}
	}
	return
}

// Delete executes DELETE FROM value.TableName where .Where()
// Calling .Where is mandatory. You can pass a nil pointer to value if you just setted
// the tablename with Model.
func (db *Database) Delete(value DBModel) error {
	defer db.clear()
	// if Model has been called, skip table name inference procedure
	if len(db.tables) == 0 {
		db.tables = append(db.tables, handleIdentifier(value.TableName()))
		db.models = append(db.models, value)
	}

	// If where is empty, try to infer a primary key by value
	// otherwise buildDelete panics (where is mandatory)
	db = db.Where(value)

	// Compile query
	var stmt *sql.Stmt
	var err error
	if stmt, err = db.db.Prepare(db.buildDelete()); err != nil {
		return err
	}

	// Pass query parameters and executes the query
	if _, err = stmt.Exec(db.whereValues...); err != nil {
		return err
	}

	// Clear fields of value after delete, because the object no more exists
	value = reflect.Zero(reflect.ValueOf(value).Type()).Interface().(DBModel)

	return nil
}

func SaveAcls(log *logging.Logger, tx *sql.Tx, section *common.Section, element string, acls []common.JSONFileAcl) error {
	var acl common.JSONFileAcl
	var stmt *sql.Stmt
	var err error

	var insert = "INSERT INTO acls" +
		"(area, grace, dataset, element, name, type, perms)" +
		" VALUES($1, $2, $3, $4, $5, $6, $7)"

	stmt, err = tx.Prepare(insert)
	if err != nil {
		log.Debug("Failed prepare: " + err.Error())
		return errorMsg(log, 1, "Cannot save ACLs for element "+section.Name)
	}

	for _, acl = range acls {
		if acl.User != "" {
			_, err = stmt.Exec(section.Name, section.Grace, section.Dataset, element,
				acl.User, "user", acl.Mode)
		} else {
			_, err = stmt.Exec(section.Name, section.Grace, section.Dataset, element,
				acl.Group, "group", acl.Mode)
		}
		if err != nil {
			log.Debug("Failed execute: " + err.Error())
			return errorMsg(log, 2, "Cannot save ACLs for element "+section.Name)
		}
	}

	stmt.Close()
	return nil
}