CODEWITHSUNDEEP
mysqlgogorp
Anuruddha
Anuruddha

Reputation: 3245

MySQL NULL value issue on SelectInt gorp

I'm trying to run the following query using gorp library with mysql

query = "SELECT SUM(outputoctets) FROM monthlyacct where date >= ? AND date < ? AND location = ?"
count, err := dbMap.SelectInt(query , constrains.From, constrains.To, constrains.LocationId)

This query throws the following error,

Scan error on column index 0: converting string \"\u003cnil\u003e\" to a int64: strconv.ParseInt: parsing \"\u003cnil\u003e\": invalid syntax

Here column outputoctets is type BIGINT. I also tried changing SelectInt to SelectStr, then it threw the following error;

Scan error on column index 0: unsupported driver -\u003e Scan pair: \u003cnil\u003e -\u003e *string

When I ran the query without location filter, it worked. Seems like this is due to some NULL values present in the location column. However I need to have the location filter on the query. How can I solve this?

Upvotes: 0

Views: 3286

Answers (1)

Mark
Mark

Reputation: 7071

Looks like the additional location criteria is filtering out all results, so the sum(outputoctets) is null. \u003cnil\u003e is the string "<nil>", which go rejects as an invalid integer.

Use a 'nullable' type to hold the sum, eg, NullInt64

Alternatively, use the mysql IFNULL function to avoid returning a null from the query, eg, IFNULL(sum(outputoctets),0)

See the Go database tutorial for more information

Upvotes: 1

Related Questions