Issue with sql query in GO

Question

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?

What DB driver are you using? Popular Go DB drivers often don't support IN queries very well. — Ainar-G
– Ainar-G, Commented Aug 19, 2015 at 15:24

Stuart Carnie · Accepted Answer · 2015-08-19 15:32:05Z

2

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

answered Aug 19, 2015 at 15:32

Stuart Carnie

5,4961 gold badge32 silver badges28 bronze badges

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

3 Comments

Brainhash Over a year ago

another option would be to populate temporary table to keep 'IN' list and then use this table in the query (either join or IN subquery). This way you are not bounded too much handling varying size of IN list.

Oriam Over a year ago

ok, just another question, here: params := []interface{} { "aValue", 3, 4, 6, 9 } I need to add dynamically those values, I not even know how many they are going to be

Stuart Carnie Over a year ago

you would need to generate a string of ?'s for the IN clause matching the number of IDs you need

Collectives™ on Stack Overflow

Issue with sql query in GO

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related