Snowflake NodeJS Driver SELECT .... WHERE id IN (??) clause

user_1447849 0
Viewed 373

Let's say I have an array of numeric ID's

let arr = [123, 456]

Now, I want to get all the records with these ID's

try{
    connection.execute({
    binds: [arr],
    sqlText: `
        SELECT DISTINCT name, id
        FROM my_table
        WHERE id IN (?)
    `,
    complete: (err, stmt, rows) => {
        // receive results
    }
});

I'm only getting 1 column, for the first id in the "arr" array. I would like to get results of all the rows that match the id's in the "arr" array

snowflake-cloud-data-platform node.js
1 Answers

This works in Snowflake's JavaScript stored procedures and shows how to bind arrays. It uses a two-dimensional array so it can be used to construct a table in a VALUES clause. If you prefer you can modify it for use with a one dimensional array and adjust the code as required.

I haven't tried the same approach in Node.js, but I believe the same approach will work there too. If not, I can try adjusting it.

create or replace procedure BIND_TEST()
returns string
language javascript
as
$$

// Defined as a two dimensional array to enable construction of a table in a VALUES clause
let arr = [[3, 23, 24]];

let binds = arr.reduce((a, b) => a.concat(b));
let cols = arr[0].map(()=>"?").join(",");
let placeholders = arr.map(()=>"(" + cols + ")").join(",");
let sql = `
        SELECT DISTINCT N_NAME, N_REGIONKEY
        FROM "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1000"."NATION"
        WHERE N_NATIONKEY IN ${placeholders}`;
let cmd = {sqlText: sql, binds: binds};

var stmt = snowflake.createStatement(
   {
   sqlText: sql,
   binds: binds
   }
);

let rs = stmt.execute();

let out = "";

while(rs.next()) {
    if (out != "") out += ",";
    out += rs.getColumnValue("N_NAME");
}

return out;

$$;

call bind_test();
user_127563810
Related