Golang Tx.Prepare方法代碼示例

func insertTimeSection(tx *sql.Tx, centerId, startTime, endTime string) (id int64, err error) {

	sql := "insert into time_section(center_id,start_time,end_time,lesson_no) values(?,?,?,?)"
	lessgo.Log.Debug(sql)
	stmt, err := tx.Prepare(sql)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	res, err := stmt.Exec(centerId, startTime, endTime, "1")

	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	newTimeSectionId, err := res.LastInsertId()
	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	return newTimeSectionId, nil
}

func (stor *Storage) insertBinaryAnnotations(tx *sql.Tx, span *models.Span) error {
	stmt, err := tx.Prepare(`INSERT INTO zipkin_annotations VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`)
	if err != nil {
		return fmt.Errorf("prepare: %+v", err)
	}
	defer stmt.Close()

	for _, ann := range span.BinaryAnnotations {
		// BinaryAnnotation.Value is base64, see: https://github.com/openzipkin/zipkin/blob/master/zipkin-common/src/main/scala/com/twitter/zipkin/json/JsonBinaryAnnotation.scala
		value, err := base64str2sql(ann.Value)
		if err != nil {
			return fmt.Errorf("parse value: %+v", value)
		}

		ipv4, port, service := ep2sql(ann.Endpoint)
		_, err = stmt.Exec(
			hex2int64(span.TraceID),
			hex2int64(span.ID),
			ann.Key,
			value,
			ann.AnnotationType,
			span.Timestamp,
			ipv4, port, service,
		)
		if err != nil {
			return err
		}
	}
	return nil
}

//	保存錯誤信息
func saveError(tx *sql.Tx, date, message string) error {

	stmt, err := tx.Prepare("replace into error values(?,?)")
	if err != nil {
		return err
	}
	defer stmt.Close()

	//	新增
	result, err := stmt.Exec(date, message)
	if err != nil {
		return err
	}

	ra, err := result.RowsAffected()
	if err != nil {
		return err
	}

	if ra == 0 {
		return sql.ErrNoRows
	}

	return nil
}

func insertVideoContributorRelation(tx *sql.Tx, videoId string, contributorId string) {
	// 存在チェック
	stmt, stmtErr := tx.Prepare("SELECT count(video_id) FROM videos_contributors WHERE video_id = ? AND contributor_id = ?")
	if stmtErr != nil {
		panic(stmtErr.Error())
	}
	defer stmt.Close()

	var count int
	err := stmt.QueryRow(videoId, contributorId).Scan(&count)
	if err != nil {
		panic(err.Error())
	}
	defer stmt.Close()

	if count > 0 {
		return
	}

	stmtIns, stmtInsErr := tx.Prepare("INSERT INTO videos_contributors (video_id, contributor_id) VALUES(?, ?)")
	if stmtInsErr != nil {
		panic(stmtInsErr.Error())
	}
	defer stmtIns.Close()

	_, insErr := stmtIns.Exec(videoId, contributorId)
	if insErr != nil {
		panic(insErr.Error())
	}
	defer stmtIns.Close()
}

func insertCourse(tx *sql.Tx, centerId, courseName, courseType string) (id int64, err error) {

	sql := "insert into course(name,center_id,price,is_probation,type,begin_age,end_age,intro,app_display_level,create_time,lesson_num) values(?,?,?,?,?,?,?,?,?,?,?)"
	lessgo.Log.Debug(sql)
	stmt, err := tx.Prepare(sql)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	res, err := stmt.Exec(courseName, centerId, "0", "0", courseType, "0", "60", courseName, "0", time.Now().Format("20060102150405"), "12")

	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	newCourseId, err := res.LastInsertId()
	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	return newCourseId, nil
}

func insertConsumerStatusLog(tx *sql.Tx, consumerId, employeeId, oldStatus, newStatus string) (id int64, err error) {

	sql := "insert into consumer_status_log(consumer_id,employee_id,create_time,old_status,new_status) values(?,?,?,?,?)"
	lessgo.Log.Debug(sql)
	stmt, err := tx.Prepare(sql)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	res, err := stmt.Exec(consumerId, employeeId, time.Now().Format("20060102150405"), oldStatus, newStatus)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	logId, err := res.LastInsertId()

	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	return logId, err
}

func StoreOIP041Artifact(o Oip041, txid string, block int, dbtx *sql.Tx) error {
	// store in database
	stmtStr := `INSERT INTO 'oip_artifact'
		('active','block','json','tags','timestamp',
		'title','txid','type','year','publisher')
		VALUES (?,?,?,?,?,?,?,?,?,?);`

	stmt, err := dbtx.Prepare(stmtStr)
	if err != nil {
		return err
	}
	defer stmt.Close()

	s, err := o.GetJSON()
	if err != nil {
		return nil
	}

	_, err = stmt.Exec(1, block, s, strings.Join(o.Artifact.Info.ExtraInfo.Tags, ","),
		o.Artifact.Timestamp, o.Artifact.Info.Title, txid, o.Artifact.Type,
		o.Artifact.Info.Year, o.Artifact.Publisher)
	if err != nil {
		return err
	}

	return nil
}

func APIGetAllOIP041(dbtx *sql.Tx) ([]Oip041ArtifactAPIResult, error) {
	stmtStr := `select a.block, a.json, a.tags, a.timestamp,
				a.title, a.txid, a.type, a.year, a.publisher
				from oip_artifact as a join publisher as p
				where p.address = a.publisher and a.invalidated = 0`

	stmt, err := dbtx.Prepare(stmtStr)
	if err != nil {
		return []Oip041ArtifactAPIResult{}, err
	}
	defer stmt.Close()

	rows, err := stmt.Query()
	if err != nil {
		return []Oip041ArtifactAPIResult{}, err
	}

	var results []Oip041ArtifactAPIResult

	for rows.Next() {
		var a Oip041ArtifactAPIResult
		var s string

		rows.Scan(&a.Block, &s, &a.Tags, &a.Timestamp,
			&a.Title, &a.TxID, &a.Type, &a.Year, &a.Publisher)

		json.Unmarshal([]byte(s), &a.OIP041)
		results = append(results, a)
	}

	stmt.Close()
	rows.Close()

	return results, nil
}

// 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()

}

// Up is executed when this migration is applied
func Up_20160429102901(txn *sql.Tx) {
	var sql string = "create table if not exists users ( " +
		"id int(10) unsigned not null auto_increment, " +
		"created_at int(13) unsigned not null, " +
		"updated_at int(13) unsigned not null, " +
		"name varchar(100) not null unique, " +
		"screen_name varchar(100), " +
		"mail varchar(100) not null unique, " +
		"phone varchar(100) unique, " +
		"avatar varchar(200), " +
		"gender varchar(10), " +
		"birth int(13), " +
		"description text, " +
		"type varchar(16), " +
		"password varchar(100), " +
		"last_login_at int(13), " +
		"PRIMARY KEY(id) " + ")"
	stmt, err := txn.Prepare(sql)
	if err != nil {
		panic(err.Error())
	}
	_, err = stmt.Exec()

	if err != nil {
		panic(err.Error())
	}
}

func insertParent(tx *sql.Tx, name, password, telephone, comeForm string) (id int64, err error) {

	sql := "insert into parent(name,password,telephone,reg_date,come_form) values(?,?,?,?,?)"
	lessgo.Log.Debug(sql)
	stmt, err := tx.Prepare(sql)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	res, err := stmt.Exec(name, password, telephone, time.Now().Format("20060102150405"), comeForm)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	newParentId, err := res.LastInsertId()
	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	return newParentId, nil
}

func insertConsumerContactsLog(tx *sql.Tx, createUser, note, consumerId, contactsType string) (id int64, err error) {

	sql := "insert into consumer_contact_log(create_user,create_time,note,consumer_id,type) values(?,?,?,?,?) "
	lessgo.Log.Debug(sql)
	stmt, err := tx.Prepare(sql)
	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	res, err := stmt.Exec(createUser, time.Now().Format("20060102150405"), note, consumerId, contactsType)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	consumerContactsLogId, err := res.LastInsertId()

	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	return consumerContactsLogId, nil
}

func insertConsumer(tx *sql.Tx, centerId, contactStatus, homePhone, child, year, month, birthday, lastContactTime, comeFromId, createUser, parttimeName string) (id int64, err error) {

	sql := "insert into consumer_new(center_id,contact_status,home_phone,create_time,child,year,month,birthday,last_contact_time,come_from_id,create_user_id,parttime_name) values(?,?,?,?,?,?,?,?,?,?,?,?)"
	lessgo.Log.Debug(sql)
	stmt, err := tx.Prepare(sql)
	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	res, err := stmt.Exec(centerId, contactStatus, homePhone, time.Now().Format("20060102150405"), child, year, month, birthday, lastContactTime, comeFromId, createUser, parttimeName)

	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	consumerId, err := res.LastInsertId()

	if err != nil {
		lessgo.Log.Error(err.Error())
		return 0, err
	}

	return consumerId, err
}

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 (s *SQLEngine) consumeMsgIDsToDismiss(tx *sql.Tx, u gregor.UID, mid gregor.MsgID, dmids []gregor.MsgID, ctime time.Time) error {

	// early out if no msg IDs to dismiss were specified
	if len(dmids) == 0 {
		return nil
	}

	ins, err := tx.Prepare("INSERT INTO gregor_dismissals_by_id(uid, msgid, dmsgid) VALUES(?, ?, ?)")
	if err != nil {
		return err
	}
	defer ins.Close()
	upd, err := tx.Prepare("UPDATE gregor_items SET dtime=? WHERE uid=? AND msgid=?")
	if err != nil {
		return err
	}
	defer upd.Close()

	ctimeArg := s.newQueryBuilder().TimeArg(ctime)
	hexUID := hexEnc(u)
	hexMID := hexEnc(mid)

	for _, dmid := range dmids {
		_, err = ins.Exec(hexUID, hexMID, hexEnc(dmid))
		if err != nil {
			return err
		}
		_, err = upd.Exec(ctimeArg, hexUID, hexEnc(dmid))
		if err != nil {
			return err
		}
	}
	return err
}