如何修复Golang SQL字符串转换扫描错误

I'm receiving the following error when querying a row in an sqlite3 database:

sql: Scan error on column index 3: converting string "" to a int64: strconv.ParseInt: parsing "": invalid syntax

The query itself works, as it is able to return the queried data. I should note that each row in the database contains several columns with NULL data (only the Id and Email columns are populated). I am struggling to understand why the err occurs and how to resolve it.

The SQL table has the following nine columns:

`0,Id,INTEGER,0,,1
1,Email,TEXT,0,,0
2,Name,TEXT,0,,0
3,Rsvp,INTEGER,0,,0
4,Guests,INTEGER,0,,0
5,Meal0,INTEGER,0,,0
6,Meal1,INTEGER,0,,0
7,Comments,TEXT,0,,0
8,ModifiedAt,TEXT,0,,0`

which are defined in server.go with the following struct (I added the sql.NullString to handle the empty columns):

type User struct {
    Id         int
    Email      string
    Name       sql.NullString
    Rsvp       sql.NullInt64
    Guests     sql.NullInt64
    Meal0      sql.NullInt64
    Meal1      sql.NullInt64
    Comments   sql.NullString
    ModifiedAt sql.NullString
}

the query is from the following handler:

func Rsvp1Handler(w http.ResponseWriter, r *http.Request) {
  email := r.URL.Path[len("/rsvp/"):]
  var user User
  err := userStatement.QueryRow(email).Scan(&user.Id, &user.Email, &user.Name, &user.Rsvp, &user.Guests, &user.Meal0, &user.Meal1, &user.Comments, &user.ModifiedAt)

  switch {
  case err == sql.ErrNoRows:
    log.Println("No user with that ID.")
    w.Header().Set("Content-Type", "text/plain")
    fmt.Fprintf(w, "Email address not found: %s", email)

  case err != nil:
    log.Println(err)
    fmt.Fprintf(w, "Database error:
", err)
  default:
    log.Println("Query success. Email address: ", email)
    w.Header().Set("Content-Type", "application/json")
    json.NewEncoder(w).Encode(&user)
}

which relies on the following constant and var:

const userSelect  = "SELECT * FROM rsvp WHERE email = ?"
var userStatement   *sql.Stmt

Any help or insight into the error is greatly appreciated!

donkey199024
donkey199024 我已经明确指定了SQLSELECT,但仍然收到相同的错误:sql:扫描列索引3时出错:将字符串“”转换为int64:strconv.ParseInt:解析“”:无效语法
接近 6 年之前 回复
dongyong3554
dongyong3554 尝试在您的SQLSELECT语句中明确显示,而不要使用*。这将有助于排除列不匹配。
接近 6 年之前 回复

1个回答



您的其中一列(很可能是 Rsvp </ code>)似乎存储为字符串类型,而不是int, 设置为“” </ code>而不是 null </ code>。</ p>

因此,您要么必须更改 Rsvp </ code>(可能是 将其更改为 sql.NullString </ code>的另一列),重做数据库以使其具有null而不是数字字段的空字符串,或者如果您认为它是驱动程序中的错误,请在其跟踪器上打开一个问题。</ p >
</ div>

展开原文

原文

It seems one of your columns (most likely Rsvp) is stored as a string type not int and it's set to "" instead of null.

So you either have to change Rsvp (might be a different column) to sql.NullString, redo the database to have nulls instead of empty strings for numeric fields or if you believe it's a bug in the driver, open an issue on their tracker.

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问