Error conjugating Date and String bind parameters in execute

[gregz07]

1. What versions are you using?

   https://github.com/oracle/node-oracledb/releases/download/v5.2.0/oracledb-src-5.2.0.tgz

2. Is it an error or a hang or a crash?

3. What error(s) or behavior you are seeing?

[Error: ORA-01036: illegal variable name/number]

4. Include a runnable Node.js script that shows the problem.

` const oracledb = require('oracledb');

    process.ORA_SDTZ = 'Europe/Lisbon';

     // **LOG_TS** is a TIMESTAMP(0,6)
      const queryString =
          `SELECT t.*
              FROM TABLE_X t 
              WHERE  t.ACTIVITY_ID = :ACT_ID  
                  AND t.LOG_TS >= :SDT  ORDER BY t.LOG_TS DESC`;

      const bindparams = {
          ACT_ID: { val: '2-1WZZC51T', type: oracledb.STRING, dir: oracledb.BIND_IN },
          SDT: { val: new Date('2022-01-02T00:00:00.000Z'), type: oracledb.DATE, dir: oracledb.BIND_IN }
      };

      let connection;
      try {
          connection = await getConnectionFromPool();
          const result = await connection.execute(
              queryString,
              values,
              {
                  autoCommit: true,
                  outFormat: oracledb.OUT_FORMAT_OBJECT
              }
          );

          console.log(result.rows);
      } catch (err) {
          console.error(err);
      } finally {
          if (connection) {
              try {
                  // Put the connection back in the pool
                  await connection.close();
              } catch (err) {
                  console.error(err);
              }
          }
      }

`

I should note that this only happens when I try to conjugate both params.

If I remove the ACT_ID in the query and in the bindParams, it works. Or If I remove the STD from the query and the bindParams, also works.

WORKS `
// code

   const queryString = 'SELECT t. FROM TABLE_X t WHERE t.ACTIVITY_ID = :ACT_ID';

    const bindparams = {
        ACT_ID: { val: '2-1WZZC51T', type: oracledb.STRING, dir: oracledb.BIND_IN }
    };

` WORKS

`
// code

    const queryString = 'SELECT t.* FROM TABLE_X t WHERE t.LOG_TS >= :SDT  ORDER BY t.LOG_TS DESC';

    const bindparams = {
        SDT: { val: new Date('2022-01-02T00:00:00.000Z'), type: oracledb.DATE, dir: oracledb.BIND_IN }
    };

`

[anthony-tuininga]

You're not the first to get bitten by this. Note there is a spelling mistake in your bind variable name. In one you have SDT and in the other STD. They need to be the same!

[gregz07]

Thats just a typo , in the code its fine.

[anthony-tuininga]

I can only go by what you have posted (and have now edited to be correct). That error (ORA-01036: illegal variable name/number) is what results when you have such "typos". :-)

[gregz07]

And you were absolutely right, STD's strike even in the programming world. Thank you for your time and patience

Full code `

        if (k === 'startDate') {
            if (whereString !== whereBase) {
                whereString = whereString.concat(` AND t.LOG_TS >= :SDT `);
            } else {
                whereString = whereString.concat(` t.LOG_TS >= :STD `);
            }

            const starDate: string = filters['startDate' as keyof Object].toString();
            whereVals.STD = { val: new Date(starDate), type: oracledb.DATE,  dir: oracledb.BIND_IN };
        }`