问题描述
我使用的是nodejs npm包 sql
我目前有一系列产品skus就是这样..
I am use nodejs npm package sql
I currently have an array of product skus like so..
var skus = ['product1', 'product2', 'product3'];
我的sql存储在一个文件中如下...
My sql store in a file as follows...
SELECT *
FROM stock AS s
WHERE s.sku IN (@skus)
然后我也准备好了我的预备语句代码如下..
Then I also have my prepared statement code as follows..
var connection = new sql.Connection(config, function(err) {
var ps = new sql.PreparedStatement(connection);
//Add params
if(params != undefined){
for(var key in params){
ps.input(key, sql.VarChar(200));
}
}
ps.prepare(sqlstatement, function(err) {
ps.execute(params, function(err, data) {
callback(null, data);
ps.unprepare(function(err) {
});
});
});
});
}
skus
包含正确地在 params
对象内,因为当我将它用于简单的 WHERE X = @Y
时,语句正常工作只是努力解决我需要传递 skus
的数组,以允许它们在准备好的语句中工作。
skus
is contained correctly within the params
object as the statement works fine when I am using it for simple WHERE X = @Y
I am just struggling with how I need pass the array of skus
to allow them to work in the prepared statement.
I我修改字符串使用拆分
和加入
以逗号分隔它们等等但我不能让这些方法工作。
I am amend the string using split
and join
to comma seperate them etc etc but I can't get these methods to work.
我认为我需要param字符串看起来像以下'product1','product2','product3'
。
I assumed that I would need the param string to look like the following 'product1','product2','product3'
.
如果有人可以解释如何调试已完成的预准备语句,那么我也可以看到实际被查询到SQL的内容( params inplace)
would be also useful if someone could shed some light on how to debug the completed prepared statement so I can see what is actually being queried to SQL (with params inplace)
非常感谢提前!
推荐答案
它看来 sql
对象(即 mssql
模块)没有任何属性来处理任何数组。此外,在 ps.input
的调用中指定标量类型同样不起作用。
It appears that the sql
object (i.e. the mssql
module) has no attribute to handle arrays of anything. Moreover, specifying a scalar type in the call to ps.input
similarly does not work.
下一个最好的事情是为你的sql语句本身构建你的参数数组的键:
The next best thing is to build keys for your array of parameters into your sql statement itself:
var connection = new sql.Connection(config, function(err) {
var ps = new sql.PreparedStatement(connection);
// Construct an object of parameters, using arbitrary keys
var paramsObj = params.reduce((obj, val, idx) => {
obj[`id${idx}`] = val;
ps.input(`id${idx}`, sql.VarChar(200));
return obj;
}, {});
// Manually insert the params' arbitrary keys into the statement
var stmt = 'select * from table where id in (' + Object.keys(paramsObj).map((o) => {return '@'+o}).join(',') + ')';
ps.prepare(stmt, function(err) {
ps.execute(paramsObj, function(err, data) {
callback(null, data);
ps.unprepare(function(err) {
});
});
});
});
}
这篇关于NodeJS MSSQL WHERE IN准备好的SQL语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!