英文:
Submitting an SQL query with a slice parameter
问题
我有一个Snowflake查询，我想在所有项目中更新一个字段，其中另一个字段在作为变量提交给查询的列表中：
UPDATE my_table SET download_enabled = ? WHERE provider_id = ? AND symbol IN (?)
我尝试使用gosnowflake.Array函数执行此查询，代码如下：
enable := true
provider := 1
query := "UPDATE my_table SET download_enabled = ? WHERE provider_id = ? AND symbol IN (?)"
if _, err := client.db.ExecContext(ctx, query, enable, provider,
gosnowflake.Array(assets)); err != nil {
fmt.Printf("Error: %v", err)
}
然而，这段代码会出现以下错误：
002099 (42601): SQL compilation error: Batch size of 1 for bind variable 1 not the same as previous size of 2.
那么，我该如何将表示值列表的变量提交给SQL查询呢？
英文:
I have a Snowflake query where I'm trying to update a field on all items where another field is in a list which is submitted to the query as a variable:
UPDATE my_table SET download_enabled = ? WHERE provider_id = ? AND symbol IN (?)
I've tried doing this query using the gosnowflake.Array function like this:
enable := true
provider := 1
query := "UPDATE my_table SET download_enabled = ? WHERE provider_id = ? AND symbol IN (?)"
if _, err := client.db.ExecContext(ctx, query, enable, provider,
gosnowflake.Array(assets)); err != nil {
fmt.Printf("Error: %v", err)
}
However, this code fails with the following error:
> 002099 (42601): SQL compilation error: Batch size of 1 for bind variable 1 not the same as previous size of 2.
So then, how can I submit a variable representing a list of values to an SQL query?
答案1
得分: 0
我找到了一个潜在的解决方法，就是将列表中的每个项作为单独的参数显式地提交：
func Delimit(s string, sep string, count uint) string {
return strings.Repeat(s+sep, int(count)-1) + s
}
func doQuery(enable bool, provider int, assets ...string) error {
query := fmt.Sprintf("UPDATE my_table SET download_enabled = ? " +
"WHERE provider_id = ? AND symbol IN (%s)", Delimit("?", ",", uint(len(assets))))
params := []interface{}{enable, provider}
for _, asset := range assets {
params = append(params, asset)
}
if _, err := client.db.ExecContext(ctx, query, params...); err != nil {
return err
}
return nil
}
不用说，这个解决方法比我想要的更不优雅，但它确实可以工作。
英文:
I found a potential workaround, which is to submit each item in the list as a separate parameter explicitly:
func Delimit(s string, sep string, count uint) string {
return strings.Repeat(s+sep, int(count)-1) + s
}
func doQuery(enable bool, provider int, assets ...string) error {
query := fmt.Sprintf("UPDATE my_table SET download_enabled = ? " +
"WHERE provider_id = ? AND symbol IN (%s)", Delimit("?", ", ", uint(len(assets))))
params := []interface{}{enable, provider}
for _, asset := range assets {
params = append(params, asset)
}
if _, err := client.db.ExecContext(ctx, query, params...); err != nil {
return err
}
return nil
}
Needless to say this is a less elegant solution then what I wanted but it does work.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论