SQLite:将值列表绑定到“WHERE col IN (:PRM)"；

2021-12-19 00:00:00 bind select prepared-statement sqlite

我想做的就是发送一个类似的查询

all I want to do is send a query like

SELECT * FROM table WHERE col IN (110, 130, 90);

所以我准备了以下声明

SELECT * FROM table WHERE col IN (:LST);

然后我用

sqlite_bind_text(stmt, 1, "110, 130, 90", -1, SQLITE_STATIC);

不幸的是，这变成了

SELECT * FROM table WHERE col IN ('110, 130, 90');

and 没用(注意两个额外的单引号).我已经尝试在字符串中添加额外的 ' 但它们被转义了.我没有找到关闭转义或防止文本被单引号括起来的选项.我能想到的最后一件事是不使用准备好的语句，但我只将其作为最后的选择.您有什么想法或建议吗?

and is useless (note the two additional single quotes). I already tried putting extra ' in the string but they get escaped. I didn't find an option to turn off the escaping or prevent the text from being enclosed by single quotes. The last thing I can think of is not using a prepared statement, but I'd only take it as last option. Do you have any ideas or suggestions?

谢谢

参数的数量是动态的，所以它可能是三个数字，如上例所示，一个或十二个.

The number of parameters is dynamic, so it might be three numbers, as in the example above, one or twelve.

推荐答案

可以动态构建表单的参数化 SQL 语句

You can dynamically build a parameterized SQL statement of the form

 SELECT * FROM TABLE WHERE col IN (?, ?, ?)

然后为每个?"调用 sqlite_bind_int 一次您添加到语句中.

and then call sqlite_bind_int once for each "?" you added to the statement.

无法将一个文本参数直接绑定到多个整数(或者，就此而言，多个文本)参数.

There is no way to directly bind a text parameter to multiple integer (or, for that matter, multiple text) parameters.

这是我想到的伪代码:

-- Args is an array of parameter values
for i = Lo(Args) to Hi(Args)
   paramlist = paramlist + ', ?'

sql = 'SELECT * FROM TABLE WHERE col IN (' + Right(paramlist, 3)  + ')'

for i = Lo(Args) to Hi(Args)
  sql_bind_int(sql, i, Args[i]

-- execute query here.

相关文章