NodeJS MSSQL WHERE IN准备好的SQL语句 [英] NodeJS MSSQL WHERE IN Prepared SQL Statement

问题描述

我使用的是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 = @YI 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语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！