yyfbaloot/database/sqlserver.go

package database

//import _ "github.com/denisenkom/go-mssqldb"
import (
	"bet24.com/log"
	"database/sql"
	"encoding/json"
	"fmt"
	"strconv"
	"strings"
	"time"
)

type SqlServer struct {
	m_db          *sql.DB
	goroutine_cnt chan int
}

func NewSqlServer(datasource, database, user, password string) *SqlServer {
	this := new(SqlServer)
	if !this.OpenDB(datasource, database, user, password) {
		return nil
	}
	this.goroutine_cnt = make(chan int, 30)
	return this
}

func (this *SqlServer) OpenDB(ds string, database string, user string, password string) bool {
	connString := fmt.Sprintf("server=%s;database=%s;user id=%s;password=%s;port=1433;encrypt=disable",
		ds, database, user, password)

	fmt.Printf(" connString:%s\n", connString)

	db, err := sql.Open("mssql", connString)
	if err != nil {
		log.Error("Open connection failed: %v", err.Error())
		return false
	}
	db.SetMaxIdleConns(64)
	db.SetMaxOpenConns(64)
	err = db.Ping()
	if err != nil {
		log.Error("PING:%s\n", err)
		return false
	}
	this.m_db = db
	return true
}

func (this *SqlServer) execSqlJson(sqlString string) (string, error) {
	this.goroutine_cnt <- 1
	db := this.m_db

	defer func() {
		//c <- ret
		<-this.goroutine_cnt
	}()

	rows, err := db.Query(sqlString)
	
	if err != nil {
		return "", err
	}
	defer rows.Close()
	columns, err := rows.Columns()
	if err != nil {
		return "", err
	}
	count := len(columns)
	tableData := make([]map[string]interface{}, 0)
	values := make([]interface{}, count)
	valuePtrs := make([]interface{}, count)
	for rows.Next() {
		for i := 0; i < count; i++ {
			valuePtrs[i] = &values[i]
		}
		rows.Scan(valuePtrs...)
		entry := make(map[string]interface{})
		for i, col := range columns {
			var v interface{}
			val := values[i]
			b, ok := val.([]byte)
			if ok {
				str := string(b)
				// 可能是float
				if strings.Contains(str, ".") {
					f, err := strconv.ParseFloat(str, 64)
					if err == nil {
						v = f
					}
				}
				if v == nil {
					v = str
				}
			} else {
				v = val
			}
			entry[col] = v
		}
		tableData = append(tableData, entry)
	}
	jsonData, err := json.Marshal(tableData)
	if err != nil {
		return "", err
	}
	// fmt.Println(string(jsonData))
	return string(jsonData), nil
}

func (this *SqlServer) execSql(sqlstring string) [][]interface{} {
	this.goroutine_cnt <- 1
	db := this.m_db

	var ret [][]interface{}
	defer func() {
		//c <- ret
		<-this.goroutine_cnt
	}()

	rows, err := db.Query(sqlstring)
	if err != nil {
		log.Error("db.Query %v %s\n", err.Error(), sqlstring)
		return ret
	}
	defer rows.Close()

	for {
		cols, err := rows.Columns()
		if err != nil {
			log.Error("rows.Columns() %v", err.Error())
			return ret
		}
		if cols == nil {
			log.Error("cols == nil %v", err.Error())
			return ret
		}
		for rows.Next() {
			vals := make([]interface{}, len(cols))
			for i := 0; i < len(cols); i++ {
				vals[i] = new(interface{})
			}
			err = rows.Scan(vals...)
			if err != nil {
				fmt.Println(err)
				continue
			}

			//log
			//for i := 0; i < len(vals); i++ {
			//	if i != 0 {
			//		log.Debug("\t")
			//	}
			//	printValue(vals[i].(*interface{}))
			//}

			//fmt.Println()

			ret = append(ret, vals)
		}
		if !rows.NextResultSet() {
			break
		}
	}

	return ret
}

func printValue(pval *interface{}) {
	switch v := (*pval).(type) {
	case nil:
		log.Debug("nil NULL")
	case bool:
		if v {
			log.Debug("bool 1")
		} else {
			log.Debug("bool 0")
		}
	case []byte:
		log.Debug("[]byte %s", v)
	case time.Time:
		log.Debug("time.Time %s", v.Format("2006-01-02 15:04:05.999"))
	default:
		log.Debug("default %v", v)
	}
}