Problem with sintaxis "IN" to select

[ghost]

Hi friends,

I need help, my query have problem where use tha sintaxis "IN" , my result rows is []

The values in the condition are numbers, for example:

"and c.id_centro in ( :unidades ) " +

the var :unidades is the type string : (2216,2201,2203,2501,2502,2401)

If var :unidades is array show error : NJS-044: named JSON object is not expected in this context

1. What is your version of Node.js? Run examples/version.js to find versions.
   Node version. 6.10

2. What version of node-oracledb are you using?
   oracledb 1.11

3. What is the version of your Oracle client (e.g. Instant Client)? How was it installed? Where it is installed?
   12

4. What is the version of Oracle Database?
   11g

5. What is your OS and version?
   windows 7

6. What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as?

7. What error(s) you are seeing?

[dmcghan]

@fabialvarado Have a look at #450. It references #137, but has some additional notes that may be of interest from @cjbj.

I should do a blog post on this topic! :)

[ghost]

hi, thanks for answer, my solutions of the moment is invoked the functions with an foreach, see this:
...
unidades = req.body.unidades; <-- this array [2102,2205,2103,2104]
...
unidades.forEach(function(element) {
fileJS.functionSelect(user,element, function (err, respuesta){
..

[dmcghan]

@fabialvarado The forEach loop is synchronous but it's making asynchronous calls. Try using something like async's eachSeries method to maintain control there.

Also, when performance matters, it's important to avoid unnecessary round trips on the network. Using one of the other solutions mentioned will do this for you.

[ghost]

ok @dmcghan , thanks for your help.

[whardier]

I'm running into this as well with Node 6.10 and an up to date oracledb module along with the latest instant client from oracle.

I'm sending an array of padded chars to be matched against PROJ_NAME (char(20)) and getting back

Unhandled rejection Error: NJS-044: named JSON object is not expected in this context
at Error (native)

Using module asyncawait (old node)

    result = await(
        conn.execute(
            `
                SELECT
                    ICRE_PROJECT.PROJ_NAME     ICRE_PROJECT_PROJ_NAME,
                    ICRE_PROJECT.PROJ_ID       ICRE_PROJECT_PROJ_ID
                FROM ICRE.PROJECT@DF ICRE_PROJECT
                WHERE
                    ICRE_PROJECT.PROJ_NAME IN (:PROJ_NAMES)
            `,
            {
                PROJ_NAMES: proj_char_names,
            }
        )
    )

[cjbj]

@whardier see https://oracle.github.io/node-oracledb/doc/api.html#sqlwherein
(Also this issue is long closed; best open a new one if you still have issues).

[whardier]

Thanks @cjbj - Ended up with the following solution (can't create temporary tables due to access restrictions... so no join love).

String.prototype.toOracleDbChar = function(maxSize) {
    return (this + ' '.repeat(maxSize)).substr(0, maxSize)
}

Array.prototype.toOracleDbChar = function(maxsize) {
    return this.map((val) => {
        return val.toOracleDbChar(maxsize)
    })
}

Array.prototype.toOracleDbBindMap = function(offset = 0) {
    return this.map((val, index) => `:${index+offset}`).join(", ")
}

            var conn = await (oracledb.getConnection(icrePoolConfig.poolAlias));

            var result;

            _.each(
                _.chunk(proj_names.toUniqueList(), 255), (proj_names_chunk) => {

                    chunk_result = await (
                        conn.execute(`
                                SELECT
                                    *
                                FROM ICRE.PROJECT@DF ICRE_PROJECT
                                WHERE
                                    ICRE_PROJECT.PROJ_NAME IN (${proj_names_chunk.toOracleDbBindMap()})`,
                            proj_names_chunk.toOracleDbChar(20)
                        )
                    )

                    if (result) {
                        result.rows.push(...chunk_result.rows);
                    } else {
                        result = chunk_result;
                    }

                    console.log(result.rows.length);

                }
            )

            await (conn.close());

            return result;

[cjbj]

You could still be nice to the statement cache and DB by combining techniques to reduce the number of unique SQL statements that get executed like:

binds = ['Christopher', 'Hazel', 'Samuel'];
if (binds.length <= 10) {
  let i;
  sql = "SELECT first_name, last_name FROM employees WHERE first_name IN (";
  for (i=0; i < binds.length; i++)
     sql += (i > 0) ? ", :" + i : ":" + i;
  for (i=binds.length; i < 10; i++) {
     sql += (i > 0) ? ", :" + i : ":" + i;
     binds[i] = null;
   }
  sql += ")";
} else {
  let i;
  sql = "SELECT first_name, last_name FROM employees WHERE first_name IN (";
  for (i=0; i < binds.length; i++)
     sql += (i > 0) ? ", :" + i : ":" + i;
  sql += ")";
}

For what it's worth, last time I bench marked some alternative JS implementations of constructing the SQL statement, the simple loop solution was fastest.

[whardier]

I had considered this and it makes sense. I'd have to come up with an unmatchable other than null but.. I can ;) Thanks for the reply!

[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 (TypeScript):

interface ParameterizedSQL {
  query: string;
  parameters: any[];
}

export function sql(queryParts: TemplateStringsArray, ...parameters: any[]): ParameterizedSQL {
  if ((queryParts.length - 1) === parameters.length) {
    return {
      query: queryParts.map((part, index) => index < parameters.length ? `${part}${parameterIndexes(parameters, index)}` : part).join(''),
      parameters: parameters.flat(),
    };
  }
  throw new Error("Invalid number of parameters.");
}

function parameterIndexes(parameters: any[], index: number): string {
  const newIndex = parameters.slice(0, index).reduce((p, c) => p + (Array.isArray(c) ? c.length : 1), 0);
  const parameter = parameters[index];
  if (Array.isArray(parameter)) {
    const indexes = new Array<number>(parameter.length).fill(index).map((e, i) => e + i);
    return ':' + indexes.join(', :');
  }
  else {
    return ':' + newIndex;
  }
}

Source code (Javascript):

function sql(queryParts, ...parameters) {
  if ((queryParts.length - 1) === parameters.length) {
    return {
      query: queryParts.map((part, index) => index < parameters.length ? `${part}${parameterIndexes(parameters, index)}` : part).join(''),
      parameters: parameters.flat(),
    };
  }
  throw new Error("Invalid number of parameters.");
}

function parameterIndexes(parameters, index) {
  const newIndex = parameters.slice(0, index).reduce((p, c) => p + (Array.isArray(c) ? c.length : 1), 0);
  const parameter = parameters[index];
  if (Array.isArray(parameter)) {
    const indexes = new Array(parameter.length).fill(index).map((e, i) => e + i);
    return ':' + indexes.join(', :');
  }
  else {
    return ':' + newIndex;
  }
}

[dmcghan]

@lmcarreiro Pretty clever - looks cool! Note that you shouldn't be using Promise.all with connection.execute. In theory, you're doing work in parallel. In reality, a connection can only do one thing at a time so this runs sequentially anyway.

When you do that you depend on Libuv's thread pool queue to handle this correctly and I've only seen bad things happen as a result.

Changing this:

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

to this:

const result1 = await connection.execute(query1.query, query1.parameters);
const result2 = await connection.execute(query2.query, query2.parameters);

Is easier to understand and more accurate in terms of how it's executing. It also keeps the queuing in JS land where it belongs.

[cjbj]

@lmcarreiro thanks for sharing, I will link to this from the doc.