Array binding for query "WHERE IN" clause not working

[CorvusCorrax]

when using an array in a SQL IN binding, no row is returned, when using an integer a row is returned

var oracledb = require('oracledb');
oracledb.getConnection({...}, function(err, connection) {
  connection.execute('SELECT * FROM TEST WHERE IDTEST IN (:1)', [[4, 5, 6]], {}, function(err, result) {
    console.log(result);
  });
});

does node oracledb support it ?

[dmcghan]

Have a look at this: https://github.com/oracle/node-oracledb/issues/137

Does that help?

[atiertant]

well as sql query are limited to 64k, use a binding for all element of an array or stringify them will increase query size quickly !

[cjbj]

There are various solutions depending on the need; security is always the issue to be careful with, with performance next. For fixed or a capped number of binds, just use a statement with the max number of binds in the IN clause and bind NULLs to anything the actual invocation doesn't want to specify. At the other extreme put the values in some temporary table and don't use IN. There are plenty of web references with solutions and discussions. It's really outside the scope of node-oracledb since it is fundamental Oracle bind & SQL behavior. Note AskTom has one solution similar to Dan's but doesn't need the APEX gunk.

Nitpick: From AskTom "Even in Oracle8, 64k was not the limit."" Also see the note in Logical Database Limits "The limit on how long a SQL statement can be depends on many factors, including database configuration, disk space, and memory"

[liu-minjie]

how node-mysql solve this problem ?

[dmcghan]

@liu-minjie What makes you think they have? A link would help...

[lmcarreiro]

I've created a tagged template literal sql to write my SQL queries.

Example:

const query1 = sql`select * from table where a in (${[1, 2, 3]})`

query1 === {
  query: "select * from table where a in (:0, :1, :2)",
  parameters:[1,2,3]
}

const query2 = sql`
  select *
  from table
  where name like ${'some str'}
    and a in (${[1, 2, 3]})
    and b = ${100}
    and c in (${['val1', 'val2']})
`

query2 === {
  query: "select * from table where name like :0 and a in (:1, :2, :3) and b = :4 and c in (:3, :4)",
  parameters: ["some str", 1, 2, 3, 100, "val1", "val2"]
}

const [result1, result2] = await Promise.all([
  connection.execute(query1.query, query1.parameters),
  connection.execute(query2.query, query2.parameters)
])

Source code: https://stackoverflow.com/a/57614632/4871582

[cjbj]

Cross referencing to the related comment: https://github.com/oracle/node-oracledb/issues/699#issuecomment-524018809