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

问题描述

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

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 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.

以下是我想到的伪代码:

Here's pseudo code for what I have in mind:

-- 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.

这篇关于SQLite:将值列表绑定到"WHERE col IN(:PRM)".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！