gorp Update not updating

Question

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)
   }
}

you can use sql logging of gorp to see the query https://github.com/go-gorp/gorp#sql-logging — fzerorubigd, Jun 13 '15 at 16:48

score 6 · Accepted Answer · answered Jun 13 '15 at 10:32

6

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)

answered Jun 13 '15 at 10:32

Ainar-G

34,563
13
93
119

Thanks for the answer this did work for me but it got a bit ugly, is there a more streamlined way to do this ? Before i posted this question i had `npr := dbProbes[index]` where `dbProbes` is a slice of `WirelessNetwork` but that also failed to work. Now with your solution i have to set every property of that struct ? – norwat Jun 14 '15 at 16:53
@norwat That still depends on where do `dbProbes` come from. I can't recommend anything without knowing that, but if you could validate your data there, `npr := dbProbes[index]` would work again. – Ainar-G Jun 14 '15 at 17:48
Sorry forgot to mention that, dbProbes comes from another table which also stores `WirelessNetwork` objects, i then try to match a elements of one table with another based on user input. So in essence `dbProbes` corresponds to an sql object with all valid fields and `npr` a new object with only 2 valid fields – norwat Jun 14 '15 at 19:10
Is the data in the other table all NULLs? If it isn't, I'd say the problem is in the way you fetch the data from that other table. Also I don't quite get your last sentence. Is `npr` supposed to be all NULLs except for two fields? – Ainar-G Jun 14 '15 at 20:42
no, `dbProbes` comes from a table where all the fields are not null, and yes `npr` only has two non null fields – norwat Jun 15 '15 at 13:10
If values are in the table and you still get NULLs from it, then inspect the code that fetches them and maybe open a new question about it. Once that is solved, you can simply nullify the unneeded fields of `npr`. – Ainar-G Jun 15 '15 at 13:41

gorp Update not updating

1 Answers1