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

huangapple go评论85阅读模式
英文:

How to fix golang sql string conversion scan error

问题

在查询sqlite3数据库中的一行时,我收到以下错误:

sql: 在第3列索引上扫描错误: 将字符串""转换为int64: strconv.ParseInt: 解析"": 无效的语法

查询本身是有效的,因为它能够返回查询的数据。我应该注意到数据库中的每一行都包含几个包含NULL数据的列(只有IdEmail列是有数据的)。我很难理解为什么会出现这个错误以及如何解决它。

SQL表格有以下九列:

`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`

这些列在server.go中用以下结构体定义(我添加了sql.NullString来处理空列):

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
}

查询来自以下处理程序:

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("没有找到该ID的用户。")
        w.Header().Set("Content-Type", "text/plain")
        fmt.Fprintf(w, "未找到电子邮件地址:%s", email)

    case err != nil:
        log.Println(err)
        fmt.Fprintf(w, "数据库错误:\n", err)
    default:
        log.Println("查询成功。电子邮件地址:", email)
        w.Header().Set("Content-Type", "application/json")
        json.NewEncoder(w).Encode(&user)
}

它依赖于以下常量和变量:

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

非常感谢任何帮助或对错误的见解!

英文:

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:\n", 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!

答案1

得分: 3

似乎你的其中一列(很可能是 Rsvp)被存储为字符串类型而不是整数类型,并且它的值被设置为 "" 而不是 null

所以你需要将 Rsvp(可能是其他列)更改为 sql.NullString,重新设计数据库以在数值字段中使用 null 而不是空字符串,或者如果你认为这是驱动程序的错误,可以在其问题跟踪器上提交一个问题。

英文:

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.

huangapple
  • 本文由 发表于 2014年9月28日 03:45:39
  • 转载请务必保留本文链接:https://go.coder-hub.com/26078522.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定