Using Golang and the built in database/sql library and the postgres lib/pq library, I'm trying to read from a database that has some null values in some of the records. the code compiles, but when I try to run it I get the following error:
sql: Scan error on column index 19, name "L2Name": unsupported Scan, storing driver.Value type <nil> into type *string
I have the struct like this:
// Assets Info Dataset
type AssetInfo struct {
Asset_id string
Asset_name string
Organisation_id string
LastCheckIn string
Asset_Status string
Asset_latitude string
Asset_longitude string
Organisation_name string
CurrentDevice_name string
AssetActiveDeviceType string
AssetSafetyTimer float32
TemplateName string
TemplateL2name string
TemplateL2contact string
TemplateL3name string
TemplateL3contact string
TemplateL4name string
TemplateL4contact string
TemplateEscalationNotes string
}
Here is my code:
db, err := sql.Open("mysql", "stevejc:19939c@tcp(127.0.0.1:3306)/johntcw_loneworker?charset=utf8")
checkErr(err)
defer db.Close()
for {
sqlstatement := "SELECT" +
" assets.ID, assets.Name, assets.LastCheckIn, assets.Status, assets.OffTimer," +
" assets.SafetyTimer, assets.HazardTimer, assets.HazardTimerStartedTime, assets.LastSignedOn," +
" assets.Latitude, assets.Longitude, assets.TemplateID, assets.ActiveDeviceType, assets.CurrentDeviceID," +
" assets.OffTimerTemp, assets.OrganisationID," +
" organisations.Name As OrganisationName," +
" devices.Label As CurrentDeviceName," +
" templates.Name As TemplateName, templates.L2Name, templates.L2Contact, templates.L3Name, templates.L3Contact," +
" templates.L4Name, templates.L4Contact, templates.Note" +
" FROM assets" +
" LEFT JOIN organisations ON assets.OrganisationID = organisations.ID" +
" LEFT JOIN devices ON assets.CurrentDeviceID = devices.ID" +
" JOIN templates ON assets.TemplateID = templates.ID" +
" WHERE assets.Status != 'Not monitoring' AND assets.AssetStatus = 'Active' AND assets.Display != '0'"
// select monitoring assets
rows, err := db.Query(sqlstatement)
checkErr(err)
for rows.Next() {
var assetid string
var name string
var lastcheckin string
var status string
var offtimer float32
var offtimertemp float32
var safetytimer float32
var hazardtimer float32
var hazardstarttime string
var lastsignedon string
var lat string
var lon string
var templateid string
var activedevicetype string
var currentdeviceid string
var organisationid string
var organisationname string
var currentdevicename string
var templatename string
var l2name string
var l2contact string
var l3name string
var l3contact string
var l4name string
var l4contact string
var escalationnotes string
err = rows.Scan(&assetid,
&name,
&lastcheckin,
&status,
&offtimer,
&safetytimer,
&hazardtimer,
&hazardstarttime,
&lastsignedon,
&lat,
&lon,
&templateid,
&activedevicetype,
¤tdeviceid,
&offtimertemp,
&organisationid,
&organisationname,
¤tdevicename,
&templatename,
&l2name,
&l2contact,
&l3name,
&l3contact,
&l4name,
&l4contact,
&escalationnotes)
checkErr(err)
assetinfo := new(AssetInfo)
assetinfo.Asset_id = assetid
assetinfo.Asset_name = name
assetinfo.LastCheckIn = lastcheckin
assetinfo.Asset_Status = status
assetinfo.Organisation_id = organisationid
assetinfo.Asset_longitude = lon
assetinfo.Asset_latitude = lat
assetinfo.Organisation_name = organisationname
assetinfo.CurrentDevice_name = currentdevicename
assetinfo.AssetActiveDeviceType = activedevicetype
assetinfo.AssetSafetyTimer = safetytimer
assetinfo.TemplateName = templatename
assetinfo.TemplateL2name = l2name
assetinfo.TemplateL2contact = l2contact
assetinfo.TemplateL3name = l3name
assetinfo.TemplateL3contact = l3contact
assetinfo.TemplateL4name = l4name
assetinfo.TemplateL4contact = l4contact
assetinfo.TemplateEscalationNotes = escalationnotes
The output of the code is ok, I can get all the data I want from the database. Except the SQL error is also printed on the console. And the L2Name is not a null value here, I can get the value printing on the console. So don't know why the error showing a type ?