Issue Related to Prepare or Bind

[hansaliyad1]

Hello,

I am having this weird issue with ODBC just on my production environment but not on development. I have this below function. When this function is called, it returns a result based on what my code can decide if the record exists or not.

async function functionName(bindValue) {
    try {
        const conn = await db.conn();
        const statement = await conn.createStatement();
        await statement.prepare('select count(*) as ttl_recs from hcc_ccs_grtrack2 where risk_no=? and random_id=?');
        await statement.bind(bindValue);
        await console.log(statement);
        const result = await statement.execute();
        return Promise.resolve(result)
    }
    catch (e) {
        return Promise.reject(e);
    } 
}

In the development environment, the result I get back is below.

Statement { odbcStatement: ODBCStatement {} }
[
  { ttl_recs: 1 },
  statement: 'select count(*) as ttl_recs from hcc_ccs_grtrack2 where risk_no=? and random_id=?',
  parameters: [ 'xxxxxxxxxxx', 'xxxxxxxxxxx' ],
  return: undefined,
  count: -1,
  columns: [ { name: 'ttl_recs', dataType: 4 } ]
]

In the production environment, with the same function and same ODBC connection, the result I get back is below.

Statement { odbcStatement: ODBCStatement {} }
[
  { t: 1 },
  statement: 's',
  parameters: [ 'xxxxxxxxxxx', 'xxxxxxxxxxx' ],
  return: undefined,
  count: -1,
  columns: [
    {
      name: 't',
      dataType: 4,
      columnSize: 10,
      decimalDigits: 0,
      nullable: false
    }
  ]
]

On production, ttl_recs changes to just t even though the query is select count(*) as ttl_recs. Both the production and development environment is windows server 2016 with NodeJS version of 12.18.2 on development and 12.18.3 on production. I will really appreciate any assistance with this weird issue. Thank you!

[markdirish]

Hi @hansaliyad1 , could you just confirm what version of odbc you are using? If 2.x, I think I know the issue:

It looks like your production environment is on version 2.3.2, which had a bug on WIndows systems where UNICODE caused some errors with things like column names to be truncated. It was fixed for column names, error messages, and error states, but I see that your statement is also truncated. I will try to confirm that that is still broken in 2.3.3 and fix it today.

Anyways, the fix: In your production environment, run npm upgrade odbc, and you should pull in 2.3.3.

And just FYI, the issue board for 2.x is here: https://github.com/markdirish/node-odbc/issues

[hansaliyad1]

@markdirish Can't thank you enough! I was working on this all weekend. Thank you so much! In my development environment, I have odbc version 2.3.0. I did try to install 2.3.3 on production but it was giving me the same issue so I downgraded to 2.3.0. It works great now. Thank you so much!!

[markdirish]

@hansaliyad1 , did your output look exactly the same in 2.3.3, or was it just the statement that was broken? On Windows 64-bit machines, odbc uses a pre-built binary. Its possible the upgrade command didn't pull the new binary down (or rebuild). If you want to use 2.3.3, you should be able to run npm install [email protected] --install-from-source, or delete the odbc.node binary and force it to pull down the latest version.

If you are certain you got the 2.3.3 binary and it still had bad output, I'd be happy to look into it if you post details on https://github.com/markdirish/node-odbc/issues

[hansaliyad1]

@markdirish The output didn't look exactly the same! Only ttl_recs was returned as t. Since development environment is using 2.3.0, I will use that version for now and will upgrade it in future updates. Thank you for your time and prompt response. I really appreciate it.

[hansaliyad1]

@markdirish Just FYI, I did try to use a 2.3.3 version again because in 2.3.0, the ODBC connection was dropping randomly and it never re-connects. I have to restart the Node application to make it connect. Before start using 2.3.3, I ran npm uninstall odbc --save command. Then, I ran npm install odbc --save and npm rebuild. I also made sure in package.json that the new version was installed. I tried again to query ODBC but it returned below results.

GetRegValue(): Unable to RegOpenKeyEx () The system cannot find the file specified.

Statement { odbcStatement: ODBCStatement {} }
[
  { t: 1 },
  statement: 's',
  parameters: [ 'xxxxxxxxx', 'xxxxxxxxx' ],
  return: undefined,
  count: -1,
  columns: [
    {
      name: 'ttl_recs',
      dataType: 4,
      columnSize: 10,
      decimalDigits: 0,
      nullable: false
    }
  ]
]