Possible to bind arrays, for use in e.g. IN (?) queries?

[beaugunderson]

I'd like to do something like this and pass a list of strings to IN in my query:

	var callsInFile = []string{"N7YHF", "W0NY"}

	err = sqlitex.Execute(conn,
		`SELECT id, callsign FROM callsigns WHERE callsign IN (?)`,
		&sqlitex.ExecOptions{Args: []any{callsInFile},
			ResultFunc: func(stmt *sqlite.Stmt) error {
				fmt.Printf("%d\n", stmt.ColumnInt64(0))
				return nil
			}})
	if err != nil {
		panic(err)
	}

It doesn't work at the moment; am I missing a way to do it or is it simply not possible?

[beaugunderson]

I unblocked myself, I can construct my own parameter list that matches the length of the array I pass in as Args:

	var callsInDatabase = map[string]int64{}
	var parameters []string

	for _ = range callsInFile {
		parameters = append(parameters, "?")
	}
	var parameterString = strings.Join(parameters, ", ")

	parameterValues := make([]interface{}, len(callsInFile))
	for i, v := range callsInFile {
		parameterValues[i] = v
	}

	err = sqlitex.Execute(conn,
		fmt.Sprintf(`SELECT id, callsign FROM callsigns WHERE callsign IN (%s)`, parameterString),
		&sqlitex.ExecOptions{Args: parameterValues,
			ResultFunc: func(stmt *sqlite.Stmt) error {
				callsInDatabase[stmt.ColumnText(1)] = stmt.ColumnInt64(0)
				return nil
			}})
	if err != nil {
		panic(err)
	}

I do think it would be nice to have the option to bind those arrays directly for IN clauses though. :)

[zombiezen]

SQLite does not have a direct way of passing arrays into statements. As you've discovered, you can create a statement every time, but that ends up eating some performance because you have to compile a new statement every time. Another approach is to use the json_each function:

SELECT id, callsign FROM callsigns WHERE callsign IN (SELECT value FROM json_each(?));

And then pass in a JSON-encoded array as the single parameter.

[beaugunderson]

Oh, I really like the json_each approach--in my case I'm only executing this query once per application run so the performance hit isn't an issue but the json_each approach is less code and more readable. Thanks for pointing that out! 🙏