select * from x where y in(a,b) doesn't work

[kikillotest]

hi,

i tried this query :

connection.execute("select OBJECTID from IMMEUBLES where VOIE in (:1)",
[" '1 RUE ARNOLD SCHOENBERG', '3 ROND-POINT DES SAULES' "],
function(err, results) {
  console.log(results);
});

but it returns no results when it should have returned 2 values.

i also tried to put those value in an array :

var adresses = new Array('1 RUE ARNOLD SCHOENBERG', '3 ROND-POINT DES SAULES');
connection.execute("select OBJECTID from IMMEUBLES where VOIE in (:1)",
adresses,
function(err, results) {
  console.log(results);
});

then i get this error :

Error: ORA-32109: invalid column or parameter position

how can i solve this ? what am i doing wrong ? thank you for your time and for this driver

[kontrafiktion]

Could you try:

connection.execute("select OBJECTID from IMMEUBLES where VOIE in (:1)",
  ["1 RUE ARNOLD SCHOENBERG"],
  function(err, results) {
    console.log(results);
  });

I am not sure how "IN" is supported by node-oracle. I would expect that one result should now be returned. (If there is an entry with that 'VOIE').

What you could do is this:

connection.execute("select OBJECTID from IMMEUBLES where VOIE in (:1, :2)",
  ["1 RUE ARNOLD SCHOENBERG", "23 ROND-POINT DES SAULES"],
  function(err, results) {
    console.log(results);
  });

But now your SQL depends on the number of "VOIE" you want to search for. Not so nice. I think it is not possible, to have multiple values in the "IN" clause, without explicitly having each as a parameter

[baconmania]

^ Is that really the only way to get this working? Seems to partly defeat the purpose of using a prepared statement.

[ericwaldheim]

Yes, that's how it works. It works the same way in sqlplus. This issue can be closed.

(You could write a wrapper around it to do the grunt work for you or you could use an oracle temp table and join against it.)