コード例 #1
0
ファイル: web.go プロジェクト: andrewsomething/go_test
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
}
コード例 #2
0
ファイル: testPostgres.go プロジェクト: sunvim/codelab
func push(db *sql.DB, iter, routine int, queryStr string) {
	defer timeTracker(time.Now(), "pump", routine)
	//fmt.Printf("StartTime: %v\n", time.Now())
	stmt, prepErr := db.Prepare(queryStr)
	if prepErr != nil {
		log.Fatal(prepErr)
	}
	defer stmt.Close()

	//var rowIds = []int64{}
	var rowNumCount int64
	for i := 0; i < iter; i++ {
		res, resErr := stmt.Exec("tester", time.Now(), i)
		if resErr != nil || res == nil {
			log.Fatal(resErr)
		}

		// count number of rows efected
		numRow, _ := res.RowsAffected()
		rowNumCount += numRow
		//lins, _ := res.LastInsertId()
		//rowIds = append(rowIds, lins)
		//lins, errLin := res.LastInsertId()
		//numRow, errRow := res.RowsAffected()
	}

	//fmt.Printf("Last ID: %d Number of rows: %d\n", rowIds[:len(rowIds)-1], rowNumCount)
	fmt.Printf("Number of rows: %d\n", rowNumCount)
	//fmt.Printf("StopTime: %v\n", time.Now())
	// cant do this cause we'll exit when first goroutine is done!
	//os.Exit(0)
}
コード例 #3
0
ファイル: operatedb.go プロジェクト: nuanri/hichat
func insert_session(db *sql.DB, user_id int, sid string) {
	row := db.QueryRow("select count(id) from auth_session  where user_id=?", user_id)
	var count int
	err := row.Scan(&count)
	if err != nil {
		fmt.Println(err)
	}
	if count > 0 {
		stmt, err := db.Prepare("update auth_session set sid=? where user_id=?")
		if err != nil {
			fmt.Println(err)
			return
		}
		stmt.Exec(sid, user_id)
		defer stmt.Close()
		return
	}
	stmt, err := db.Prepare("insert into auth_session(user_id, sid) values (?,?)")
	if err != nil {
		fmt.Println(err)
	}
	defer stmt.Close()
	if _, err := stmt.Exec(user_id, sid); err != nil {
		fmt.Println("smt.Exec failed: ", err)
	}
}
コード例 #4
0
ファイル: main.go プロジェクト: jovon/cf_martini_api
func FundHandler(rw http.ResponseWriter, r *http.Request, db *sql.DB, params martini.Params) {
	id := params["id"]
	sqlStr := "SELECT * FROM funds WHERE id=?"
	stmt, err := db.Prepare(sqlStr)
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()
	fund := new(Fund)
	err = stmt.QueryRow(id).Scan(&fund.Id, &fund.Name)
	if err != nil {
		if err == sql.ErrNoRows {
			rw.Header().Set("Status", "404")
			rw.Header().Set("Content-Type", "application/json; charset=utf-8")
			notFound := map[string]string{"error": "Not Found"}
			b, _ := json.Marshal(notFound)
			rw.Write(b)
		} else {
			log.Fatal(err)
		}
	} else {
		rw.Header().Set("Content-Type", "application/json; charset=utf-8")
		b, err := json.Marshal(fund)
		if err != nil {
			log.Fatal(err)
		}
		rw.Write(b)
	}
}
コード例 #5
0
ファイル: users.go プロジェクト: mtStats/track
// Save the User data to the DB
func SaveUser(db *sql.DB, u User) (id int64, err error) {

	// Prepare Insert Statement
	stmt, err := db.Prepare(`
		INSERT
			tbl_users
		SET
			steam64=?,
			steam32=?,
			name=?
	`)
	if err != nil {
		fmt.Println(err)
	}

	defer stmt.Close()

	res, err := stmt.Exec(
		u.Steam64,
		u.Steam32,
		u.Name,
	)

	if err != nil {
		fmt.Println(err)
	}

	id, err = res.LastInsertId()
	if err != nil {
		fmt.Println(err)
	}

	return id, err

}
コード例 #6
0
func mustPrepare(db *sql.DB, query string) *sql.Stmt {
	stmt, err := db.Prepare(query)
	if err != nil {
		log.Fatalf("Error when preparing statement %q: %s", query, err)
	}
	return stmt
}
コード例 #7
0
ファイル: postgres.go プロジェクト: RapidMVP/pgfutter
//tries to create the schema and ignores failures to do so.
//versions after Postgres 9.3 support the "IF NOT EXISTS" sql syntax
func tryCreateSchema(db *sql.DB, importSchema string) {
	createSchema, err := db.Prepare(fmt.Sprintf("CREATE SCHEMA %s", importSchema))

	if err == nil {
		createSchema.Exec()
	}
}
コード例 #8
0
ファイル: user.go プロジェクト: ErikBrown/5sur
func returnRideHistory(db *sql.DB, u int) (map[string]int, map[string]int, error) {
	result := make(map[string]int)
	result2 := make(map[string]int)

	stmt, err := db.Prepare(`
		SELECT year, sum(rides_given), sum(rides_taken)
			FROM ride_history 
			WHERE user_id = ? 
			GROUP BY year;
	`)

	if err != nil {
		return result, result2, util.NewError(err, "Error de la base de datos", 500)
	}
	defer stmt.Close()

	rows, err := stmt.Query(u)
	if err != nil {
		return result, result2, util.NewError(err, "Error de la base de datos", 500)
	}
	defer rows.Close()

	for rows.Next() {
		year := ""
		var given, taken int
		err := rows.Scan(&year, &given, &taken)
		if err != nil {
			return result, result2, util.NewError(err, "Error de la base de datos", 500)
		}
		result[year] = given
		result2[year] = taken
	}

	return result, result2, nil
}
コード例 #9
0
ファイル: user.go プロジェクト: ErikBrown/5sur
func returnUserComments(db *sql.DB, u int) ([]Comment, error) {
	results := make([]Comment, 0)

	stmt, err := db.Prepare(`
		SELECT positive, DATE_FORMAT(date,'%d/%m/%Y'), comment
			FROM comments 
			WHERE user = ? 
			AND public = true;
	`)

	if err != nil {
		return results, util.NewError(err, "Error de la base de datos", 500)
	}
	defer stmt.Close()

	rows, err := stmt.Query(u)
	if err != nil {
		return results, util.NewError(err, "Error de la base de datos", 500)
	}
	defer rows.Close()

	for rows.Next() {
		comment := Comment{}
		err := rows.Scan(&comment.Positive, &comment.Date, &comment.Text)
		if err != nil {
			return results, util.NewError(err, "Error de la base de datos", 500)
		}
		results = append(results, comment)
	}

	return results, nil
}
コード例 #10
0
ファイル: postgres.go プロジェクト: lukasmartinelli/pgfutter
//create table with a single JSON or JSONB column data
func createJSONTable(db *sql.DB, schema string, tableName string, column string, dataType string) (*sql.Stmt, error) {
	fullyQualifiedTable := fmt.Sprintf("%s.%s", schema, tableName)
	tableSchema := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (%s %s)", fullyQualifiedTable, column, dataType)

	statement, err := db.Prepare(tableSchema)
	return statement, err
}
コード例 #11
0
ファイル: objs.go プロジェクト: hdonnay/vaultd
func dbInit(db *sql.DB) error {
	var err error
	_, err = db.Exec(initSQL)
	if err != nil {
		glog.Errorln("error intializing database")
		return err
	}
	q = map[string]*sql.Stmt{}
	prepare := map[string]string{
		"getUser":          "******",
		"getUserIdByName":  "SELECT id FROM users WHERE name = $1",
		"getGroup":         "SELECT name, userGroup FROM groups WHERE id = $1",
		"getGroupIdByName": "SELECT id FROM groups WHERE name = $1",
		"checkToken":       "SELECT EXISTS (SELECT 1 FROM session WHERE id = $1 AND token = $2 AND expire > current_timestamp);",
		"checkChallenge":   "SELECT EXISTS (SELECT 1 FROM session WHERE id = $1 AND challenge = $2 AND expire > current_timestamp);",
	}
	for name, query := range prepare {
		x, err := db.Prepare(query)
		if err != nil {
			glog.Errorf("error preparing: %s\t%s\n", name, query)
			return err
		}
		q[name] = x
	}
	return nil
}
コード例 #12
0
ファイル: group.go プロジェクト: ZhangTingkuo/im_service
func LoadAllGroup(db *sql.DB) (map[int64]*Group, error) {
	stmtIns, err := db.Prepare("SELECT id, appid, super FROM `group`")
	if err != nil {
		log.Info("error:", err)
		return nil, nil
	}

	defer stmtIns.Close()
	groups := make(map[int64]*Group)
	rows, err := stmtIns.Query()
	for rows.Next() {
		var id int64
		var appid int64
		var super int8
		rows.Scan(&id, &appid, &super)
		members, err := LoadGroupMember(db, id)
		if err != nil {
			log.Info("error:", err)
			continue
		}

		if super != 0 {
			group := NewSuperGroup(id, appid, members)
			groups[group.gid] = group
		} else {
			group := NewGroup(id, appid, members)
			groups[group.gid] = group
		}
	}
	return groups, nil
}
コード例 #13
0
ファイル: main.go プロジェクト: bussyjd/kodingchallenge
func WritePsql(username string, db *sql.DB) {
	statement, err := db.Prepare("INSERT INTO entry(username,created) VALUES($1,$2)")
	checkErr(err)
	_, err = statement.Exec(username, time.Now())
	checkErr(err)
	counts.Add("sql write", 1)
}
コード例 #14
0
func (self *catalogResource) executeInsert(db *sql.DB, query *string, args ...interface{}) (id int64, err error) {
	l := NewLogger("catalog_resource")

	defer func() {
		if r := recover(); r != nil {
			id = 0
			err = r.(error)

			l.Error("Could not insert record", "err", err)
		}
	}()

	stmt, err := db.Prepare(*query)
	if err != nil {
		panic(err)
	}

	res, err := stmt.Exec(args...)
	if err != nil {
		panic(err)
	}

	id, err = res.LastInsertId()
	if err != nil {
		panic(err)
	}

	return id, nil
}
コード例 #15
0
ファイル: murmurdb.go プロジェクト: carriercomm/grumble
// Link a Server's channels together
func populateChannelLinkInfo(server *Server, db *sql.DB) (err error) {
	stmt, err := db.Prepare("SELECT channel_id, link_id FROM channel_links WHERE server_id=?")
	if err != nil {
		return err
	}

	rows, err := stmt.Query(server.Id)
	if err != nil {
		return err
	}

	for rows.Next() {
		var (
			ChannelId int
			LinkId    int
		)
		if err := rows.Scan(&ChannelId, &LinkId); err != nil {
			return err
		}

		channel, exists := server.Channels[ChannelId]
		if !exists {
			return errors.New("Attempt to perform link operation on non-existant channel.")
		}

		other, exists := server.Channels[LinkId]
		if !exists {
			return errors.New("Attempt to perform link operation on non-existant channel.")
		}

		server.LinkChannels(channel, other)
	}

	return nil
}
コード例 #16
0
ファイル: user.go プロジェクト: ErikBrown/5sur
func ReturnUserPicture(db *sql.DB, user int, size string) (string, error) {
	picture := ""
	stmt, err := db.Prepare(`
		SELECT custom_picture, name FROM users WHERE id = ?;
		`)

	if err != nil {
		return picture, util.NewError(err, "Error de la base de datos", 500)
	}
	defer stmt.Close()

	customPicture := false
	name := ""
	err = stmt.QueryRow(user).Scan(&customPicture, &name)
	if err != nil {
		return picture, util.NewError(err, "Usuario no encontrado", 500)
	}

	sizeSuffix := ""
	if size != "100" {
		sizeSuffix = "_" + size
	}

	if customPicture {
		picture = "https://5sur.com/images/" + name + sizeSuffix + ".png"
	} else {
		picture = "https://5sur.com/default" + sizeSuffix + ".png"
	}

	return picture, nil
}
コード例 #17
0
ファイル: auth.go プロジェクト: protosam/hostcontrol
func Getusers(username string, data map[string]map[string]string, db *sql.DB) map[string]map[string]string {
	stmt, _ := db.Prepare("SELECT * from hostcontrol_users WHERE owned_by = ? and system_username != owned_by")
	rows, _ := stmt.Query(username)
	stmt.Close()

	for rows.Next() {
		var hostcontrol_id string
		var system_username string
		var privileges string
		var owned_by string
		var login_token string
		var email_address string

		rows.Scan(&hostcontrol_id, &system_username, &privileges, &owned_by, &login_token, &email_address)

		data[system_username] = make(map[string]string)
		data[system_username]["hostcontrol_id"] = hostcontrol_id
		data[system_username]["system_username"] = system_username
		data[system_username]["privileges"] = privileges
		data[system_username]["owned_by"] = owned_by
		data[system_username]["login_token"] = login_token
		data[system_username]["email_address"] = email_address

		data = Getusers(system_username, data, db)

	}

	return data
}
コード例 #18
0
func JssdkDispatch(db *sql.DB) Dlm {
	return Dlm{
		// 获得js-sdk信息
		"getConfig": func(r *http.Request) (string, interface{}) {
			url := GetParameter(r, "url")
			//			url := "http://develop.zsgd.com:11002/votes/wechat/index.html?vote_id=11"
			state, weixin := getOauth2Info(strings.Split(url, "&")[0], db)
			stmt, err := db.Prepare("select weixin, appid, access_token, jsapi_ticket from weixin where weixin = ?")
			defer stmt.Close()
			if nil != err {
				log.Println(err)
				panic("获取js-sdk数据失败")
			}
			var w Wechat
			err = stmt.QueryRow(weixin).Scan(&w.Weixin, &w.Appid, &w.AccessToken, &w.JsapiTicket)
			if nil != err {
				log.Println(err)
				panic("获取js-sdk数据失败")
			}
			var wx WXConfig
			wx.State = state
			wx.Timestamp = time.Now().Unix()
			wx.Appid = w.Appid
			wx.NonceStr = string(GenerateRandomString(16, KC_RAND_KIND_ALL))
			wx.Signature = generateSign(wx.NonceStr, w.JsapiTicket, url, wx.Timestamp)
			return "获取js-sdk数据成功", wx
		},
	}
}
コード例 #19
0
ファイル: mysql.go プロジェクト: JanChou/pholcus
//向sqlCode添加"插入1行数据"的语句,执行前须保证Create()、AddRow()已经执行
//insert into table1(field1,field2) values(rowValues[0],rowValues[1])
func (self *MyTable) Update(db *sql.DB) {
	if self.tableName != "" {
		self.sqlCode = `insert into ` + self.tableName + `(`
		if self.columnNames != nil {
			for _, v1 := range self.columnNames {
				self.sqlCode += "`" + v1 + "`" + `,`
			}
			self.sqlCode = string(self.sqlCode[:len(self.sqlCode)-1])
			self.sqlCode += `)values(`
		}
		if self.rowValues != nil {
			for _, v2 := range self.rowValues {
				v2 = strings.Replace(v2, `"`, `\"`, -1)
				self.sqlCode += `"` + v2 + `"` + `,`
			}
			self.sqlCode = string(self.sqlCode[:len(self.sqlCode)-1])
			self.sqlCode += `);`
		}
	}

	stmt, err := db.Prepare(self.sqlCode)
	util.CheckErr(err)

	_, err = stmt.Exec()
	util.CheckErr(err)

	// 清空临时数据
	self.rowValues = []string{}
}
コード例 #20
0
ファイル: prepdb.go プロジェクト: arelangi/benchMarkSqlite
func InsertPrepareStatement(db *sql.DB, tableName string, rows map[string]interface{}) (stmt *sql.Stmt, err error) {
	stmt, err = db.Prepare("insert into " + tableName + "(" + getFields(rows) + ") values (" + getFieldValues(rows) + ")")
	if err != nil {
		return
	}
	return
}
コード例 #21
0
ファイル: add_virgin_unit.go プロジェクト: jjjjj-kun/mangle
func handleAddVirginUnitMsg(msg AddVirginUnitMsg, db *sql.DB) {
	resp_msg := AddVirginUnitRspMsg{}

	stmt_str :=
		`INSERT INTO VirginUnits(VirginUnitId, Filepath, Hash, Format) 
        VALUES(?, ?, ?, ?)`

	stmt, err := db.Prepare(stmt_str)
	if err != nil {
		resp_msg.Err = err
		msg.ResponseChannel <- resp_msg
		return
	}
	defer stmt.Close()

	_, err = stmt.Exec(
		msg.VirginUnitInfo.VirginUnitId,
		msg.VirginUnitInfo.Filepath,
		msg.VirginUnitInfo.Hash,
		msg.VirginUnitInfo.Format)

	if err != nil {
		resp_msg.Err = err
		msg.ResponseChannel <- resp_msg
		return
	}

	resp_msg.Err = nil
	msg.ResponseChannel <- resp_msg
}
コード例 #22
0
ファイル: test_helper.go プロジェクト: sluu99/um
// assertRecord returns true if the table contains a record with the specified properties
func assertRecord(session *sql.DB, table string, props map[string]interface{}) bool {
	if session == nil {
		panic("database session is not established")
	}

	var buff bytes.Buffer
	var vals = make([]interface{}, len(props))
	var i = 0
	var count int

	buff.WriteString("select count(*) from ")
	buff.WriteString(table)
	buff.WriteString(" where ")

	for k, v := range props {
		if i != 0 {
			buff.WriteString(" and ")
		}
		vals[i] = v
		i++
		buff.WriteString(fmt.Sprintf("%s = $%d", k, i))
	}

	stmt, err := session.Prepare(buff.String())
	if err != nil {
		panic(err)
	}
	row := stmt.QueryRow(vals...)
	err = row.Scan(&count)
	if err != nil {
		panic(err)
	}
	return (count != 0)
}
コード例 #23
0
ファイル: main.go プロジェクト: Abdul2/phonebook
//doSelect a poniter to db.Open() object that we have obtained in main()
//  if all goes well it will return nil otherwise it will return an error
func doSelect(db *sql.DB) error {

	//use the db.Open() to return a prepared statement to be used at later stage
	stmt, err := db.Prepare(selectStatement)

	checkError(err)

	//Query executes a prepared query statement with the given arguments
	// and returns the query results as a *Rows. [this is direct from Go docs]

	rows, err := stmt.Query()

	checkError(err)

	//once done with statement close it.
	defer stmt.Close()

	for rows.Next() {

		var id string
		var name string

		// to avoid caller having copies of values, pointers are used
		err = rows.Scan(&id, &name)

		// first %v is replaced by value of id and second %v by value of name
		fmt.Printf("firstname: %v lastname: %v \n", id, name)
	}

	return nil
}
コード例 #24
0
ファイル: round_result.go プロジェクト: jollheef/tin_foil_hat
// AddRoundResult add round result to database
func AddRoundResult(db *sql.DB, res RoundResult) (id int, err error) {

	addRoundResultMutex.Lock()

	defer addRoundResultMutex.Unlock()

	if res.Round > 1 { // if not first round
		previous, err := GetLastResult(db, res.TeamID)
		if err != nil {
			return id, err
		}
		res.AttackScore += previous.AttackScore
		res.DefenceScore += previous.DefenceScore
	}

	stmt, err := db.Prepare("INSERT INTO round_result " +
		"(team_id, round, attack_score, defence_score) " +
		"VALUES ($1, $2, $3, $4) RETURNING id")
	if err != nil {
		return
	}

	defer stmt.Close()

	err = stmt.QueryRow(res.TeamID, res.Round, res.AttackScore,
		res.DefenceScore).Scan(&id)
	if err != nil {
		return
	}

	return
}
コード例 #25
0
ファイル: ql.go プロジェクト: Links2004/discosrv
func qlCompile(db *sql.DB) (map[string]*sql.Stmt, error) {
	stmts := map[string]string{
		"cleanAddress":  `DELETE FROM Addresses WHERE Seen < now() - duration("2h")`,
		"cleanRelay":    `DELETE FROM Relays WHERE Seen < now() - duration("2h")`,
		"cleanDevice":   `DELETE FROM Devices WHERE Seen < now() - duration("24h")`,
		"countAddress":  "SELECT count(*) FROM Addresses",
		"countDevice":   "SELECT count(*) FROM Devices",
		"countRelay":    "SELECT count(*) FROM Relays",
		"insertAddress": "INSERT INTO Addresses (DeviceID, Seen, Address) VALUES ($1, now(), $2)",
		"insertRelay":   "INSERT INTO Relays (DeviceID, Seen, Address, Latency) VALUES ($1, now(), $2, $3)",
		"insertDevice":  "INSERT INTO Devices (DeviceID, Seen) VALUES ($1, now())",
		"selectAddress": `SELECT Address from Addresses WHERE DeviceID==$1 AND Seen > now() - duration("1h") LIMIT 16`,
		"selectRelay":   `SELECT Address, Latency from Relays WHERE DeviceID==$1 AND Seen > now() - duration("1h") LIMIT 16`,
		"updateAddress": "UPDATE Addresses Seen=now() WHERE DeviceID==$1 AND Address==$2",
		"updateDevice":  "UPDATE Devices Seen=now() WHERE DeviceID==$1",
		"deleteRelay":   "DELETE FROM Relays WHERE DeviceID==$1",
	}

	res := make(map[string]*sql.Stmt, len(stmts))
	for key, stmt := range stmts {
		prep, err := db.Prepare(stmt)
		if err != nil {
			log.Println("Failed to compile", stmt)
			return nil, err
		}
		res[key] = prep
	}
	return res, nil
}
コード例 #26
0
ファイル: db.go プロジェクト: yegle/userpages
func storeSites(db *sql.DB, sites []OpmlOutline) {
	sql_additem := `
	INSERT INTO sites(
		XmlUrl,
		Title,
		Type,
		Text,
		HtmlUrl,
		Favicon
	) values(?, ?, ?, ?, ?, ?)
	`

	stmt, err := db.Prepare(sql_additem)
	if err != nil {
		panic(err)
	}
	defer stmt.Close()

	for _, site := range sites {
		_, err2 := stmt.Exec(site.XmlUrl, site.Title, site.Type,
			site.Text, site.HtmlUrl, site.Favicon)
		if err2 != nil {
			panic(err2)
		}
	}
}
コード例 #27
0
ファイル: users.go プロジェクト: 42MrPiou42/Wibo_OpenProject
/**
* GetDevicesByIdUser
* Query function getDevicesUserIdi  with prototype:
*	FUNCTION getDevicesByUserId(iduser integer) RETURNS TABLE(macaddr varchar(18))
* Return a pointer on new Device list created
**/
func (Lusr *All_users) GetDevicesByIdUser(idUser int64, Db *sql.DB) *list.List {

	lDevice := list.New()
	stm, err := Db.Prepare("SELECT getDevicesByUserId($1)")
	if err != nil {
		Lusr.Logger.Println("GetDevicesByIdUser query error: ", err)
		return lDevice
	}
	defer stm.Close()
	rows, err := stm.Query(idUser)
	if err != nil {
		Lusr.Logger.Println("GetDevicesByIdUser query error: ", err)
		return lDevice
	}
	defer rows.Close()
	for rows.Next() {
		var idDevice string
		err = rows.Scan(&idDevice)
		if err != nil {
			Lusr.Logger.Println("GetDevicesByIdUser scan error: ", err)
		} else {
			lDevice.PushBack(idDevice)
		}
	}
	return lDevice
}
コード例 #28
0
func doImportantWork(db *sql.DB) error {
	stmt, err := db.Prepare("SELECT 'something important'")
	if err != nil {
		return err
	}

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

	for rows.Next() {
		var something string
		err := rows.Scan(&something)
		if err != nil {
			return err
		}
		// throw away the result
	}

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

	return nil
}
コード例 #29
0
ファイル: core.go プロジェクト: stmansour/dir
// CreateDeductionsTable takes the string value of "deductions" for every person
// in the people table, parses it, and creates an entry in the deductions table
// for each deduction
func CreateDeductionsTable(db *sql.DB) {
	// OK, we're connected to the database. On with the work. First thing to do is
	// to put all the deductions into a separate multivalued Deductions table organized
	// by employee id
	rows, err := db.Query("select uid, deductions from people")
	errcheck(err)
	defer rows.Close()

	InsrtDeduct, err := db.Prepare("INSERT INTO deductions (uid,deduction) VALUES(?,?)")
	errcheck(err)
	var (
		uid        int
		deductions string
	)

	for rows.Next() {
		errcheck(rows.Scan(&uid, &deductions))
		if len(deductions) > 0 {
			da := strings.Split(deductions, ",")
			for i := 0; i < len(da); i++ {
				d := deductionStringToInt(strings.Trim(da[i], " \n\r"))
				_, err := InsrtDeduct.Exec(uid, d)
				errcheck(err)
			}
		}
	}
	errcheck(rows.Err())
	RemovePeopleDeductCol, err := db.Prepare("alter table people drop column deductions")
	errcheck(err)
	_, err = RemovePeopleDeductCol.Exec()
	errcheck(err)

	createDeductionsList(db)
}
コード例 #30
0
ファイル: web.go プロジェクト: andrewsomething/go_test
func doInitialize(db *sql.DB) error {
	var stmt *sql.Stmt
	var err error

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

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

	defer stmt.Close()

	stmt, err = db.Prepare("INSERT INTO contacts(firstname,lastname) VALUES($1, $2)")
	if err != nil {
		fmt.Printf("stmt.Prepare error: %v\n", err)
	}

	_, err = stmt.Exec("John", "Smith")
	if err != nil {
		fmt.Printf("stmt.Exec error: %v\n", err)
	}

	return nil
}