Using ? with slices

[zhaohui-kevin]

Like this:
SELECT * FROM TABLE WHERE A IN (?)

when i use the query interface db.Query(sSql,vArgs...),hwo to input para ?
as a string like this:"a,b,c"?
or as a array like this:[a,b,c]?
or as a slice like this:["a","b","c"]?

that`s all failed,cry...

[arnehormann]

This is a duplicate of #107.

That won't work.

A ? can not match anything changing the execution plan of the statement after it is prepared.
Here are the case it can not be used for (may be incomplete):

• more than one value (slices, maps, ...) ◀️ this is your mistake here
• SQL keywords
• SQL identifiers (schema, table and column names)
• sort order

You'll have to rewrite your query. If you always have 3 arguments, make it
SELECT * FROM TABLE WHERE A IN (?, ?, ?).
If not, you have to create the query depending on the number of slice elements or look for another approach.

[zhaohui-kevin]

Hi Arnehormann thanks for your answer,if use like this "SELECT * FROM TABLE WHERE A IN (?, ?, ?)", i have to know how many para;But usually i don`t know how many para there are, so i want to use in a string input para like this:SELECT * FROM TABLE WHERE A IN (?) ,and query from a string or []string ext.

thank you
br for u.

[DisposaBoy]

@zhaohui-kevin, unless I'm missing something. You always know how many params there are because you have to either pass individual args to Query() or expand a slice with s... so just construct the query with the correct number of placeholders based on that knowledge http://play.golang.org/p/mA2KwolV-n

[arnehormann]

From reading this, I'm not sure if you know what to do now or not.
If you don't know the number of parameters in advance, you can not use ?.

So you need to do something like

if len(inValues) > 0 {
    query = "SELECT * FROM " + table + " WHERE " + column +
        " IN (?" + strings.Repeat(",?", len(inValues)-1)) + ")"
    ....
}

EDIT Thanks @DisposaBoy, that looks rather familiar 👍

[zhaohui-kevin]

I know how to use like this, but that is not my mind:) Any way thanks very much.

At 2013-11-22 19:06:15,"Arne Hormann" [email protected] wrote:

From reading this, I'm not sure if you know what to do now or not.
If you don't know the number of parameters in advance, you can not use ?.

So you need to do something like

iflen(inValues)>0{query="SELECT * FROM "+table+" WHERE "+column+" IN (?"+strings.Repeat(",?",len(inValues)-1))+")"....}

—
Reply to this email directly or view it on GitHub.

[developerdong]

This example may be better.

package main

import (
	"fmt"
	"strings"
)

func main() {
	clauses := []string{"SELECT id FROM table WHERE 1"}
	params := make([]interface{}, 0)
	if names := []string{"Alice", "Bob"}; len(names) > 0 {
		clauses = append(clauses,
			fmt.Sprintf(
				"AND name IN (%s)",
				strings.Join(strings.Split(strings.Repeat("?", len(names)), ""), ", "),
			),
		)
		for _, name := range names {
			params = append(params, name)
		}
	}
	// now we have the final clause and parameters, so we can execute it.
	fmt.Println(strings.Join(clauses, " ") + ";")
	fmt.Println(params)
}

If the length of names is bigger than 0, the IN condition will be appended to the final clause, otherwise it will not.
In this example, it will not have impact on other conditions if the length is 0.