sql：列索引19上的扫描错误，名称“ L2Name”：不支持的扫描，存储了驱动程序。值类型<nil>转换为类型* string

Question

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,
        &currentdeviceid,
        &offtimertemp,
        &organisationid,
        &organisationname,
        &currentdevicename,
        &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 ?

Answer 1

Simplest fix is to wrap the nullable cols in your sql statement with a COALESCE(templates.L2Name, ''), thanks for @pmk help.

  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, COALESCE(templates.L2Name, ''), COALESCE(templates.L2Contact, '')," +
    " COALESCE(templates.L3Name, ''), COALESCE(templates.L3Contact, '')," +
    " COALESCE(templates.L4Name, ''), COALESCE(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'"