I have a really simple mysql table, with 3 columns, A, B and C.
A and B are keys.
I have a GO app and I'm trying to retrieve data from db. With other queries works like a charm, but with this one it doesn't:
aParam := "aValue"
bParam := "3,4,6,9"
stmt, err := o.database.Prepare("SELECT * FROM tableX WHERE `A`= ? AND `B` IN ( ? )")
defer stmt.Close()
rows, err := stmt.Query(aParam, bParam)
for rows.Next() {
...
}
If I replace the second ? for the values, it works perfect:
stmt, err := o.database.Prepare("SELECT * FROM tableX WHERE `A`= ? AND `B` IN ( 3,4,6,9 )")
I also tried with this (it doesn't work):
stmt, err := o.database.Prepare("SELECT * FROM tableX WHERE `A`= ? AND `B` IN ( " + bParam +" )")
Any idea?
The issue is that the single ? is not expanded into the SELECT statement (like a string replace), but as a single string value 3,4,5,6
You need to expand each value of the IN clause as such:
params := []interface{} { "aValue", 3, 4, 6, 9 }
stmt, err := o.database.Prepare("SELECT * FROM tableX WHERE `A`= ? AND `B` IN ( ?, ?, ?, ? )")
defer stmt.Close()
rows, err := stmt.Query(params...)
To make your life easier, you can use a package like sqlx, which has better support for parameterized IN queries