Support multiple parameters

[gonerandom]

When using queryRaw (presumably same for query), using multiple parameters fails with the message:

events.js:2549: Uncaught Error: [Microsoft][SQL Server Native Client 11.0][SQL Server]There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

The workaround is to output the parameters in the SQL directly, however it would be really useful and be better for performance to be able to use multiple parameters.

Example:

var query = 'INSERT INTO dbo.Logs (LogDate, Level, Message) VALUES (?)';
var currentDate = '2013-07-29 06:23';
var level = 'Info';
var msg = 'This is a message';
var values = [currentDate, level, msg];

sql.queryRaw(connectionString, query, values, function(err, results) {
    if (err) throw err;
    console.log('logged');
});

[jkint]

You need one question mark for every parameter in the array.

var query = 'INSERT INTO dbo.Logs (LogDate, Level, Message) VALUES (?, ?, ?)';

should allow you to put the values array into your database.

[PvanHengel]

I see this is closed, I ran across another case where this might help. For example, if you want to do something like "SELECT * FROM TABLE_A WHERE COL_Z IN(?) OR COL_Y IN(?);" and you dont know the number of values ahead of time you can pass multiple arrays in. So you could pass the sql and an array as such:

var sql_params = [["A","Bunch","Of","Values"],"Single Value"];

Here is a small POC that can be used, maybe it could be merged into the source at some point?

function execute_raw_sql(sql_command, sql_params, _callback){
var param_index = null;
var actual_sql_params = [];
var output_param_array = [];

for(param in sql_params){
    param_index = sql_command.indexOf("?",param_index)+1;
    // TODO: this only checks if we have too many values, we should also check if we have too few...
    if(param_index==0){
        console.log("Error: The number of parameters passed does not match the parameters in the sql!");
    }

    if( Object.prototype.toString.call( sql_params[param] ) === '[object Array]' ) { 
        // If we are an array, then find the cooresponding ? in the sql command, and replace with multiple ? marks
        array_length = sql_params[param].length;
        var str = new Array(array_length + 1).join( "?," );
        str = str.substring(0, str.length - 1);

        sql_command = sql_command.substring(0, param_index-1) + str + sql_command.substring(param_index + 0);
        param_index = param_index + str.length;

        for(innerparam in sql_params[param]){
            output_param_array.push(sql_params[param][innerparam]);
        }
    }else{

        output_param_array.push(sql_params[param]);
    }
}
console.log(sql_command);
console.log(output_param_array);

//sqlconn.query(sql_commend,sql_params, function(err, sql_results) {
sql.query(conn_str,sql_command,output_param_array, function(err, sql_results) {

if(err){ 
        console.log(err);
        _callback({"result":"failure","detail":err,"origParams":sql_params});
    } 
    else{
        _callback( {"result":"success", results:sql_results, origParams:sql_params} );
    }
});

}