Using prepared statements & pointers in Golang

I changed the name of this blog to “a study of bugs”. This makes it easier for me to think of what to write about -- bugs, of course.

In MySQL, you can use a question mark (?) in a prepared statement to stand in for a value.

func GetUserId(name string) (int, error) {
    // (1) - BAD
    stmt, err := dbconn.Db.Prepare("SELECT id FROM user WHERE name=\"?\";")
    // (2) – GOOD
    stmt, err := dbconn.Db.Prepare("SELECT id FROM user WHERE name=?;") 
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()

    var userId int
    err = stmt.QueryRow(name).Scan(&userId) // (3)
    if err != nil {
        if err != sql.ErrNoRows {
            log.Fatal(err)
        }
        return 0, err
    }

    return userId, nil
}

In the Go code above, dbconn is a connection to a MySQL server. Line 1 defines a prepared statement. And Line 3 queries the table for a row where the name column matches the value of variable name. I assumed the ? in this query would be interpolated with an actual string. I added quotation marks since they are needed around strings in MySQL CLI.

This fails to return anything. Removing the quotation marks solved the problem. The correct code is on Line 2.

I also find Line 3 quite interesting. Here, first, stmt.QueryRow() returns a pointer to a sql.Row object. Its Scan() method then does two fascinating things: a) it sets a “dest” to the output value and b) returns an error if there's an error or nil if there's no error.

Thingy a is quite interesting. In this case, Scan() method will find the memory address of variable userId and write the output value (the user's ID) there.

It feels as if getting the value from a DB is a side effect.

The same goes when you decode a JSON string. In this example, on Line 4, Unmarshal() method unpacks the JSON string and writes it to &animals, the address of animals variable. And this is a side effect. The return value is again, either an error or nil — if there is an error, you simply can't ignore it.

var jsonBlob = []byte(`[
    {"Name": "Platypus", "Order": "Monotremata"},
    {"Name": "Quoll",    "Order": "Dasyuromorphia"}
]`)
type Animal struct {
    Name  string
    Order string
}
var animals []Animal
err := json.Unmarshal(jsonBlob, &animals) // (4)