sqlgonullpointerexceptionsqlx

go with sqlx NamedQuery timestamp works with date but not with datetime. NamedQuery vs Query

rows, err := db.NamedQuery(`SELECT ts FROM test_table WHERE ts > '1999-01-08 04:05:06';`, map[string]interface{}{})

The code above gave me the following error:

unexpected `:` while reading named param at 74
panic: runtime error: invalid memory address or nil pointer dereference

This is strange, as the following snippet,

rows, err := db.NamedQuery(`SELECT ts FROM test_table WHERE ts > '1999-01-08';`, map[string]interface{}{})

runs without fault. The difference between the two, is adding time to the input.

I resorted to using db.Query instead of the sqlx method db.NamedQuery which solved my problem.

I now see that I should have passed my input to NamedQuery as a parameter. How does one typically write such a query and why would you use NamedQuery rather than Query?

Solution

  • why would you use NamedQuery rather than Query?

    Queries that use named parameters are easier for the human to parse.

    How does one typically write such a query

    layout := "2006-01-02 15:04:05"
ts, err := time.Parse(layout, "1999-01-08 04:05:06")
if err != nil {
    return err
}

arg := map[string]interface{}{"ts": ts}
rows, err := db.NamedQuery(`SELECT ts FROM test_table WHERE ts > :ts`, arg)
if err != nil {
    return err
}