3

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!

2
  • 2
    Try being explicit in your SQL SELECT statement instead of using *. This will help rule out a column mismatch. Commented Sep 27, 2014 at 20:02
  • I've made the SQL SELECT explicit, but still receive the same error: sql: Scan error on column index 3: converting string "" to a int64: strconv.ParseInt: parsing "": invalid syntax Commented Sep 27, 2014 at 20:14

1 Answer 1

3

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.