with - sql update select



gorp Update not updating (1)

Im having issues updating a row in my postgresql database with gorp, im successfully able run the update using db.Exec, all columns get updated with the right information, while with gorp im only able to update the non sql.Null* fields while the rest remain unchanged.

var db *sql.DB
var dbmap *gorp.DbMap

func getDB() (*sql.DB, *gorp.DbMap) {
    if db == nil {
        var err error
        db, err = sql.Open("postgres", "postgres://xxxxxxxx")
        db.SetMaxOpenConns(5)
        db.SetMaxIdleConns(0)
        dbmap = &gorp.DbMap{Db: db, Dialect: gorp.PostgresDialect{}}
        dbmap.AddTableWithName(WirelessNetwork{}, "network").SetKeys(true, "Id")
        if err != nil {
            log.Panic(err)
        }
    }

    return db, dbmap
}

type WirelessNetwork struct {
    Id        int             `db:"id"`
    Ssid      string          `db:"ssid"`
    Lat       sql.NullFloat64 `db:"lat"`
    Lon       sql.NullFloat64 `db:"lon"`
    Sec       sql.NullString  `db:"sec"`
    Bssid     sql.NullString  `db:"bssid"`
    Channel   sql.NullInt64   `db:"channel"`
    Found     bool            `db:"found"`
    Datefirst sql.NullString  `db:"datefirst"`
    Datelast  sql.NullString  `db:"datelast"`
}

npr := new(WirelessNetwork)
npr.Id = getNetworkId(ssid)
npr.Ssid = ssid
npr.Lat = dbProbes[index].Lat
npr.Lon = dbProbes[index].Lon
npr.Sec = dbProbes[index].Sec
npr.Bssid = dbProbes[index].Bssid
npr.Channel = dbProbes[index].Channel
npr.Found = dbProbes[index].Found
npr.Datefirst = dbProbes[index].Datefirst
npr.Datelast = dbProbes[index].Datelast
npr.Found = true

This works

db, _ := getDB()
db.Exec("UPDATE network SET ssid=$1,lat=$2,lon=$3,sec=$4,channel=$5,found=$6,datefirst=$7,datelast=$8,bssid=$9 WHERE id=$10",
    npr.Ssid, npr.Lat.Float64, npr.Lon.Float64, npr.Sec.String, npr.Channel.Int64, npr.Found, npr.Datefirst.String, npr.Datelast.String, npr.Bssid.String, getNetworkId(ssid))

This does not

func updateNetwork(n *WirelessNetwork) {
    _, dbmap := getDB()
    _, err := dbmap.Update(n)
   if err != nil {
        log.Fatal("updateNetwork - ", err)
   }
}

sql.Null* types are structs with Valid boolean field, which tells if the value is NULL. The initial value for boolean is false, so unless you explicitly validate your data, you'll be sending NULLs to the database. You didn't tell us, what is dbProbes and how does it get the data, but if it's initialised with something like

dbProbes[index].Lat = sql.NullFloat64{Float64: lat}

then Valid is still false, and you need to either validate your data manually:

dbProbes[index].Lat = sql.NullFloat64{Float64: lat, Valid: true}

or use the Scan method:

err = dbProbes[index].Lat.Scan(lat)




gorp