// Select a table and returns objects
func SelectWith(db *sql.DB, val PtrRecord, postSql string, args ...interface{}) (interface{}, *Result) {
sql := sqlutils.BuildSelectClause(val) + " " + postSql
rows, err := db.Query(sql, args...)
if err != nil {
return nil, NewErrorResult(err, sql)
}
defer rows.Close()
slice, err := CreateStructSliceFromRows(val, rows)
if err != nil {
return slice, NewErrorResult(err, sql)
}
return slice, NewResult(sql)
}
/*
Select all records from a table which based on the record struct.
*/
func Select(db *sql.DB, val PtrRecord) (interface{}, *Result) {
var sql = sqlutils.BuildSelectClause(val)
rows, err := db.Query(sql)
if err != nil {
return nil, NewErrorResult(err, sql)
}
defer rows.Close()
slice, err := CreateStructSliceFromRows(val, rows)
if err != nil {
return slice, NewErrorResult(err, sql)
}
return slice, NewResult(sql)
}
func TestFillRecord(t *testing.T) {
staff := Staff{}
var db = openDB()
// Create Staff
staff.Id = 1
staff.Name = "Mary"
staff.Phone = "1234567"
t1 := time.Now()
staff.CreatedOn = &t1
r := Create(db, &staff, DriverPg)
if r.Error != nil {
t.Fatal(r.Error)
}
if r.Id == -1 {
t.Fatal("Primary key failed")
}
staff.Id = r.Id
sql := sqlutils.BuildSelectClause(&staff) + " WHERE id = $1"
if !strings.Contains(sql, "id, name, gender, staff_type, phone, birthday, created_on") {
t.Fatal("Unexpected SQL: " + sql)
}
if !strings.Contains(sql, "FROM staffs WHERE id = $1") {
t.Fatal("Unexpected SQL: " + sql)
}
stmt, err := db.Prepare(sql)
rows, err := stmt.Query(r.Id)
if rows.Next() {
err = FillFromRows(&staff, rows)
if err != nil {
t.Fatal(err)
}
} else {
t.Fatal("No record found.")
}
r = Delete(db, &staff)
t.Log(r)
if r.Error != nil {
t.Fatal(r.Error)
}
}
func SelectWhere(db *sql.DB, val PtrRecord, conds WhereMap) (interface{}, *Result) {
var whereSql, args = sqlutils.BuildWhereClauseWithAndOp(conds, GetHolderTypeByDriver(driverType))
var sql = sqlutils.BuildSelectClause(val) + whereSql
var rows, err = db.Query(sql, args...)
if err != nil {
return nil, NewErrorResult(err, sql)
}
defer rows.Close()
slice, err := CreateStructSliceFromRows(val, rows)
if err != nil {
return slice, NewErrorResult(err, sql)
}
return slice, NewResult(sql)
}
/*
Load record from a where condition map
*/
func LoadByCols(db *sql.DB, val PtrRecord, cols WhereMap) *Result {
var sqlstring = sqlutils.BuildSelectClause(val)
whereSql, args := sqlutils.BuildWhereClauseWithAndOp(cols, GetHolderTypeByDriver(driverType))
sqlstring += whereSql + sqlutils.BuildLimitClause(1)
return LoadFromQueryRow(db, val, sqlstring, args...)
}
func LoadWith(db *sql.DB, val PtrRecord, postQuery string, args ...interface{}) *Result {
var sqlstring = sqlutils.BuildSelectClause(val) + " " + postQuery + sqlutils.BuildLimitClause(1)
return LoadFromQueryRow(db, val, sqlstring, args...)
}
func QuerySelectWith(db *sql.DB, val PtrRecord, postSql string, args ...interface{}) (*sql.Rows, error) {
return db.Query(sqlutils.BuildSelectClause(val)+" "+postSql, args...)
}
/*
Execute a select query to the database connection.
*/
func QuerySelect(db *sql.DB, val PtrRecord) (*sql.Rows, error) {
return db.Query(sqlutils.BuildSelectClause(val))
}