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

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 ?

doumei8258
doumei8258 是的,我没有惊慌。但是变量l2name,l2contact,l3name,l3contact,l4name,l4contact都可以为空,为什么只在L2Name上引起问题?
一年多之前 回复
douyue2313
douyue2313 1.您的checkErr可能不会引发紧急情况,因此代码执行仍在继续。2.最简单的解决方法是使用COALESCE(templates.L2Name,'')在SQL语句中包装可为空的cols。
一年多之前 回复

2个回答



最简单的解决方法是使用COALESCE(templates.L2Name,'')将可空cols包装在您的sql语句中,感谢@pmk </ p>

  sqlstatement:=“ SELECT” + 
“ asset.ID,assets.Name,assets.LastCheckIn,assets.Status,assets.OffTimer,” +
“资产.SafetyTimer,资产.HazardTimer,资产.HazardTimerStartedTime,资产.LastSignedOn,” +
资产。 ,assets.OrganisationID,“ +
” organisations.Name作为OrganisationName,“ +
” devices.Label作为CurrentDeviceName,“ +
” template.Name作为TemplateName,COALESCE(templates.L2Name,“),COALESCE( templates.L2Contact,''),“ +
” COALESCE(templates.L3Contact,)),++ n“ COALESCE(templates.L4Name,''),COALESCE( template.L4Contact,''),template.Note“ +
” FROM资产“ +
” LEFT JOIN组织 在资产上。OrganisationID= Organisations.ID“ +
”在资产上左加入设备。CurrentDeviceID= devices.ID“ +
”在资产上加入模板。TemplateID= templates.ID“ +
” WHERE资产。状态!= '不监视'AND资产。AssetStatus='有效'AND资产。显示!='0'“
</ code> </ pre>
</ div>

展开原文

原文

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'"



对于可以为NULL的字段,您应该使用sql.NullString数据结构。
以下是更多详细信息:</ p>
\ n

https://golang.org/pkg/database/sql/#NullString < / a> </ p>
</ div>

展开原文

原文

For fields that can be NULL you should use sql.NullString data structure. Here is more details:

https://golang.org/pkg/database/sql/#NullString

ds122455
ds122455 因为它是第一位的。 您能否详细说明“它仍然不起作用”是什么意思? 如果您进行了更改,我建议它不会因相同的错误而失败。 如果看到相同的确切错误,则说明您的代码未更新。
一年多之前 回复
dounai1986
dounai1986 嗨,它仍然无法正常工作。 变量l2name,l2contact,l3name,l3contact,l4name,l4contact都可以为空,为什么仅导致L2Name出现问题?
一年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问